Laravel paginate pivot tables

Question

I have collections which contains custom products I need to paginate those collections products but I receive error.

data

With this query I can get my collection and it's products but I'm not able to paginate products.

$collection = Collection::where('slug', $slug)->where('status', 'active')->with('products')->first();

With this query I receive error

$collection = Product::with('collections')->whereHas('collections', function($query) use($slug) { $query->where('slug', $slug)->where('status', 'active')->first(); });

Error

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'shopping.product_id' doesn't exist (SQL: select * from `collection_products` inner join `product_id` on `collection_products`.`id` = `product_id`.`collection_product_id` where `products`.`id` = `product_id`.`product_id` and `slug` = test and `status` = active limit 1)

Code

Product model

public function collections()
    {
        return $this->belongsToMany(CollectionProduct::class, 'product_id');
    }

Collection model

public function collection(){
        return $this->belongsToMany(CollectionProduct::class);
    }

    public function products(){
        return $this->belongsToMany(Product::class, 'collection_products', 'collection_id', 'product_id');
    }

CollectionProduct model

public function collection()
    {
        return $this->belongsTo(Collection::class, 'collection_id','id');
    }

Controller default query

public function single($slug){
  $collection = Collection::where('slug', $slug)->where('status', 'active')->with('products')->first();
  return view('front.collections.single', compact('collection'));
}

Question

How can I get my collection products with pagination ability?

newUserName02 newUserName02 · Accepted Answer · 2019-02-15T03:15:00

Couple things:

You are trying to call the first() method inside of a relationship query in this line:

$collection = Product::with('collections')->whereHas('collections', function($query) use($slug) { $query->where('slug', $slug)->where('status', 'active')->first(); });

The methods first() and get() are used to execute the query, so you should keep them at the end of the chain of eloquent methods:

$collection = Product::with('collections')
                     ->whereHas('collections', function($query) use($slug) { 
                         $query->where('slug', $slug)->where('status', 'active'); 
                     })
                     ->get();

https://laravel.com/docs/5.7/eloquent#retrieving-models

However, if you want to paginate the list of products, then what you really want is the paginate() method:

$collection = Product::with('collections')
                     ->whereHas('collections', function($query) use($slug) { 
                         $query->where('slug', $slug)->where('status', 'active'); 
                     })
                     ->paginate(20);   // will return 20 products per page

https://laravel.com/docs/5.7/pagination

Also, the collections() method on your Product model has product_id listed as the join table, and is joining to the CollectionProduct model instead of the Collection model.

Try this instead for your Product model:

public function collections()
{
    return $this->belongsToMany(Collection::class, 'collection_products', 'product_id', 'collection_id');
}