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.

Typebar

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.

Fawnfawna

Thanks, somehow I missed this part. – Typebar

Thanks. Yesterday accidentally I found an other way to do what you included in the update. $this->Contacts->find()->contain(['users'])->matching('Users', function($q) { return $q->where(['Users.id' => 1]); }); see: github.com/cakephp/cakephp/issues/5109 – Typebar

@Typebar Ah, I should have read the issue more carefully, on a first look I thought both wouldn't give you the desired results. Now someone needs to find out whether the casing trick is as realiable as the somewhat more "complex" additional association workaround :) – Fawnfawna

I am getting duplicate results when the condition is return $q->where(['ContactsUsers.user_id IN' =>[1,2,3]]); . Please let me know how to get Unique results? Or please answer Here #46910279 @Fawnfawna – Barriebarrientos

@SumonSarker Check the warning box under the linked docs section, you have to either do a distinct select, or apply grouping accordingly (both utilizing the primary key). – Fawnfawna

Thank you very much! (y) @Fawnfawna – Barriebarrientos

Answer 2

While @ndm answer got me where I needed to with a similar issue, solution needed a little tweak. Problem was getting users filtered by data on the joinTable adding matching didn't return the users. So this is what I ended up with, hope it'll help someone.

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

This got me Contacts with Users who has some_field set to 1 in association table. Or am I overcomplicating and there is a better solution?

Goodfellowship