I have an association of Price
belongsTo Season
I am trying to query all prices that match a specific date range when passed in the season as well as any that have none (Prices.season_id=0
)
Here is what I have:
// build the query
$query = $this->Prices->find()
->where(['product_id'=>$q['product_id']])
->contain(['Seasons']);
if(!empty($to_date) && !empty($from_date)) {
$query->matching('Seasons', function ($q) {
return $q->where([
'from_date <= ' => $to_date,
'to_date >= ' => $from_date
]);
});
}
However, this will only return Prices explicitly associated with a Season. How do I make it return Prices.season_id=0 also?
The
$query->matching()
call internally creates aINNER JOIN
and places the where-statements of the callback-function into theON
clause of the join. For retrieving items without the association you need aLEFT JOIN
. So your codesnippet would look like this:So we create a normal
INNER JOIN
and place the conditions in the normal (outmost)where
clause of the query.The double array is for disambiguation of probably other where conditions with an
or
connection.I myself stumbled over the
column IS NULL
instead of'column' => null
syntax.PS: This works for all associations. For
hasMany
andbelongsToMany
you have to group the results with$query->group('Prices.id')