Find records from database based on second table using cakephp 3

Question

I have two tables "categories" and "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 to get result with apply limit(2) and order by score on "items" table by using cakephp 3 Output Like -

{
    "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"
        }
    }

Do you want a query Mysql for extract data in that way? – etsa Apr 06 '17 at 13:35 — etsa, Apr 06 '17 at 13:35

score 0 · Answer 1 · answered Apr 06 '17 at 15:53

0

Use contain:

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

Untested – commented on an iPhone. ;)

answered Apr 06 '17 at 15:53

Marijan

1,825
1
13
18

That won't work, the ORM isn't capable of limiting associations per group, that can be a tricky task. **[tag:greatest-n-per-group]** | **http://google.com/search?q=mysql+related+greatest+n+per+group**. Unless someone beats me to it, I'll add a quick and dirty example for `hasMany` associations later on to **http://stackoverflow.com/questions/30241975/how-to-limit-contained-associations-per-record-group**. – ndm Apr 06 '17 at 17:48
I'm too busy right now to pursue this further... just put together a MySQL specific custom association for testing purposes, based on `ROW_NUMBER()` emulation similar to **http://stackoverflow.com/q/9969126/1392379**. If anyone's interested: **https://gist.github.com/ndm2/039da4009df1c5bf1c262583603f8298** – ndm Apr 06 '17 at 22:23

Find records from database based on second table using cakephp 3

1 Answers1