Geek Answers Handbook

CakePHP 3 contains select boxes

I have the following database tables: elements, users, groups, itemImages. Between relations (elements, groups) and (users, groups) there are many different relations, and between them (between points, itemImages). All relevant foreign keys were installed in CakePHP as associations.

How can I build a query with the contains () function that selects all the elements and their main image that are in the group that was assigned as the main group for the user with a specific identifier?

To make it clearer, here's what a simple SQL query looks 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 that I have:

 $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']);
+7
php mysql orm cakephp
source share
1 answer

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 addition operator can be omitted because the desired result is already in the _matchingData strong> property .

A note about distict () stament:

Since this function will create an INNER JOIN, you might consider causing a difference on the find query, as you can get duplicate strings 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

+9
source share

More articles:


All Articles