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

Answer 1

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' );
        });
    });