Laravel Eloquent - Filter Relationship of Relationship

Question

This is the query I tried to do:

$products = Product::with(['cross_selling.product_related' => 
   fn($query) => $query->where('status_id', '=', '2')])
  ->where('status_id',2)
  ->get();

I get this results:

In cross_selling I still get id:12 that doesn't has any product_related. I need some help for making that the query doesn't give me that item if product_related is null.

Tks.

Answer 1

First get only those cross selling which has any related products after that get related products. Use the whereHas condition on cross_selling.

$products = Product::with(['cross_selling' => function($qry){
    $qry->whereHas('product_related', function ($q) {
        $q->where('status_id', 2);
    });
    }, 'cross_selling.product_related' =>
    fn($query) => $query->where('status_id', '=', '2')])
    ->where('status_id',2)
    ->get();

Answer 2

Try this

$products = Product::with(['cross_selling.product_related' => 
   fn($query) => $query->where('status_id', '=', '2')])
  ->has('cross_selling.product_related')
  ->where('status_id',2)
  ->get();

Answer 3

If you don't want cross selling products with no related products you can add

->whereHas('product_related')

Which basically says: Get me only cross selling which have 'product_related'.

In order to do it properly, you can:

$callback = fn($query) => $query->where('status_id', 2);
$products = Product::whereHas('cross_selling.product_related', $callback)
->with(['cross_selling.product_related' => $callback])
->where('status_id',2)->get();

Now the callback is applied to the whereHas() and the with() plus you can pass additional constraints using $callback.