CakePHP 3 contain select fields

Question

I have the following DB tables: items, users, groups, itemImages. There are many to many relations between (items, groups) and (users, groups), and one to many between (items, itemImages). All the appropriate foreign keys have been setup in CakePHP as associations.

How can I construct a query with contain() which selects all items and their main image, which are in a group which has been assigned as a main group to a user with a particular id?

To make it clearer, here is what a plain SQL query would look like:

SELECT items.id AS itemId, items.name, itemImages.url AS itemUrl
FROM items
INNER JOIN groups_items ON groups_items.item_id = items.id
INNER JOIN groups ON groups_images.group_id = groups.id
INNER JOIN groups_users ON groups_users.group_id = groups.id
INNER JOIN users ON groups_users.user_id = users.id
INNER JOIN itemImages ON itemImages.item_id = items.id
WHERE groups_users.isMainGroup = 1
    AND users.id = :userId
    AND itemImages.isMainImage = 1

CakePHP code I have for now:

$items = $this->Items->find()
            ->hydrate(false)
            ->contain([
                'Groups.Users' => function($q) use ($userId){
                    return $q->where(['isMainGroup' => 1, 'Users.id' => $userId]);
                },
                'ItemImages' => function($q){
                    return $q->where(['isMainImage' => 1]);
                },
            ]);
            //->select(['itemId' => 'Items.id', 'Items.name', 'itemUrl' => 'itemImages.url']);

Answer 1

matching() method can help with this

$items = $this->Items->find()
        ->hydrate(false)
        ->select(['Items.id', 'Items.name', 'itemImages.url'])
        ->distinct(['Items.id'])
        ->matching('Groups.Users', function ($q) use ($userId) {
            return $q->where(['Groups.isMainGroup' => 1, 'Users.id' => $userId]);
        })
        ->matching('ItemImages', function ($q) {
            return $q->where(['ItemImages.isMainImage' => 1]);
        })
        ->contain([
            'ItemImages' => function ($q) {
                return $q->autoFields(false)
                         ->select(['id','url'])
                         ->where(['ItemImages.isMainImage' => 1]);
            }
        ]);

I think, in this case, the last contain statement can be ommited, because desired result is already in _matchingData property.

Note on distict() stament:

As this function will create an INNER JOIN, you might want to consider calling distinct on the find query as you might get duplicate rows if your conditions don’t filter them already

http://book.cakephp.org/3.0/en/orm/retrieving-data-and-resultsets.html#filtering-by-associated-data