Eloquent with Nested Wherehas

Eloquent with nested whereHas

Update: the PR has been just merged to 4.2, so now it's possible to use dot nested notation in has methods ( ->has('relation1.relation2) ->whereHas('relation1.relation2, .. )

Your question remains a bit unclear or you misunderstand whereHas() method as it is used to filter models (users in this case) and get only those that have related models fitting search conditions.

It seems that you want to find Packages from the context of a given User, so no need to use whereHas method.

Anyway depending on the relations (1-1,1-m,m-m) this can be easy or pretty hard and not very efficient. As I stated, loading nested relations means that for every level of nesting comes another db query, so in this case you end up with 5 queries.

Regardless of the relations you can invert this chain like this, as it will be easier:

---

edit: This is not going to work atm as whereHas() doesn't process dot nested relations!

// given $user and $search:
$packages = Package::where('alias','like',"%$search%")
  ->whereHas('product.membership.club.user', function ($q) use ($user) {
    $q->whereId($user->id);
  })->get();

---

As you can see this is much more readable, still runs 5 queries.
Also this way you get $packages, which is a single Collection of the models you wanted.

While from the context of a user you would get something like this (depending on the relations again):

$user
 |-club
 |  |-membership
 |  |  |-product
 |  |  |  |-packages
 |  |  |-anotherProduct
 |  |  |  |-packages
 |  |  |-yetAnotherProduct
 |  |     |-packages
 |  |-anotherMembership
  .....

You get the point, don't you?

You could fetch the packages from the Collection, but it would be cumbersome. It's easier the other way around.

So the answer to your question would be simply joining the tables:

// Let's assume the relations are the easiest to handle: 1-many
$packages = Package::where('alias','like',"%$search%")
  ->join('products','packages.product_id','=','products.id')
  ->join('memberships','products.membership_id','=','memberships.id')
  ->join('clubs','memberships.club_id','=','clubs.id')
  ->where('clubs.user_id','=',$user->id)
  ->get(['packages.*']); // dont select anything but packages table 

Of course you can wrap it in a nice method so you don't have to write this everytime you perform such search.
Performance of this query will be definitely much better than separate 5 queries shown above. Obviously this way you load only packages, without other related models.

Laravel - Get nested relation with whereHas

Seems that your models are a little bit "messy" you can get what you want with Eloquent but you've to split the logic into a few parts.

Example:

// Get all user seen posts #1
$seenPosts = Auth::user()->views()->pluck('id');

// Get all user unseen posts with its period inside the collection #2
$posts = Post::whereNotIn('id',$seenPosts)->with('periods')->get(); 

Nested relation whereHas in laravel

Okay for a while I was think about my problem finally I got the answer. Okay for sharing to everyone I will explain a little bit for the answer.

So the query what I wrote on the controller, I change into this :

$getData = StockIn::with(['type_of_items' => function ($type_of_item) {
            $type_of_item->select('id_type_item', 'type_id_item', 'code_type_of_item', 'type_of_item')
            ->with(['items' => function ($item) {
                $item->select('id_item', 'item_id_common_unit', 'name_item')
                ->with(['common_units' => function ($common_unit) {
                    $common_unit->select('id_common_unit', 'name_unit');
                }]);
            }]);
        }])
        ->with(['stock_out_left_join' => function ($stock_out_left_join) {
            $stock_out_left_join->select('id_stock_out', 'stock_out_id_stock_in');
        }])
        ->whereHas('type_of_items', function ($type_of_items_search) use ($search) {
            $type_of_items_search->where('code_type_of_item', 'like', "%{$search}%")
                ->orWhere('type_of_item', 'like', "%{$search}%");
        })
        ->orWhereHas('type_of_items.items', function ($items_search) use ($search) {
            $items_search->where('name_item', 'like', "%{$search}%");
        })
        ->orWhereHas('type_of_items.items.common_units', function ($common_units_search) use ($search) {
            $common_units_search->where('name_unit', 'like', "%{$search}%");
        })
        ->orWhere('created_by', 'like', "%{$search}%")
        ->orWhere('edited_by', 'like', "%{$search}%")
        ->get()
        ->toArray();

As you can see my new query has a new parameter in every with function and I was naming all the parameter with different name, and not like first name before query, so the problem is the ambiguous paramater in every single with function because this query based on nested relation so I must make the parameter name different each other. Or you want make them into split one by one and not using the nested with function you can use this query too, I put on this bellow :

$getData = StockIn::with(['type_of_items' => function ($query) {
            $query->select('id_type_item', 'type_id_item', 'code_type_of_item', 'type_of_item');
        }])
        ->with(['type_of_items.items' => function ($query) {
            $query->select('id_item', 'item_id_common_unit', 'name_item');
        }])
        ->with(['type_of_items.items.common_units' => function ($query) {
            $query->select('id_common_unit', 'name_unit');
        }])
        ->with(['stock_out_left_join' => function ($query) {
            $query->select('id_stock_out', 'stock_out_id_stock_in');
        }])
        ->whereHas('type_of_items', function ($query) use ($search) {
            $query->where('code_type_of_item', 'like', "%{$search}%")
                ->orWhere('type_of_item', 'like', "%{$search}%");
        })
        ->orWhereHas('type_of_items.items', function ($query) use ($search) {
            $query->where('name_item', 'like', "%{$search}%");
        })
        ->orWhereHas('type_of_items.items.common_units', function ($query) use ($search) {
            $query->where('name_unit', 'like', "%{$search}%");
        })
        ->orWhere('created_by', 'like', "%{$search}%")
        ->orWhere('edited_by', 'like', "%{$search}%")
        ->get()
        ->toArray();

I already tried that query and it's work too (with the same name parameter in every single with function).

Multilevel relationship whereHas on eloquent Model in Laravel

Found the answer over here.

Project::with(['tasks' => function($q) {
            $q->whereHas('tags', function($query) {
                $query->where('tag_id', 1);
            });
}])->get();

Nested wherehas with different relation in Eloquent

From what I can understand, you need to do a nested whereHas, so:

public function scopeMyScope(Builder $query)
{
    // b is the name of the relationship...
    return $query->whereHas('b', function($q)
    {
         // c is the name of the relationship...
         $q->whereHas('c', function()
         {
             $q->where(1, 1);
         });
    }
}

Does this solve your problem?

Laravel Eloquent - Get nested relationships with filtered data

you can repeat the same condition on eager loading:

  $Categories = Categories::whereHas('subcategories', function ($q) use ($request) {
        $q->whereHas('products', function ($q) use ($request) {
            $q->where('name', 'LIKE', "%$request->search%")
                ->orWhere('article_number', 'LIKE', "%$request->search%");
        });
    })->with(['subcategories'=> function ($q) use ($request) {
        $q->with(['products'=>function ($q) use ($request) {
            $q->where('name', 'LIKE', "%$request->search%")
                ->orWhere('article_number', 'LIKE', "%$request->search%");
        }]);
    }])->get();

Eloquent query though multiple whereHas

I have refactored and corrected your query:

$frm = "some string";
$media = Media::with('ad')

                    // here we use nested whereHas (relation1.relation2.relation3), 
                    // it's easier to write and read
                ->whereHas('ad.time_slot', function($q){

                    // quotes added around the operator, 
                    // it was probably causing you a syntax error
                    // I also used the now() helper, which will give you the
                    // same result as $today = date('Y-m-d H:i:s')
                    $q->whereDate('from', '<=', now()); 
                    $q->whereDate('to', '>=', now());

                })->where('format', $frm)->inRandomOrder()->firstOrFail();

If the records exist and the relationships correctly configured, it should works.

---

Related Topics