Drizzle Team•8mo ago

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]
    })
}));

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.

1 Reply

declspeclOP•8mo ago

The main problem I'm having is that no matter what table I query, I can't do a where filter specific enough to what I want it to do. Doing with is the only way I can navigate these many-to-many relationships but it is not at all the same behavior, so I'm a bit stuck. Any help would be very very appreciated 🙏

Gaming

Programming

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