[FIXED] How to paginate associated records?

Issue

Products belongsToMany Categories and Categories hasMany Products, inside my Product view I'm showing a list of all it's categories but I want to paginate or limit these results.

My current code on ProductsController is:

$product = $this->Products
    ->findBySlug($slug_prod)
    ->contain(['Metas', 'Attachments', 'Categories'])
    ->first();

$this->set(compact('product'));

I know I need to set $this->paginate() to paginate something but I can't get it working to paginate the categories inside the product. I hope you guys can understand me.

UPDATE: Currently I have this going on:

$product = $this->Products->findBySlug($slug_prod)->contain([
              'Metas',
              'Attachments',
              'Categories' => [
                'sort' => ['Categories.title' => 'ASC'],
                'queryBuilder' => function ($q) {
                  return $q->order(['Categories.title' => 'ASC'])->limit(6);
                }
              ]
            ])->first();

The limit works but I don't know how to paginate yet

Solution

The paginator doesn't support paginating associations, you'll have to read the associated records manually in a separate query, and paginate that one, something along the lines of this:

$product = $this->Products
    ->findBySlug($slug_prod)
    ->contain(['Metas', 'Attachments'])
    ->first();

$categoriesQuery = $this->Products->Categories
    ->find()
    ->innerJoinWith('Products', function (\Cake\ORM\Query $query) use ($product) {
        return $query->where([
            'Products.id' => $product->id,
        ]);
    })
    ->group('Categories.id');

$paginationOptions = [
    'limit' => 6,
    'order' => [
        'Categories.title' => 'ASC'
    ]
];

$categories = $this->paginate($categoriesQuery, $paginationOptions);

$this->set(compact('product', 'categories'));

Then in your view template you can display your $product and separately paginate $categories as usual.

See also

• Cookbook > Controllers > Components > Pagination
• Cookbook > Views > Helper> Paginator
• Cookbook > Database Access & ORM > Query Builder > Filtering by Associated Data

Answered By - ndm

Answer Checked By - David Goodson (PHPFixing Volunteer)