orWhereHas - parameter grouping on Eloquent query - how do I do this in Laravel?

Question

In an eloquent query I am building, I am placing a constraint on a has relationship using Laravel 4.1's whereHas and orWhereHas methods.

In the example soccer application, I wish to place a constraint on the homeClub and awayClub relationships so that I can the result set will select where the homeClub = Arsenal OR awayClub = Arsenal.

This issue has evolved from an earlier question It seems that when using the orWhereHas method - the resulting sql doesn't group the or constraint.

The query (relevant excerpt that is placing the constraints):

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

            });
        } )
        ->whereHas('territory',function( $query ) use ( $parameterValues ){     
               $query->where('region','Australia');

        })->get()->toArray();

This produces the sql:

SELECT * FROM `broadcasts` WHERE 

(SELECT count(*) FROM `uploads` WHERE `broadcasts`.`upload_id` = `uploads`.`id` and `type` = 'international-audience') >= '1' 
and 
(SELECT count(*) FROM `clubs` WHERE `clubs`.`id` = `broadcasts`.`home_club_id` and `name` = 'Arsenal') >= '1' 
or 
(SELECT count(*) FROM `clubs` WHERE `clubs`.`id` = `broadcasts`.`away_club_id` and `name` = 'Arsenal') >= '1' 
and 
(SELECT count(*) FROM `seasons` WHERE `broadcasts`.`season_id` = `seasons`.`id` and `name` = '2012/13') >= '1' 
and 
(SELECT count(*) FROM `territories` WHERE `broadcasts`.`territory_id` = `territories`.`id` and `region` = 'Australia') >= '1'

But, this isn't what I want, because referring to the eloquent statement, the club queries are grouped and the query above either selects the homeClub constraints OR, the awayClub, season name, territory region. What I'm intending is the following SQL:

SELECT * FROM `broadcasts` WHERE 

(SELECT count(*) FROM `uploads` WHERE `broadcasts`.`upload_id` = `uploads`.`id` and `type` = 'international-audience') >= '1' 
and 
((SELECT count(*) FROM `clubs` WHERE `clubs`.`id` = `broadcasts`.`home_club_id` and `name` = 'Arsenal') >= '1' 
or 
(SELECT count(*) FROM `clubs` WHERE `clubs`.`id` = `broadcasts`.`away_club_id` and `name` = 'Arsenal') >= '1' )
and 
(SELECT count(*) FROM `seasons` WHERE `broadcasts`.`season_id` = `seasons`.`id` and `name` = '2012/13') >= '1' 
and 
(SELECT count(*) FROM `territories` WHERE `broadcasts`.`territory_id` = `territories`.`id` and `region` = 'Australia') >= '1'

Note.. the parentheses on the club subquery.

Does anyone know how I would write this as the eloquent query? I really don't want to have to revert to fluent / joins.

Answer 1

You need to reference the query passed through to the where closure. Otherwise you are adding the grouped where clauses to the main query bypassing any groupings:

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