DT
Drizzle Teamdeclspecl

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.
D
declspecl•19d 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 🙏
Want results from more Discord servers?
Add your server
More Posts
Circular Reference Error when Using Self-Reference in Table Definition with Drizzle-ORMI encountered an error while defining a PostgreSQL table using Drizzle-ORM. The issue arises when atSQL many statements?Any ideas on how to debug this? ```Task migrations deno run -A src/lib/db/migrations.ts error: UncaQuerying a materialized view: relation does not existI define a materialized view as such: ``` export const usersMv = pgMaterializedView("users_mv").as( default to empty object for sqlite text field with JSON modeHow can I default a `mode: 'json'` text type to an empty object in sqlite? The following in a `sqli