Drizzle TeamDT
Drizzle Team•2y ago
declspecl

Navigating through multiple many-to-many relationships in a single query?

Hi all! I've been racking my brain unsuccessfuly on how to accomplish my desired behavior with a query using drizzle. I don't even know how to do it in SQL honestly, and I've searched everywhere on the internet but nothing seems to do exactly what I want.

This is a stripped version of my schema just showing the relations, as the rest is pretty simple to deduce. The hierarchy is primarily meal combinations -> many food items -> many traits
export const foodItemsRelations = relations(foodItems, ({ many, one }) => ({
    foodItemsTraits: many(foodItemsTraits),
    mealCombinationFoodItems: many(mealCombinationsFoodItems) // may not ever need to query this, can maybe eventually comment out
}));

export const foodItemsTraitsRelations = relations(foodItemsTraits, ({ one }) => ({
    foodItem: one(foodItems, {
        fields: [foodItemsTraits.foodItemId],
        references: [foodItems.id]
    }),
    trait: one(traits, {
        fields: [foodItemsTraits.traitId],
        references: [trait.id]
    }),
}));

export const mealCombinationsRelations = relations(mealCombinations, ({ many}) => ({
    mealCombinationFoodItems: many(mealCombinationsFoodItems)
}));

export const mealCombinationsFoodItemsRelations = relations(mealCombinationsFoodItems, ({ one }) => ({
    mealCombination: one(mealCombinations, {
        fields: [mealCombinationsFoodItems.mealCombinationId],
        references: [mealCombinations.id]
    }),
    foodItem: one(foodItems, {
        fields: [mealCombinationsFoodItems.foodItemId],
        references: [foodItems.id]
    })
}));
Which is a lot of many to many relationships that I want to navigate in a single query. My goal is pretty much this: "SELECT * FROM mealCombinations WHERE every linked trait from every linked foodItem is in some given list of traits." And every row that does not satisfy that condition is no longer considered.
Was this page helpful?