Ad

Fetch Records That Has All Specific Relationships

I have the next schema:

nutrients
|id|label        |type
|1 |Avocado      |fats
|2 |Cooked shrimp|proteins
|3 |Raw oatmeal  |carbohydrates
|4 |Chocolate    |fats

recipes
|id|label
|1 |Something with Avocado
|2 |Something w Avocado, chocolate and raw oatmeal

nutrient_recipe
|nutrient_id|recipe_id
|1          |1
|1          |2
|3          |2
|4          |2

And I want to fetch only the recipe that has exactly certain nutrient types. For example:

  1. I want a recipe that has only fats.

It should return Something with Avocado (id: 1)


  1. I want a recipe that has only fats and carbohydrates.

It should return Something w Avocado, chocolate and raw oatmeal (id: 2)

How can I achieve this?

I'm working with Laravel it would be great if you take advantage of Eloquent.

Edit:

Thanks to @GordonLinoff

I could achieve the next code:

App\Models\Recipe::whereHas('nutrients', function ($query) {
    $query->havingRaw("sum(case when nutrients.type = 'fats' then 1 else 0 end) > 0")
        ->havingRaw("sum(case when nutrients.type not in ('fats') then 1 else 0 end) = 0")
        ->groupBy('recipes.id')
        ->select('recipes.id');
})

obviusly this code seams to belong to a filtered search, so I hope this may help someone.

Ad

Answer

You can use group by and having. Here is an example for "fats":

select nr.recipe_id
from nutrient_recipe nr join
     nutrients n
     on nr.nutrient_id = n.id
group by nr.recipe_id
having sum(case when n.type = 'fats' then 1 else 0 end) > 0 and
       sum(case when n.type not in ('fats') then 1 else 0 end) = 0 ;

The first having condition says there is at least one nutrient that is "fats". The second says there are none.

You can easily extend this with more having clauses:

select nr.recipe_id
from nutrient_recipe nr join
     nutrients n
     on nr.nutrient_id = n.id
group by nr.recipe_id
having sum(case when n.type = 'fats' then 1 else 0 end) > 0 and
       sum(case when n.type = 'carbohydrates' then 1 else 0 end) > 0 and
       sum(case when n.type not in ('fats', 'carbohydrates') then 1 else 0 end) = 0 ;
Ad
source: stackoverflow.com
Ad