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"
    ]);

Answer 1

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"]);