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.