How to filter by conditions for associated models?

Question

I have a belongsToMany association on Users and Contacts.

I would like to find the Contacts of the given User. I would need something like

$this->Contacts->find()->contain(['Users' => ['Users.id' => 1]]);

The cookbook speaks about giving conditions to contain, custom finder methods and sing through association key, but I did not find out how to put these together.

Answer 1

Use Query::matching() or Query::innerJoinWith()

When querying from the Contacts table, then what you are looking for is Query::matching() or Query::innerJoinWith(), not (only) Query::contain().

Note that innerJoinWith() is usually preferred in order to avoid problems with strict grouping, as matching() will add the fields of the association to the select list, which can cause problems as they are usually not functionally dependent.

See Cookbook > Database Access & ORM > Query Builder > Filtering by Associated Data

Here's an example using your tables:

$this->Contacts
    ->find()
    ->innerJoinWith('Users', function(\Cake\ORM\Query $q) {
        return $q->where(['Users.id' => 1]);
    });

This will automatically add the required joins + conditions to the generated query, so that only those contacts are being retrieved that are associated to at least one user with the id 1.

Target the join table

In case you'd have a manually set up many to many association via hasMany and belongsTo, you can directly target the join table:

$this->Contacts
    ->find()
    ->innerJoinWith('ContactsUsers', function(\Cake\ORM\Query $q) {
        return $q->where(['ContactsUsers.user_id' => 1]);
    });

Including containments

In case you actually want to have all the associations returned in your results too, then just keep using contain() too:

$this->Contacts
    ->find()
    ->contain('Users')
    ->innerJoinWith('Users', function(\Cake\ORM\Query $q) {
        return $q->where(['Users.id' => 1]);
    });

That would contain all users that belong to a contact.

Restricting containments

In cases where you have multiple matches, and you'd wanted to contain only those matches, you'd have to filter the containment too. In this example it doesn't make much sense since there would be only one match, but in other situations it might be useful, say for example if you'd wanted to match all contacts that have active users, and retrieve the contacts including only the active associated users:

$this->Contacts
    ->find()
    ->contain(['Users' => function(\Cake\ORM\Query $q) {
        return $q->where(['Users.active' => true]);
    }])
    ->innerJoinWith('Users', function(\Cake\ORM\Query $q) {
        return $q->where(['Users.active' => true]);
    })
    ->group('Contacts.id');

Given that there could be duplicates, ie multiple active users for a single contact, you'll probably want to group things accordingly, in order to avoid retrieving duplicate contact records.

Deep associations

You can also target deeper associations that way, by using the dot notated path syntax known from Query::contain(). Say for example you had a Users hasOne Profiles association, and you want to match only on those users that want to receive notifications, that could look something like this:

->innerJoinWith('Users.Profiles', function(\Cake\ORM\Query $q) {
    return $q->where(['Profiles.receive_notifications' => true]);
})

This will automatically create all the required additional joins.

Select from the other table instead

With these associations and your simple requirements, you could also easily query from the other side, ie via the Users table and use just Query::contain() to include the associated contacts, like

$this->Users
    ->find()
    ->contain('Contacts')
    ->where([
        'Users.id' => 1
    ])
    ->first();

All the contacts can then be found in the entities contacts property.