CakePHP 3.x Contains GroupBy

Question

I am attempting to do a GroupBy on an associated table via contains -> conditions, however I am getting the following error...

Error: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'group = 'BrandUsers.user_id' AND BrandUsers.brand_id in (1,2,3,4,5,6))' at line 1

with the following query

SELECT
    BrandUsers.id AS `BrandUsers__id`,
    BrandUsers.user_id AS `BrandUsers__user_id`,
    BrandUsers.brand_id AS `BrandUsers__brand_id`,
    Users.id AS `Users__id`,
    Users.username AS `Users__username`,
    Users.email AS `Users__email`,
    Users.password AS `Users__password`,
    Users.first_name AS `Users__first_name`,
    Users.last_name AS `Users__last_name`,
    Users.token AS `Users__token`,
    Users.token_expires AS `Users__token_expires`,
    Users.api_token AS `Users__api_token`,
    Users.activation_date AS `Users__activation_date`,
    Users.secret AS `Users__secret`,
    Users.secret_verified AS `Users__secret_verified`,
    Users.tos_date AS `Users__tos_date`,
    Users.active AS `Users__active`,
    Users.is_superuser AS `Users__is_superuser`,
    Users.role AS `Users__role`,
    Users.created AS `Users__created`,
    Users.modified AS `Users__modified` 
FROM
    brand_users BrandUsers 
    INNER JOIN
        users Users 
        ON Users.id = 
        (
            BrandUsers.user_id
        )
WHERE
    (
        group = :c0 
        AND BrandUsers.brand_id in 
        (
            :c1,
            :c2,
            :c3,
            :c4,
            :c5,
            :c6
        )
    )

I have taken a look at the following links, but the above error persists

Here is my code

$this->paginate = [
    'contain' => [
        'BrandUsers' => [
            'conditions' => [
                'group' => 'BrandUsers.user_id'
            ]
        ],
        'BrandUsers.Users'
    ]
];

$brands = $this->paginate(
    $this->Brands
        ->find('all')
        ->where(['Brands.user_id' => $this->Auth->user('id')])
);

score 4 · Accepted Answer · answered Feb 25 '18 at 19:46

As mentioned in the answers/comments to the questions that you've linked, there is no group option for containments, that's true for CakePHP 2.x as well as 3.x, and if there was such an option you would have placed it wrong, as you've nested it inside the conditions option, hence it is being compiled into the queries WHERE clause.

If you need to modify the query used for obtaining containments on the fly, then you can for example pass a callable as known from other query builder methods:

'BrandUsers' => function (\Cake\ORM\Query $query) {
    return $query->group('BrandUsers.user_id');
}

or use the finder option to point to a finder that modifies the passed query accordingly:

'BrandUsers' => [
    'finder' => 'groupedByUser'
]

It should be noted that grouping only works for HasMany and BelongsToMany associations, as they are not being joined into the main/parent query.

CakePHP 3.x Contains GroupBy

1 Answers1