Querying by multiple associations (OR) in CakePHP 3.0

I am trying to build a (more or less complex) query with CakePHP 3.0 beta 3. I have the following entities:

User belongsToMany Group Task belongsToMany User (aliased 'Recipients') Task belongsToMany Group

It is a tasks management system. A task can be issued either to specific users, or to named groups of users.

I am now trying to find tasks that belong to the current user OR to a group that the current user is a member of (all tasks relevant to a specific user). I have got the user id and a list of groups that the user is a member of.

I have managed to get it to work using the following code:

$queryOptions = [
    'contain' => [
        'Recipients', 'Groups'
    ]
];

$tasksForUser = $this->Tasks->find('all', $queryOptions)->matching('Recipients', function ($q) use ($user_id) {
    return $q->where(['Recipients.id' => $user_id]);
})->toArray();

$tasksForGroup = $this->Tasks->find('all', $queryOptions)->matching('Groups', function($q) use ($group_ids) {
    return $q->where(['Groups.id IN' => $group_ids]);
})->toArray();

$this->set('tasks', array_merge($tasksForUser, $tasksForGroup));

CakePHP 3.0 claims to have a sophicsticated ORM model, so is there a way to execute this in a single query? It might give me the ability to filter out duplicates on the fly.

Thanks in advance, Dave

Solution

You need the UNION of two queries. This statement is supported by the ORM. MySQL implements it by by default clearing out duplicate results (assumes DISTINCT) so you're good to go.

Cake's documentation has a nice example which should apply to your case like this:

$tasksForUser = $tasks->find()
    ->contain('Recipients', function ($q) use ($user_id) {
        return $q->where(['Recipients.id' => $user_id]);
    })
;

$tasksForGroup = $tasks->find()
    ->contain('Groups', function($q) use ($group_ids) {
        return $q->where(['Groups.id IN' => $group_ids]);
    })
;

$tasksForGroup->union($tasksForUser);

So all you need is to place the code in your model to be called from your controller as a custom find.