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
Thanks, It did the job. Make an answer of it so that I could mark it as accepted — Anuj TBE

chrisShick chrisShick · Accepted Answer · 2017-05-10T16:17:53

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']);

group by on associated model in CakePHP

1 Answers