php - Find records from database based on second table using cakephp 3 -

this question has answer here:

• how limit contained associations per record/group? 1 answer

i have 2 tables "categories" , "items"

categories ->  id  |   title 1   |   cat-1 2   |   cat-2   items -> id  |   title   |   category_id |   score 1   |   item-1  |   1           |   4 2   |   item-2  |   1           |   5 3   |   item-3  |   1           |   3 4   |   item-4  |   2           |   4 5   |   item-5  |   2           |   5 6   |   item-6  |   2           |   6 

i want result apply limit(2) , order score on "items" table using cakephp 3 output -

{     "cat-1": {         "0": {             "id": 2,             "title": "item-2",             "score": "5"         },         "1": {             "id": 1,             "title": "item-1",             "score": "4"         },     "cat-2": {         "2": {             "id": 6,             "title": "item-6",             "score": "6"         },         "3": {             "id": 5,             "title": "item-5",             "score": "5"         }     } 

use contain:

$this->categories->find()     ->contain([         'item' => function($q) {             return $q->find()                 ->order(['score' => 'asc'])                 ->limit(2);         }     ]); 

untested – commented on iphone. ;)