matching associations and no associated record cakephp 3

Question

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?

Answer 1

The $query->matching() call internally creates a INNER JOIN and places the where-statements of the callback-function into the ON clause of the join. For retrieving items without the association you need a LEFT JOIN. So your codesnippet would look like this:

if(!empty($to_date) && !empty($from_date)) {
    $query->leftJoinWith('Seasons', function ($q){return $q;});
    $query->where([[
        'or' => [
            'Season.id IS NULL',
            [
                'from_date <= ' => $to_date,
                'to_date >= ' => $from_date,
            ],
        ],
    ]]);
}

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 and belongsToMany you have to group the results with $query->group('Prices.id')