Drizzle TeamDT
Drizzle Team•2y ago
lelabo

Relations with multiple fields and references, or with constraints.

Lets say I have a table food and a table nutrient.
The food table contains an 
id
 and a 
name
.
The nutrient table contains an 
id
, a type and a 
name
.

I want to create a many-to-many relation about the food to their nutrients, so I did a third table: food-nutrient.
The food-nutrient table as a foodId, a nutrientId and for now an 
amount
.

This setup seems to be doable with the many-to-many example, I did it just fine...
I can now query food.foodNutrients and food.foodNutrients[number].nutrient.


NOW, lets say, I want food.macros and food.micros which are the same relations but with a new constraint on nutrient.type.
Is it doable and how?

In MySQL, I would do this with a join (like the following) but I would rather use a relation if possible:
SELECT * FROM food_nutrient
JOIN nutrient ON nutrient.id = food_nutrient.nutrientId
WHERE foodId = 1 AND type = 'macro';


I saw that relation accept arrays in one function config arg for fields and references, I am playing with it right now, but did not figure how to do it yet, I would like to be able to specify something like a where.
Was this page helpful?