Cakephp 3 Associative Limit Per Row

Question

I am trying to get products under the Categories. Here Categories hasMany products. I am trying to fetch 3 products under the category. I have tried below code in controller

public function test()
{
        $this->viewBuilder()->layout(false);
        $Menus = $this->loadModel( 'Categories' );
        $menu_top = $Menus
                    ->find()
                    ->contain(['Subcats','Products'=>function($q{return $q->limit(3);}])

        $this->set('menu_top',$menu_top);
}

After @ndm comment I am going to add more details.

Example I have 3 Categories, and all categories has lots of products

A      B      C
  ->p1   ->p1   ->p1
  ->p2   ->p2   ->p2
  ->p3   ->p3   ->p3
  ->p4   ->p4   ->p4
   ...    ...    ...

I am trying to fetch 3 products per categories like below

A      B      C
  ->p1   ->p1   ->p1
  ->p2   ->p2   ->p2
  ->p3   ->p3   ->p3

If I used ->contain(['Subcats','Products'=>function($q{return $q->limit(3);}])

It actually limiting my total products. Example now I am getting like

A      B      C
->p1   ->     ->
->p2   ->     ->
->p3   ->     ->

Here I need row wise limit not total limit on products.

Whenever receiving errors, please always post the _complete_ error including the _full_ stacktrace (ideally copied from the logs where it is available in a properly readable fashion) - thanks! That being said, you cannot limit associations per record/group like that, see for example **https://stackoverflow.com/questions/30241975/how-to-limit-contained-associations-per-record-group**. — ndm, Sep 19 '19 at 10:49
Possible duplicate of [Using limit() on contained model](https://stackoverflow.com/questions/29775766/using-limit-on-contained-model) — Alimon Karim, Sep 19 '19 at 11:10
@AlimonKarim That question is about `hasOne`, not `hasMany`. Also the accepted answer for the question would only work when retrieving a single record, with multiple records you cannot use `limit()` on containments, as it will limit the overall number of associated records, not the number of records per row/group. — ndm, Sep 19 '19 at 11:34
@ndm your comment is right, I am still unable to solve this problem. Also updated my question. — Satu Sultana, Oct 01 '19 at 11:28
Yeah, there's pretty much only two options when using the ORM, a) a custom association as shown/linked in my answer to the linked question above, b) retrieving and merging the associated records manually in for example a result formatter (ideally with a single additional query, so that you don't end up with n+1 queries). — ndm, Oct 01 '19 at 13:21

score 0 · Answer 1 · answered Sep 19 '19 at 12:33

You are getting the error because you have passed string inside limit. Limit parameter should be integer always.

public function test()
{
        $this->viewBuilder()->layout(false);
        $Menus = $this->loadModel( 'Categories' );
        $menu_top = $Menus
                    ->find()
                    ->contain(['Products' , function ($q){
                        return $q
                               ->limit(3);
                    }]);

        $this->set('menu_top',$menu_top);
}

score 0 · Answer 2 · answered Sep 20 '19 at 12:09

The way you are passing the arguments to contain is not correct. Below is how you should use pass using arrow, otherwise Cakephp assumes it as another array value passed and hence the error.

    $menu_tops = $Menus
         ->find()
         ->contain([
            'Products' => function($q) {
                return $q
                    ->limit('3');
    }
    ]);     

    foreach( $menu_tops as $key => $value ) {
        debug( $value ); 
    }

score 0 · Answer 3 · answered Sep 21 '19 at 04:20

If you try to get product under the category you can query categories and contain the products. As you mention your table should be one categories has many product. This query example my help:

$conditions = [];
$query = $this->Categoies->find()
        ->select([
            'Categoies.id',
            'Categoies.name',
        ])
        ->contain([
            'Products' => function ($q) {
            return $q->select(['Products.id', 'Products.category_id', 
                              'Products.name']);
        ])
        ->where($conditions);

Cakephp 3 Associative Limit Per Row

3 Answers3