Drizzle Team•12mo ago

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';

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.

5 Replies

Angelelz•12mo ago

This is very similar to polymorphic associations but not quite Do you mind sharing the schema as it is right now?

lelaboOP•12mo ago

My schema look like this:

export const food = mysqlTable("food", {
  id: serial("id").primaryKey(),
  name: varchar("name", { length: 256 }),
});

export const foodRelations = relations(food, ({ one, many }) => ({
  nutrients: many(foodNutrients),
}));

export const nutrients = mysqlTable("nutrient", {
  id: serial("id").primaryKey(),
  type: mysqlEnum("type", ["nutrition", "macro", "micro", "fodmap"]),
  name: varchar("name", { length: 256 }),
});

export const nutrientRelations = relations(nutrients, ({ many }) => ({
  foods: many(foodNutrients),
}));

export const foodNutrients = mysqlTable(
  "food_nutrient",
  {
    foodId: bigint("foodId", { mode: "number" }).notNull(),
    nutrientId: bigint("nutrientId", { mode: "number" }).notNull(),
    value: bigint("value", { mode: "number" }).notNull(),
  },
  (table) => ({
    pk: primaryKey(table.foodId, table.nutrientId),
  }),
);

export const foddNutrientRelations = relations(foodNutrients, ({ one }) => ({
  food: one(food, {
    fields: [foodNutrients.foodId],
    references: [food.id],
  }),
  nutrient: one(nutrients, {
    fields: [foodNutrients.nutrientId],
    references: [nutrients.id],
  }),
}));

export const food = mysqlTable("food", {
  id: serial("id").primaryKey(),
  name: varchar("name", { length: 256 }),
});

export const foodRelations = relations(food, ({ one, many }) => ({
  nutrients: many(foodNutrients),
}));

export const nutrients = mysqlTable("nutrient", {
  id: serial("id").primaryKey(),
  type: mysqlEnum("type", ["nutrition", "macro", "micro", "fodmap"]),
  name: varchar("name", { length: 256 }),
});

export const nutrientRelations = relations(nutrients, ({ many }) => ({
  foods: many(foodNutrients),
}));

export const foodNutrients = mysqlTable(
  "food_nutrient",
  {
    foodId: bigint("foodId", { mode: "number" }).notNull(),
    nutrientId: bigint("nutrientId", { mode: "number" }).notNull(),
    value: bigint("value", { mode: "number" }).notNull(),
  },
  (table) => ({
    pk: primaryKey(table.foodId, table.nutrientId),
  }),
);

export const foddNutrientRelations = relations(foodNutrients, ({ one }) => ({
  food: one(food, {
    fields: [foodNutrients.foodId],
    references: [food.id],
  }),
  nutrient: one(nutrients, {
    fields: [foodNutrients.nutrientId],
    references: [nutrients.id],
  }),
}));

I used polymorphic before but I did not think of this as a polymorphic relation, as I am not filtering on the same table. For me it was more like "relation meet filtering"

Angelelz•12mo ago

I actually have an open PR for a feature like this. It can be applied for Polymorphic assoc but it doesn't have to You can add your like and comment to see if the team picks it up

Angelelz•12mo ago

https://github.com/drizzle-team/drizzle-orm/pull/1451

GitHub

Feat: optional where clause in relations by Angelelz · Pull Request...

This PR will close #1437, and will close #1051, will address part of #674 and part of #821. Upon successful merging of this PR, a new where option will be added to the relation configuration. The m...

lelaboOP•12mo ago

This would address indeed some of the cases. Nice to see I am not just asking some dumb questions, but asking about things that actually are looked upon. In the mean time, I think joins will do the jobs...

Gaming

Programming

Relations with multiple fields and references, or with constraints.