CakePHP 3.x：如何查询仅含指定状态评论的HasMany关联文章

Great question! Your current query gets articles that have at least one Ugly comment, but we need to narrow it down to articles where all comments are Ugly (status 3), excluding any that have even one Good or Bad comment. Here are two efficient, elegant ways to do this with CakePHP's Query Builder:

Approach 1: Using NOT EXISTS Subquery #

This method checks two key conditions: the article has at least one Ugly comment, and it has no comments that aren't Ugly. It’s robust even if your status IDs change later.

// Subquery: Articles with at least one Ugly comment
$hasUglyComment = $this->Articles->Comments->find()
    ->select(['article_id'])
    ->distinct()
    ->where(['comment_status_id' => 3]);

// Subquery: Articles with any non-Ugly comment (Good/Bad)
$hasNonUglyComment = $this->Articles->Comments->find()
    ->select(['article_id'])
    ->where([
        'Comments.article_id = Articles.id',
        'comment_status_id NOT IN' => [3]
    ]);

// Main query: Articles with at least one Ugly, and no non-Ugly comments
$articlesWithOnlyUgly = $this->Articles->find()
    ->where(['EXISTS' => $hasUglyComment])
    ->where(['NOT EXISTS' => $hasNonUglyComment]);

Approach 2: Using GROUP BY + HAVING #

This is a more concise method that leverages grouping to ensure all comments for an article are Ugly. It works because 3 is the highest status ID here—if both the minimum and maximum status are 3, every comment must be 3.

$articlesWithOnlyUgly = $this->Articles->find()
    ->innerJoinWith('Comments') // Ensure we only include articles with at least one comment
    ->group(['Articles.id'])
    ->having([
        'MIN(comment_status_id)' => 3,
        'MAX(comment_status_id)' => 3
    ]);

Efficiency Notes #

Both approaches are efficient if you have indexes on comment_status_id and article_id in your Comments table. The NOT EXISTS method might be slightly faster in large datasets because it can stop checking an article as soon as it finds a non-Ugly comment, while the GROUP BY method processes all comments for each article.

Content来源于stack exchange，提问作者dividedbyzero