Many To Many Through Many To Many

Hello.

I have 3 models and their respective tables, along with a pivot table:

Categories: id, title

Products: id, title, category_id

Listings: id, title

Listings_Products: listing_id, product_id, price, is_sold

My models are like this:

class Listing extends Model
{
    public function products()
    {
        return $this->belongsToMany('App\Models\Product', 'listings_products');
    }

    /**
     * @param Builder $query
     * @param array $filters
     * @return Builder
     */
    public function scopeFilter(Builder $query, array $filters)
    {
        $query->with(['user', 'products.category']);

        if (isset($filters['product'])) {
            $query->where('listings_products.product_id', '=', $filters['product']);
        }
    
        return $query;
    }
}

class Product extends Model
{
    public function category()
    {
        return $this->belongsTo('\App\Models\Category');
    }
    
    public function listings() {
        return $this->belongsToMany('App\Models\Listing', 'listings_products');
    }
}
    
class Category extends Node
{
    protected $table = 'categories';
    
    public function products() {
        return $this->hasMany('\App\Models\Product');
    }   
}

I am trying to filter the list of the listings per category in the scopeFilter function of the model. For example if I select the category 5 I want all the listings that contain products that belong to that category or its descendants. I can get the descendants by using this

$categories = Category::find($filters['category'])->getDescendantsAndSelf()->pluck('id');

Any help would be much appreciated.

Thanks

Parasoul

8 years ago

Level 3

Hey this might interest you : https://softonsofa.com/laravel-querying-any-level-far-relations-with-simple-trick/

1 like

achatzi

8 years ago

Level 5

Thank you for the reply @Parasoul, much appreciated.

I made a few changes in my code, now the Category model is like this

class Category extends Node
{
    public function products()
    {
        return $this->hasMany('\App\Models\Product');
    }
    
    public function listings()
    {
        return $this->hasManyThrough('\App\Models\Listing', '\App\Models\Product');
    }
}

and in my Listing model I modified the scopeFilter function like this

public function scopeFilter(Builder $query, array $filters)
{
    if (isset($filters['category'])) {
        $categories = Category::find($filters['category'])->getDescendantsAndSelf()->pluck('id');
        
        $query->whereIn('products.category_id', $categories);
    }

    if (isset($filters['active'])) {
        $query->where('listings.is_active', $filters['active']);
    }

    return $query;
}

Finally, in my Listings controller I have this

$listings = Listing::with(['user', 'products.category'])->filter(request()->all())->paginate(10);

Unfortunatelly I get this error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'products.category_id' in 'where clause' (SQL: select count(*) as aggregate from `listings` where `products`.`category_id` in (2, 3, 9, 10, 8))

when I try to filter for a category.

Any ideas why it does not include the relations in the count function?

Parasoul

8 years ago

Level 3

You can't acces a relation in sql this way. You have to join first.

achatzi

8 years ago

Best Answer

Level 5

Ok, I figured it out, after I read a little bit more about querying relationships.

So now the scopeFilter function looks like this:

/**
 * @param Builder $query
 * @param array $filters
 * @return Builder
 */
public function scopeFilter(Builder $query, array $filters)
{
    if (isset($filters['created_range'])) {
        $query->whereBetween('listings.created_at', explode(' - ', $filters['created_range']));
    }
    
    if (isset($filters['title'])) {
        $query->where('listings.title', 'like', '%' . $filters['title'] . '%');
    }
    
    if (isset($filters['user'])) {
        $query->where('listings.user_id', '=', $filters['user']);
    }
    
    if (isset($filters['product'])) {
        $query->whereHas('products', function ($q) use ($filters) {
            $q->where('product_id', $filters['product']);
        });
    }
    
    if (isset($filters['category'])) {
        $categories = Category::find($filters['category'])->getDescendantsAndSelf()->pluck('id');
        
        $query->whereHas('products', function ($q) use ($categories) {
            return $q->whereHas('category', function ($q2) use ($categories) {
                $q2->whereIn('products.category_id', $categories);
            });
        });
    }
    
    if (isset($filters['active'])) {
        $query->where('listings.is_active', $filters['active']);
    }
    
    if (isset($filters['order']) && $filters['order'] == 'desc') {
        $order = 'desc';
    }
    else {
        $order = 'asc';
    }
    
    if (isset($filters['sort'])) {
        $query->orderBy('listings.' . $filters['sort'], $order);
    }
    
    return $query;
}

Thanks for the help!

1 like

Please or to participate in this conversation.