CakePHP 3.4: query where or matching

Question

I read the cookbook, but I can not figure out how to combine in a single query a matching() and a orWhere().

Example: I have Photo that belongs from Album. Both have the active field. So I'm trying to write a findInactive() method. A "inactive" photo has the active field as false or matching an album that has the active fields as false.

Something like this:

public function findInactive(Query $query, array $options)
{
    $query->matching('Albums', function ($q) {
            return $q->where(['Albums.active' => false]);
        })
        ->orWhere(['Photos.active' => false])
        ->enableAutoFields(true);

    return $query;
}

But that does not work:

'SELECT [...] FROM photos Photos INNER JOIN photos_albums Albums ON (Albums.active = :c0 AND Albums.id = (Photos.album_id)) WHERE Photos.active = :c1'

How to do? Thanks.

EDIT

Maybe a possible solution is usecontain():

    $query->contain(['Albums => ['fields' => ['active']]])
        ->where(['Photos.active' => false])
        ->orWhere(['Albums.active' => false]);

But is it not possible to use matching() or innerJoinWith()?

If a `Photo` **belongsTo** `Album` then your last solution seems the simplest one. The contain creates the JOIN. I don't see the need to use other solution — arilia, Apr 12 '17 at 11:56

score 2 · Answer 1 · answered Apr 12 '17 at 12:15

Add the conditions to the main query

matching() or innerJoinWith() with conditions is the wrong way to go, as the conditions are being addded to the INNER joins ON clause, which will cause the Photo row to be excluded in case the Albums.active condition doesn't match.

If you want to only receive photos that belong to an album, then you want to use matching() or innerJoinWith(), but you'll have to add the conditions to the main query instead, ie:

$query
   ->innerJoinWith('Albums')
   ->where(['Albums.active' => false])
   ->orWhere(['Photos.active' => false])
   // ...

In case a photo doesn't have to belong to an album, or it's not important whether it does, you could use either leftJoin(), leftJoinWith(), or even contain().

The latter however may use the INNER joinStrategy and/or the select strategy (which uses a separate query), so you'd need to take care of ensuring that it uses LEFT and join instead. Using containments however is usually only advised if you actually want to contain something, and given that your finder seems to be supposed to just filter things, I'd say go with leftJoinWith() instead:

$query
   ->leftJoinWith('Albums')
   ->where(['Albums.active' => false])
   ->orWhere(['Photos.active' => false])
   // ...

CakePHP 3.4: query where or matching

1 Answers1

Add the conditions to the main query

See also