Menu
A new addition to the CakePHP 1.2 core is the ContainableBehavior . This model behavior allows you to filter and limit model find operations. Using Containable will help you cut down on needless wear and tear on your database, increasing the speed and overall performance of your application.
To view records of database, we first need to get hold of a table using the TableRegistry class. We can fetch the instance out of registry using get() method. The get() method will take the name of the database table as argument. Now, this new instance is used to find records from database using find() method.
$last_query = $ this ->ModelName->getLastQuery(); As we have saved last executed query in variable $last_query then use this to print last executed query.
To apply ordering, you can use the order method: $query = $articles->find() ->order(['title' => 'ASC', 'id' => 'ASC']);
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']);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With