Laravel - conditional filtering with relation table

Question

I have to conditionally filter results in the below query, based on $search is present or not. Below is my query,

    Sellers::with(
        'shop:id,width,height,business_id'
    )->with(
        'unit.location:id,name as l_name'
    )->select(
        'sellers.id', 'sellers.name' , 'address'
    )->when($search, function ($query, $search) {
         $query->where('l_name', 'like', '%' . $search . '%');
    })
    ->findOrFail($request->seller_id);

The relations are,

Sellers has many units. Units has one location.

I need to filter the above result for location names based on the availability of search param. I have tried adding aliases, but they are throwing errors.

How can this be attained?

you can look into whereHas - Querying Relationship Existence — lagbox
– lagbox, Commented Jul 2, 2020 at 12:35
Tried it. It doesn't seem to work with relations like unit.location. — Abey
– Abey, Commented Jul 2, 2020 at 12:36
What error it throws ? also try this code function ($query) use ($search) instead of function ($query, $search) — Akhtar Munir
– Akhtar Munir, Commented Jul 2, 2020 at 12:41
@lagbox This is what i tried. Sellers::with( 'widget:id,width,height,business_id' )->whereHas('unit.location', function($q) use($search) { $q->where('locations.name', $search); }) ->select( '*' ) ->findOrFail($request->seller_id); Location array is empty in all cases. What am I doing wrong here? — Abey
– Abey, Commented Jul 2, 2020 at 12:45

motia · Accepted Answer · 2020-07-02 12:46:08Z

1

The with expression in the query builder loads the relations with a second query, ex:

select * from posts; // returns posts with ids 1, 2, 3
select * from comments where posts_id in (1,2,3);

So for your query: $query->where('l_name', 'like', '%' . $search . '%'); is causing a SQL error as the locations table is not joined.

You have two ways to make your query:

Using a join:

Add a join for your locations table to your query

   ->join('units', ...)
   ->join('locations', ...)

By querying relations:

Replace the when expression in your example by:

    ->when($search, function ($query, $search) {
         $query->whereHas('unit.location', function () {
            $query->where('l_name', 'like', '%' . $search . '%');
         });
    })

This will add a where exists expression on the locations table that limits the search results. For example: (`select * from posts where exists (select 1 from comments where approved = true and comments.post_id = posts.id ))

answered Jul 2, 2020 at 12:46

motia

1,99918 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Abey Over a year ago

I have tried, Sellers::with( 'widget:id,width,height,business_id') ->with( 'unit.location' ) ->when($search, function ($query) use ($search) { $query->whereHas('unit.location', function ($query) use ($search) { $query->where('id', 2); }); }) ->findOrFail($request->seller_id); The filter in whereHas is still not working. Its always listing all results.

motia Over a year ago

Could you debug the SQL using toSql()> Also, it would be better to write the queries inside the Where Exists with the full name of the table because of the column name collisions. (example $query->where('locations.id', 2);

Abey Over a year ago

Also like you mentioned, locations table is joined, the unit.location syntax will join it automatically. All locations are displayed, the issue is when a filter is applied to the location name or id.

motia Over a year ago

No, the unit.location does not use joins and this is what is causing the error. Laravel will make multiple queries to load the relations (each per table). Please recheck the example in the first part of the answer.

Collectives™ on Stack Overflow

Laravel - conditional filtering with relation table

1 Answer 1

Using a join:

By querying relations:

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Using a join:

By querying relations:

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related