Laravel whereHas on multiple relationships

Question

Does anyone know if this new feature can be performed on multiple relationships?

For example, I have a query where I want to filter on not only the club name (related question) but also the territory name.

In this example, I'd like query results where the club (club relationship) name is Arsenal and the the region is Australia (territory relationship)

$ret->with('territory')->with('homeClub')->with('awayClub'); $ret->whereHas('territory',function( $query ){ $query->where('region','Australia'); })->whereHas('homeClub', function ( $query ) { $query->where('name', 'Arsenal' ); })->orWhereHas('awayClub', function ( $query ) { $query->where('name', 'Arsenal' ); });

When executing this query - the result isn't constraining the territory whereHas just the clubs one.

Can whereHas be chained to filter the results on previous relationship's whereHas? Any suggestions if not?

thanks

jon

edi9999 · Accepted Answer · 2014-01-25 21:57:11Z

Yes that's possible.

The generated SQL will probably be:

SELECT * FROM ... WHERE (territory constraint) AND (homeClub constratint) OR (awayClub constraint)

This means that if awayClub constraint is satisfied, the line will be retrieved. I think you want to add a parenthesis to the generated sql:

SELECT * FROM ... WHERE (territory constraint) AND ((homeClub constratint) OR (awayClub constraint))

to do that, you need to nest both queries inside a where:

$ret->with('territory')->with('homeClub')->with('awayClub'); $ret->whereHas('territory',function( $query ){ $query->where('region','Australia'); }) ->where(function($subQuery) { $subQuery->whereHas('homeClub', function ( $query ) { $query->where('name', 'Arsenal' ); }) ->orWhereHas('awayClub', function ( $query ) { $query->where('name', 'Arsenal' ); }); });

Thanks for the reply.. That syntax doesn't seem to work. It doesn't seem that whereHas can be used in a subquery. I get the error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'has' in 'where clause' (SQL: select * from broadcasts` where (select count() from uploads where broadcasts.upload_id = uploads.id and type = international-audience) >= 1 and (select count() from territories where broadcasts.territory_id = territories.id and region = Australia) >= 1 and (has = homeClub))`
It seems like the orWhereHas behaves strangely when you enforce other constraints. Refer to my other SO question with generated SQL. cheers
Worked for me! I was doing something similar with a home team and away team related to a games table. Debugbar told me this is the result: select * from games where season = '2' and series = '1' and ((select count() from teams where teams.id = games.home_team_id and user_id = '1') >= 1 or (select count() from teams where teams.id = games.visitor_team_id and user_id = '1') >= 1) order by series asc, home_team_id asc, game_number asc ...I think at this point I'm better off refactoring into a view, though this is a good exercise.

HowApped · Accepted Answer · 2014-01-27 11:57:28Z

Thanks to being shown in the right direction by edi9999 - I wasn't properly considering parameter grouping

use the main eloquent object in the anonymous function, I placed a whereHas and an orWhereHas constraint on the homeClub and awayClub and that did the trick.

$ret->with('territory')->with('homeClub')->with('awayClub')->with('programme'); $ret ->whereHas('territory',function( $query ) use ( $parameterValues ){ $query->where('region', $parameterValues['region_names'] ); }) ->whereHas('season', function ( $query ) use ( $parameterValues ){ $query->where('name', $parameterValues['season_names'] ); }) ->where( function( $subquery ) use ( $ret ){ $ret->whereHas('homeClub', function ( $query ){ $query->where('name','Arsenal' ); } ); $ret->orWhereHas('awayClub', function ( $query ){ $query->where('name','Arsenal'); }); });

sanu · Accepted Answer · 2015-11-10 07:06:12Z

I don't think you need 'with' as there is 'wherehas'

Model::whereHas('territory',function( $query ){ $query->where('region','Australia'); })->whereHas('homeClub', function ( $query ) { $query->where('name', 'Arsenal' ); })->orWhereHas('awayClub', function ( $query ) { $query->where('name', 'Arsenal' ); });

hackernewbie · Accepted Answer · 2022-02-08 11:24:34Z

 $homeAndLiving = Product::with('user')->whereHas('category',function($query){ $query->where('category_slug', 'home--living') ->where('is_active','=','on'); }) ->whereHas('user',function($q){ $q->where('is_enabled',1); })->inRandomOrder()->take(6) ->get();

This is what works great for me.

Collectives™ on Stack Overflow

Laravel whereHas on multiple relationships

4 Answers 4

3 Comments

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

Comments

Linked

Related