Laravel Eloquent - Filter Relationship

Question

I have two main models in my Laravel project. ProductMaster is where the main product data is and Product is the model which holds the variation of each product and the price for each client. I am not allowed to change this model, in case you wonder.

My question is how to make a eloquent query to get the ProductMaster data with Products that are filtered by client (and other parameters too). I tried whereHas but it didn't work.

This are my Models:

namespace App\Models;
use Illuminate\Database\Eloquent\Model;

class ProductMaster extends Model
{
    protected $table = 'product_masters';

    protected $primaryKey = 'id';

    protected $fillable = ['code','title'];
    
    public function products(){
        return $this->hasMany(Product::class,'master_id');
    }
}


namespace App\Models;
use Illuminate\Database\Eloquent\Model;
use App\Models\ProductMaster;

class Product extends Model
{

    protected $table = 'products';
    protected $primaryKey = 'id';

    protected $fillable = ['sku', 'stock', 'brand', 'size', 'priceList', 'master_id', 'client'];

    public function productMaster(){
        return $this->belongsTo(ProductMaster::class,'master_id');
    }

}

And this is the query I tried to do:

        //QUERY WITH FILTERS
        $products = ProductMaster::whereHas('products', function($query) 
            use($filterBrand, $filterCats, $filterSizes, $clientCode)
            {
                $query->where('client', $clientCode);
                $query->whereIn('brand', $filterBrand);
                $query->whereIn('size', $filterSizes);
            })
        ->where('title', 'LIKE', '%' . $request->find . '%')
        ->orderby($order[0], $order[1])
        ->paginate(6)
        ->withQueryString();

This query works but I don't get exactly what I need. This gives me all ProductMaster that have products where has that parameters, but in collection $products it puts ALL products that have that master_id and not only the products that has that parameters.

This is the sql:

select * from `product_masters` where exists (select * from `products` where `product_masters`.`id` = `products`.`master_id` and `client` = ? and `products`.`brand` in (?) and `category` in (?) and `client` = ?) and `title` LIKE ? order by `title` asc

Here is some sample data: SQL Fiddle

Anyone can help me? Thanks.

Can you show query log from `$products` and show what inside `$filterBrand, $filterCats, $filter...` — Aslam H, Jul 09 '21 at 04:26
I have add the query to my question (I used the toSql method). The $filterBrand, cats etc are just array with codes. For example $filterBrand = ['SPRITE', 'FANTA'] — Fryla- Cristian Marucci, Jul 09 '21 at 13:29
`public function products(){ return $this->hasMany(Product::class,'master_id'); }` This line looks wrong, I am assuming that `ProductMaster` table doesn't have a column called `master_id`? — damask, Jul 13 '21 at 04:27
No, but Product do have a master_id column and that is the relationship. — Fryla- Cristian Marucci, Jul 13 '21 at 17:54

Augusto Alonso · Accepted Answer · 2021-07-13T02:56:17.767

2

If you want to filter the the products to eager load and also filter to load only the product masters that hace those products your query should be like this:


$products = ProductMaster::
with([
 'products' =>
 fn($query) => $query
 ->where('client', $clientCode)
 ->whereIn('brand', $filterBrand)
 ->whereIn('size', $filterSizes)
])
->whereHas('products', 
 fn($query) => $query
 ->where('client', $clientCode)
 ->whereIn('brand', $filterBrand)
 ->whereIn('size', $filterSizes)
)
        ->where('title', 'LIKE', '%' . $request->find . '%')
        ->orderby($order[0], $order[1])
        ->paginate(6)
        ->withQueryString();

You could even pass that query on both with and whereHas functions to a private function inside your controller to keep the clone more clean.

edited Jul 13 '21 at 02:56

answered Jul 12 '21 at 17:31

Augusto Alonso

334
3
5

This returns an error: mb_strpos() expects parameter 1 to be string, object given. – Fryla- Cristian Marucci Jul 12 '21 at 18:28
1

I fixed the missing => in the with statement thanks. – Augusto Alonso Jul 13 '21 at 16:18
I think you nailed it !! I am going to test it further and confirm the answer later. Tks a lot ! – Fryla- Cristian Marucci Jul 13 '21 at 18:03
You were right! The bounty is all yours and I really thanks you for your help. – Fryla- Cristian Marucci Jul 13 '21 at 20:01

PSA · Answer 2 · 2021-07-13T05:21:07.923

Could you please try this and let me know if its working or not ? And also I think you missed the category filtering in one of the whereIn clause.

$q = ProductMaster::with('products')->where(function($query) use ($filterBrand, $filterCats, $filterSizes, $clientCode) {
     $query->whereHas('products', function($query) use ($filterBrand, $filterCats, $filterSizes, $clientCode) {
        $query->where('client', $clientCode);
        $query->whereIn('brand', $filterBrand);
        $query->whereIn('size', $filterSizes);
        $query->whereIn('category', $filterCats);
    });
 });

 $products = $q->where('title', 'LIKE', '%' . $request->find . '%')
                ->orderby($order[0], $order[1])
                ->paginate(6)
                ->withQueryString();

Not working, I get all products without being filtered by client. For making it more simple, forget about the other filters if you want to, client filter is the most important for me. Tks. — Fryla- Cristian Marucci, Jul 13 '21 at 17:57

Laravel Eloquent - Filter Relationship

2 Answers2