How to get all parents that children all match attribute?-CodePudding

I have this parent table

id
1
2

and this children table

id	parent_id	status
1	1	3
2	1	2
2	2	3
2	2	3

I want to query the tables(using laravel elloquent) and get the parents that have all their children match status 3. For example here id = 2 in parent table matches this criteria

CodePudding user response：

I'm unable to test it, but this may work:

Parent::whereDoesntHave('children', function($query) {
    $query->where('status', '!=', 3);
})->whereHas('children')

The resulting SQL looks like this:

select 
  * 
from 
  `parents` 
where 
  not exists (
    select 
      * 
    from 
      `children` 
    where 
      `parents`.`id` = `children`.`parent_id` 
      and `status` != 3
  ) 
  and exists (
    select 
      * 
    from 
      `children` 
    where 
      `parents`.`id` = `children`.`parent_id`
  )

It selects only parents that have children and excludes the ones that has children with a status not equal to 3

CodePudding user response：

Aggregation is one option:

SELECT p.id
FROM parent p
INNER JOIN children c
    ON c.parent_id = p.id
GROUP BY p.id
HAVING MIN(c.status) = MAX(c.status) AND MIN(c.status) = 3;