CakePHP 3 hasMany association with bitwise expression

Question

I have two tables, AclGroups and AclPermissions, and I want to create a hasMany relationship between them, i.e AclGroups has many AclPermissions.

The condition to determine whether a group owns a given permission is done in a single bitwise check. This is what i'm trying to do:

SELECT 
    *
FROM
    acl_groups
        JOIN
    acl_permissions ON acl_permissions.permission & acl_groups.permission != 0

In AclGroupsTable I have tried the following:

$this->hasMany('AclPermissions', [
    'className' => 'AclPermissions',
    'foreignKey' => 'permission',
    'conditions' => [
        'AclPermissions.permission & AclGroups.permission !=' => 0
    ]
]);

But that just gives me SQLSTATE[42S22]: Column not found: 1054 Unknown column 'aclgroups.permission' in 'where clause'

In my controller I do:

$this->AclGroups->find('all')->contain(['AclPermissions']);

I suppose the real question is: Is there a way I can change the conditions of the ON clause in the query that fetches associated records

First things first, aclgroups != acl_groups, ie it's unclear whether the code you are showing here is what you are actually using, which is always problematic. That being said, please include the code that you use to build your query, hasMany associations are retrieved in a separate query unless explicitly using joins. — ndm
Mate, there's no need for the passive aggression. I get what you're saying but that's literally c/p from AclGroupsTable. The rest of the class is just baked. The only other line of code is a finder in my controller. $this->AclGroups->find('all')->contain(['AclPermissions']); and that's the full extent of it — Aidan Haddon-Wright
There's no aggression here whatsoever, I just wanted to point out that it's important to be precise. Don't forget that people here don't know where the code stems from, and what your environment looks like, hence it's easy to get confused when things don't match up, from the code that uses acl_groups (default CakePHP convention for a camel cased AclGroups) and the error message that uses aclgroups (no underscore), it wasn't clear whether you maybe were using joins (where your conditions would generally work) and just had a typo somewhere (could also be an additional problem). — ndm

ndm ndm · Accepted Answer · 2018-09-12T18:40:29

As mentioned in the comments, records of hasMany associations (and belongsToMany for that matter) will always be retrieved in a separate query when using contain().

If you need to create joins with such an association, then you must explicitly use the corresponding functionality for joins, for example leftJoinWith():

$this->AclGroups->find('all')->leftJoinWith('AclPermissions');

This will create a query similar to the one you are showing. However it would also generate the default conditions using the configured foreign key, you'd have to disable the foreign key in order to avoid that, like:

$this->hasMany('AclPermissions', [
    'foreignKey' => false, // << like this
    // ...
]);

Given that the association conditions won't work with contain() (and disabling the foreign key will make it even more unusable for that purpose), you may want to create a separate association for your joining purposes, or use the "lower level" join methods, where you specify all the conditions manually (you can for example put this in a custom finder in order to keep your code DRY):

$query = $this->AclGroups
    ->find('all')
    ->join([
        'table' => 'acl_permissions',
        'alias' => 'AclPermissions',
        'type' => 'LEFT',
        'conditions' => [
            'AclPermissions.permission & AclGroups.permission != :permission'
        ]
    ])
    ->bind(':permission', 0, 'integer');

Note that the value is being explicitly bound here to ensure that the correct type is being used, as it couldn't be determined from the non-standard left hand value (which isn't really ment to contain SQL snippets - you may want want to look into using expressions).

CakePHP 3 hasMany association with bitwise expression

1 Answers