group by on associated model in CakePHP

Question

I'm working in CakePHP 3.4

I have two models skills and skill_categories and their association are like

skill_categories->hasMany('skills');
skills->belongsTo('SkillCategories', 'joinType' => 'INNER')

and skills is having an association with users where

skills->belongsTo('users')
users->hasMany('Skills')

I have to select all associated skills of the user from skills table grouping by skills.skill_category_id which will produce result like

Skill Category 1
|-- Skill 11
|-- Skill 12
|-- Skill 13
Skill Category 2
|-- Skill 21
|-- Skill 22

or like

'skill_categories' => [
        'title' => 'Skill Category 1',
        'id' => 1,
        'skills' => [
            0 => [
               'title' => 'Skill 11',
               'id' => 4,
             ],
            1 => [
                'title' => 'Skill 12',
                'id' => 6,
            ]
        ],
]

What I'm doing is : Method 1

$user_skills = $this->Skills->find()
        ->select(['Skills.skill_category_id', 'Skills.title', 'Skills.measure', 'SkillCategories.title', 'Skills.id'])
        ->where(['Skills.user_id' => $user->id, 'Skills.deleted' => false, 'Skills.status' => 0])
        ->contain(['SkillCategories'])
        ->group(['Skills.skill_category_id']);

        foreach($user_skills as $s)debug($s);

But this is throwing error as

Error: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'profPlus_db_new.Skills.title' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Method 2

$user_skills = $this->SkillCategories->find()
        ->where(['Skills.user_id' => $user->id, 'Skills.deleted' => false, 'Skills.status' => 0])
        ->contain(['Skills'])
        ->group(['SkillCategories.id']);

        foreach($user_skills as $s)debug($s);

But this gives error as

Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Skills.user_id' in 'where clause'

EDIT 2

skills schema

CREATE TABLE IF NOT EXISTS `skills` (
  `id` CHAR(36) NOT NULL,
  `user_id` CHAR(36) NOT NULL,
  `skill_category_id` CHAR(36) NOT NULL,
  `title` VARCHAR(250) NOT NULL,
  `measure` INT NOT NULL DEFAULT 0,
  `status` INT NULL DEFAULT 0,
  `deleted` TINYINT(1) NULL DEFAULT 0,
  `created` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `modified` DATETIME NULL,
  PRIMARY KEY (`id`)
  )

skill_categories schema

CREATE TABLE IF NOT EXISTS `skill_categories` (
  `id` CHAR(36) NOT NULL,
  `title` VARCHAR(200) NOT NULL,
  `status` INT NULL DEFAULT 0,
  `deleted` TINYINT(1) NULL DEFAULT 0,
  `created` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `modified` DATETIME NULL,
  PRIMARY KEY (`id`))

controller code

$user_skills = $this->SkillCategories->find()
   ->contain([
       'Skills' => function($q) use($user) {
           return $q
           ->select(['id', 'skill_category_id', 'title', 'measure', 'user_id', 'deleted', 'status'])
           ->where(['user_id' => $user->id, 'deleted' => false, 'status' => 0]);
    }])
    ->group(['SkillCategories.id']);

    foreach($user_skills as $s)debug($s);

debug output

object(App\Model\Entity\SkillCategory) {

'id' => '581cd4ac-28a7-4016-b535-b34a27d47c0d',
'title' => 'Programming',
'status' => (int) 0,
'deleted' => false,
'skills' => [
    (int) 0 => object(App\Model\Entity\Skill) {

        'id' => '36f16f7f-b484-4fd8-bfc5-4408ce97ff23',
        'skill_category_id' => '581cd4ac-28a7-4016-b535-b34a27d47c0d',
        'title' => 'PHP',
        'measure' => (int) 92,
        'user_id' => '824fbcef-cba8-419e-8215-547bd5d128ad',
        'deleted' => false,
        'status' => (int) 0,
        '[repository]' => 'Skills'

    },
    (int) 1 => object(App\Model\Entity\Skill) {

        'id' => '4927e7c1-826a-405d-adbe-e8c084c2b9ef',
        'skill_category_id' => '581cd4ac-28a7-4016-b535-b34a27d47c0d',
        'title' => 'CakePHP',
        'measure' => (int) 90,
        'user_id' => '824fbcef-cba8-419e-8215-547bd5d128ad',
        'deleted' => false,
        'status' => (int) 0,
        '[repository]' => 'Skills'
    }
],
'[repository]' => 'SkillCategories'

}

object(App\Model\Entity\SkillCategory) {

'id' => 'd55a2a95-05a0-410e-9a55-1a1509f76b8c',
'title' => 'Office',
'status' => (int) 0,
'deleted' => false,
'skills' => [],
'[repository]' => 'SkillCategories'

}

Note : See 2nd object with title Office has no skills associated with user

Have you tried to pass the conditions into the contain? `$user_skills = $this->SkillCategories->find()->contain(['Skills' = function($q) use($user) {return $q->select(['id', 'user_id', 'deleted', 'status'])->where(['user_id' => $user->id, 'deleted' => false, 'status' => 0]);}])->group(['SkillCategories.id']);` — chrisShick, May 09 '17 at 21:51
Thanks, It did the job. Make an answer of it so that I could mark it as accepted — Anuj TBE, May 10 '17 at 01:44

chrisShick · Accepted Answer · 2017-05-11T18:07:04.683

1

You can pass the conditions into the contain! :D

$user_skills = $this->SkillCategories->find()
    ->contain(['Skills' => function($q) use($user) {
        return $q
        ->select(['id', 'user_id', 'deleted', 'status', 'skill_category_id'])
        ->where(['user_id' => $user->id, 'deleted' => false, 'status' => 0]);
    }])
    ->group(['SkillCategories.id']);

edited May 11 '17 at 18:07

answered May 10 '17 at 16:17

chrisShick

1,096
8
21

Hey, there is one problem with this script. It is listing all `Skill Categories` even if user has not filled it or is not associated with particular user. Ex. If there are 50 skill categories and user has only skills associated from 10 skill categories, even then it is listing all 50 skill categories where 40 are of no use. – Anuj TBE May 11 '17 at 17:23
@AnujTBE are you sure about that? Because I ran this exact query and it works for me. – chrisShick May 11 '17 at 18:10
yes, I'm sure about that. I have added 2 skill categories from admin panel. Then added 2 skills in one skill category leaving behind 2nd category using demo user panel. When I execute this script, it shows both categories. – Anuj TBE May 12 '17 at 04:31
Can you edit your original post to include your database schema and your exact code you are running? – chrisShick May 12 '17 at 04:35
See `Edit 2` in the question – Anuj TBE May 12 '17 at 05:18

group by on associated model in CakePHP

1 Answers1