cakephp build query to filter on model OR associated model

Question

I want to find rows by filtering on a table's column, OR on an associated table's column. I can get an AND version working, but I'm not sure how to change the condition between the ->where() and the ->matching() to OR.

In this example I want to find a user by either their username or their regular name. In this case a User hasMany Names.

$users = $this->Users->find()
    ->where(['username LIKE' => '%' . $search_term . '%'])
    ->matching('Names', function($q) use ($search_term){
        return $q->where(function($exp, $query) use ($search_term) {
            $full_name = $query->func()->concat([
                'first' => 'identifier', ' ',
                'last' => 'identifier'
            ]);
            return $exp->like($full_name, '%' . $search_term . '%');
        });
    })
    ->contain(['Names'])
    ->limit(10)
    ->all();

It always helps if you specify the types of the involved associations, `hasMany`, `belongsTo`, etc., there's different solutions depending on the type. — ndm, Jan 01 '19 at 23:25
oh, I didn't realize. In this case it is a User `hasMany` Names association. — waspinator, Jan 01 '19 at 23:36

score 1 · Accepted Answer · answered Jan 02 '19 at 01:30

matching() creates INNER joins, with the conditions applied in the join's ON clause, causing users to be filtered out if no matching names exist, hence you cannot use this to create the OR condition that you are looking for.

You could for example move your names check into a correlated subquery (a query that references columns of the outer query), and use it with EXISTS, something along the lines of this:

$namesMatcher = $this->Users->Names
    ->find()
    ->select(['Names.id'])
    ->where(function($exp, $query) use ($search_term) {
        $full_name = $query->func()->concat([
            'Names.first' => 'identifier', ' ',
            'Names.last' => 'identifier'
        ]);

        return $exp
            ->equalFields('Names.user_id', 'Users.id')
            ->like($full_name, '%' . $search_term . '%');
    });

$users = $this->Users->find()
    ->where(function($exp, $query) use ($search_term, $namesMatcher) {
        return $exp
            ->or_(['Users.username LIKE' => '%' . $search_term . '%'])
            ->exists($namesMatcher);
    })
    ->contain(['Names'])
    ->limit(10)
    ->all();

The generated query would look something like this:

SELECT
    Users.id ...
FROM
    users Users
WHERE
    Users.username LIKE '%term%' OR
    EXISTS(
        SELECT
            Names.id
        FROM
            names Names
        WHERE
            Names.user_id = Users.id AND
            CONCAT(Names.first, ' ', Names.last) LIKE '%term%'
    )

This could also be solved using for example a LEFT join, so that the user records aren't being affected by the join (see Query::leftJoinWith()), allowing you to create an OR condition accordingly, but since you do not seem to need further access to the names in the query, there's not really a point in selecting them.

cakephp build query to filter on model OR associated model

1 Answers1