How to use where() for column that belong to with()

Question

I'm working with Laravel framework. I want to get the data for promotion with the certain category.

<b>Table</b><br>

product<br>
------<br>
id<br>
name<br>
price<br>

promotion<br>
----------<br>
id<br>
product_id<br>
discount<br>

category<br>
---------<br>
id<br>
name<br>

product_category<br>
----------------<br>
product_id<br>
category_id<br>

        $promotions = Promotion::with('product')->with('category') 
        ->whereIn('category_id', ['1','2'])
        ->paginate(9);

Promotion model - connect relation for product and category


// relationship with product 
 public function product()
{
    return $this->belongsTo('App\Model\Product');
}
//relationship with category
public function category() 
{
   return $this->belongsToMany('App\Model\Category', 'products_categories', 'product_id', 'category_id');
}

Error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'category.id' in 'where clause' (SQL: select count(*) as aggregate from promotions where category.id in (1, 2))

Answer 1

Try this:

$promotions = Promotion::with('product.category') 
    ->whereIn('category_id', ['1','2'])
    ->paginate(9);

Assuming that you have to create the correct relations in models.

Edit:

In the Promotion Model :

// relationship with product 
public function product()
{
    return $this->belongsTo('App\Model\Product');
}

and the Product Model:

//relationship with category
public function product_category() 
{
   return $this->belongsTo('App\Model\ProductCategory', 'product_id', 'id');
}

In the Category Model:

//relationship with category
public function product_category() 
{
   return $this->belongsToMany('App\Model\ProductCategory', 'category_id', 'id');
}

So the query like:

$promotions = Promotion::with('product.product_category') 
    ->whereIn('category_id', ['1','2'])
    ->paginate(9)

Answer 2

How about :

$promotions = Promotion::with([ 'product.category' => function($q){
    return $->whereIn('category_id', ['1','2']);
}]) 
->paginate(9);

If you want to get all promotions which have a category having id in 1,2 you can do :

$promotions = Promotion::whereHas('product.category' => function($q){
    return $->whereIn('category_id', ['1','2']);
}) 
->paginate(9);

This will just get you promotions and not the categories. if thats what you were looking for.

Answer 3

You can simply use this:

$promotions = Promotion::whereHas(
            'product.category',
            function ($q) {
                $q->whereIn('category_id', ['1', '2']);
            })
            ->paginate(9);