`belongsToMany` associations are not respecting `conditions`.

[geoidesic]

Given a pivot table EnquiryContacts between table EnquiriesTable and PeopleTable with the following conditional associations on EnquiriesTable:

$this->belongsToMany('Administratives', ['through' => 'EnquiryContacts', 'className' => 'People', 'conditions' => ['role_id' => 4], 'foreignKey' => 'enquiry_id', 'targetForeignKey' => 'person_id']);
$this->belongsToMany('Clients', ['through' => 'EnquiryContacts', 'className' => 'People', 'conditions' => ['role_id' => 3], 'foreignKey' => 'enquiry_id', 'targetForeignKey' => 'person_id']);

and the corresponding reverse associations on PeopleTable:

$this->belongsToMany('AdministrativedEnquiries', ['through' => 'EnquiryContacts', 'className' => 'Enquiries', 'conditions' => ['role_id' => 4], 'foreignKey' => 'person_id', 'targetForeignKey' => 'enquiry_id', ]);
$this->belongsToMany('ClientEnquiries', ['through' => 'EnquiryContacts', 'className' => 'Enquiries', 'conditions' => ['role_id' => 3], 'foreignKey' => 'person_id', 'targetForeignKey' => 'enquiry_id', ]);

The self and related links generated for these associations on the http://{{domain}}/api/enquiries/5 URL will contain the same set of results if followed – i.e. without conditions differentiation.

Thus for related URLs, http://{{domain}}/api/enquiries/5/relationships/clients will show the same set of results as for http://{{domain}}/api/enquiries/5/relationships/administratives and the same applies for the self URLs.

[geoidesic]

Perhaps related: https://github.com/FriendsOfCake/crud/issues/651

[geoidesic]

I've found a work-around for this, which is imho rather messy. I'd appreciate insight into whether this is actually how it should be done – maybe this is beyond the scope of crud and crud-json-api although imho it's a fairly obvious and fundamental requirement that conditions of an association should be honoured by default.

The work-around requires much code.

Firstly in the EnquiriesController::beforeFilter:

$this->Crud->on('Crud.beforeFind', function (\Cake\Event\Event $event) {
            $type = $this->request->getParam('type');
            $subject = $event->getSubject();
            $query = $subject->query;
            switch ($type) {
              case 'Clients':
                $query->contain('Clients', function ($q) {
                    return $q->select('id', 'name', 'role_id')->where(['EnquiryContacts.role_id' => 3]);
                });
              break;
            }
        });

Secondly in the PeopleController::beforeFilter (or Index action):

$this->Crud->on('beforePaginate', function (\Cake\Event\Event $event) use ($type) {
            $type = $this->request->getParam('type');
            $subject = $event->getSubject();
            $query = $subject->query;
            switch ($type) {
              case 'Clients':
                $query->matching('ClientEnquiries', function ($q) {
                    return $q->where(['EnquiryContacts.role_id' => 3]);
                });
              break;
            }
        });

These will honour the association conditions, but only by restating the conditions, not being drawn from the association itself, which means it's not DRY.

Pretty clunky :-/

[geoidesic]

Seems like there may be a way of using config instead to achieve something similar: https://crud.readthedocs.io/en/latest/listeners/related-models.html#configuring

Although I haven't managed to get that to work.

[dakota]

Took some thinking, and a bit of digging, but the issue is that your association conditions aren't correctly aliased. This means that the ORM doesn't know what or where those conditions should be applied, and so takes the safe route and doesn't apply them to matching queries. The fix is to simply alias the conditions fields with the name of the junction table (i.e. EnquiryContacts)