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.
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();
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With