cakePHP 3.0 nested associations

Question

I have ArticleLists which hasMany ArticleListPosts, which in turn belongsTo Contents.

I want to retrieve only the entries related to my below conditions, i.e only ArticleLists, that have ArticleListPosts, that their Contents match the %search% criteria. This works fine, except that it retrieves all ArticleListPosts, even if even one has Contents that match the %search% criteria. But I want only ArticleListPosts that have relevant Contents, not all.

My query is below:

$query = $this->ArticleLists
    ->find()
    ->contain([
        'ArticleListPosts',
        'ArticleListPosts.Contents'
    ])
    ->innerJoinWith('ArticleListPosts')
    ->innerJoinWith('ArticleListPosts.Contents')
    ->where([
        'ArticleLists.site' => $site,
        'ArticleLists.culture' => $culture,
        'ArticleLists.language' => $language,
        'OR' => [
            ['Contents.slug LIKE' => "%$search%"],
            ['Contents.title LIKE' => "%$search%"]
        ],
    ])
    ->order([
        "ArticleLists.slug" => "ASC",
        "Contents.slug" => "ASC"
    ]);

Sounds like **https://stackoverflow.com/questions/26799094/how-to-filter-by-conditions-for-associated-models/26800203#26800203**, containing and joining are different things unless the association can use the join strategy (which isn't the case for `hasMany`). If you want to filter what is being contained, then you need to apply the filter on the containments too. — ndm, Jan 10 '22 at 09:58
thank you @ndm for the hints. You were right, with hasMany the ORM was generating 2 queries, so it was not limiting the results. I solved using matching, and applying filter on that as well(as you suggested). — Aris, Jan 10 '22 at 12:34

score 0 · Answer 1 · answered Jan 10 '22 at 12:36

With hasMany the ORM was generating 2 queries, so it was not limiting the results. I solved it using matching, and applying filter on that as well, separately for each association, as @ndm suggested. This will generate a single query, with 2 inner joins.

$query = $this->ArticleLists
                ->find()
                ->matching('ArticleListPosts', function ($q) use ($site, $culture, $language) {
                    return $q->where([
                        'ArticleLists.site' => $site,
                    'ArticleLists.culture' => $culture,
                    'ArticleLists.language' => $language,
                    ]);
                })
                ->matching('ArticleListPosts.Contents', function ($q) use ($search) {
                    return $q->where([
                        'OR' => [
                            ['Contents.slug LIKE' => "%$search%"],
                            ['Contents.title LIKE' => "%$search%"]
                        ]
                    ]);
                })

                ->order(["ArticleLists.slug" => "ASC", "Contents.slug" => "ASC"]);

cakePHP 3.0 nested associations

1 Answers1