How to find entities with common parent using matching() in CakePHP?

Question

I need to find all articles having the same author as a given list of articles

Here is my custom finder method:

public function findSimilar(Query $query, array $options)
    {
        if (empty($options['idList'])) {
            throw new Exception('idList is not populated');
        }
        // We are given a list of article IDs
        $idList = $options['idList'];

        return $query->matching('Authors', function ($q) use ($idList) {
            return $q->matching('Articles', function ($q2) use ($idList) {
                return $q2->where(['Articles.id IN' => $idList]);
            });
        });
    }

Unfortunately I am getting the following error message: PDOException: SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 'Articles' What am I doing wrong?

Answer 1

There are quite a few limitations with nested matching, and this may be one of them. I can't tell whether it maybe is a bug, so you may want to check the issues on GitHub and eventually file a new one for clarification.

Quote from the docs:

[...] Dotted matching paths should be used over nested matching() calls [...]

Cookbook > Database Access & ORM > Retrieving Data & Results Sets > Filtering by Associated Data

In either case, using dot notation instead of nesting should fix the problem, ie

return $query->matching('Authors.Articles', function ($q) use ($idList) {
    return $q->where(['Articles.id IN' => $idList]);
});

And if you'd wanted to also match on Authors, you could stack the matchers, like

return $query
    ->matching('Authors', function ($q) {
        return $q->where(['Authors.foo' => 'bar']);
    })
    ->matching('Authors.Articles', function ($q) use ($idList) {
        return $q->where(['Articles.id IN' => $idList]);
    });

Answer 2

If it is a HasMany relation we can do this easily with:

return $query->where([
   'Articles.author_id IN' => $this->find()
        ->select(['Articles.author_id'])
        ->where(['Articles.id IN' => $idList])
]);

// Thanks to jose_zap on the CakePHP IRC channel