Conditional Relational Queries

I have these tables Simplified Version

export const ingredientTypeEnum = pgEnum('ingredientType', ['ITEM', 'FOOD']);

export const food = pgTable('food', {
    id: serial('id').primaryKey(),
    name: varchar('name', { length: 256 }).notNull(),
});

export const item = pgTable('item', {
    id: serial('id').primaryKey(),
    name: varchar('name', { length: 256 }).notNull(),
    description: text('description').array(),
});

export const ingredient = pgTable('ingredient', {
    recipe_id: integer('recipe_id').references(() => recipe.id),
    ingredientType: ingredientTypeEnum('ingredientType'),
    quantity: integer('amount').notNull(),
});

export const recipe = pgTable('recipe', {
    id: serial('id').primaryKey(),
    name: varchar('name', { length: 256 }).notNull(),
    instructions: text('description').array(),
});

export const recipeRelations = relations(recipe, ({ many }) => ({
    ingredients: many(ingredient),
}));

export const ingredientTypeEnum = pgEnum('ingredientType', ['ITEM', 'FOOD']);

export const food = pgTable('food', {
    id: serial('id').primaryKey(),
    name: varchar('name', { length: 256 }).notNull(),
});

export const item = pgTable('item', {
    id: serial('id').primaryKey(),
    name: varchar('name', { length: 256 }).notNull(),
    description: text('description').array(),
});

export const ingredient = pgTable('ingredient', {
    recipe_id: integer('recipe_id').references(() => recipe.id),
    ingredientType: ingredientTypeEnum('ingredientType'),
    quantity: integer('amount').notNull(),
});

export const recipe = pgTable('recipe', {
    id: serial('id').primaryKey(),
    name: varchar('name', { length: 256 }).notNull(),
    instructions: text('description').array(),
});

export const recipeRelations = relations(recipe, ({ many }) => ({
    ingredients: many(ingredient),
}));

Is it possible to setup the table relationships so that depending on the ingredient type it utilizes the corresponding tables? For example, with an ingredient type of ITEM it would use the item table instead of the food table.

3 Replies

Luxaritas•17mo ago

I believe this is an open feature request: https://github.com/drizzle-team/drizzle-orm/issues/1051 https://github.com/drizzle-team/drizzle-orm/issues/207

GitHub

[FEATURE]: Support Polymorphic Association · Issue #1051 · drizzle-...

Describe what you want I'm looking for a Typesafe ORM that support for polymorphic associations. To give a concrete example: I have a Comment model. I need this model to be associated with both...

GitHub

Add UNION support · Issue #207 · drizzle-team/drizzle-orm

T2ThatGuyOP•17mo ago

Interesting, in that case for now I will just use the old querying method until there is a solution for it apuThumbsUp

Luxaritas•17mo ago

One interesting way to do this differently that I just saw is by introducing an intermediate table. For simplicity I’ll use the example of comments on table a or table b. You can create a “comment set” table which rows on a and b point to, and then comments point to the comment set I’m surprised I haven’t seen that recommended more honestly It’s a similar mechanism as in a many:many relationship I guess with a many:many relationship, you'd need an extra table since a tagset would be correlated to one row

Gaming

Programming

Conditional Relational Queries