Drizzle Team•9mo ago

How do I reuse the results from the where clause within the findMany?

I have a vendor ID and want to retrieve all restaurant IDs and menu IDs associated with that vendor. Can I use the same ID I passed in the where clause for menus as the value for the restaurant ID received in the first where clause?

3 Replies

Chi HaoOP•9mo ago

my schema

export const vendors = pgTable("Vendors", {
  id: serial("ID").notNull().primaryKey(),
  user_id: text("User_ID")
    .notNull()
    .references(() => users.id, { onDelete: "cascade" }),
  is_approved: boolean("Is_Approved").default(false),
  subscription_type: SubscriptionEnum("Subscription_Type")
    .default("Free Trial")
    .notNull(),
});

export const restaurants = pgTable("Restaurants", {
  id: serial("ID").notNull().primaryKey(),
  vendor_id: integer("Vendor_ID")
    .notNull()
    .references(() => vendors.id, { onDelete: "cascade" }),
  restaurant_name: text("Restaurant_Name").notNull(),
  slug: text("Restaurant_Name_Slug").notNull().unique(),
  order_phases: jsonb("Order_Phases").notNull(),
  delivery_location: jsonb("Delivery_Location").notNull(),
});

export const menus = pgTable("Menus", {
  id: serial("ID").notNull().primaryKey(),
  restaurant_id: integer("Restaurant_ID")
    .notNull()
    .references(() => restaurants.id, { onDelete: "cascade" }),
  name: text("Name").notNull(),
  description: text("Description"),
  price: decimal("Price").notNull(),
  stock: integer("Stock").notNull(),
  customizations: jsonb("Customizations").notNull(),
  order_phases_type: text("Order_Phases_Type").array().notNull(),
});

export const vendors = pgTable("Vendors", {
  id: serial("ID").notNull().primaryKey(),
  user_id: text("User_ID")
    .notNull()
    .references(() => users.id, { onDelete: "cascade" }),
  is_approved: boolean("Is_Approved").default(false),
  subscription_type: SubscriptionEnum("Subscription_Type")
    .default("Free Trial")
    .notNull(),
});

export const restaurants = pgTable("Restaurants", {
  id: serial("ID").notNull().primaryKey(),
  vendor_id: integer("Vendor_ID")
    .notNull()
    .references(() => vendors.id, { onDelete: "cascade" }),
  restaurant_name: text("Restaurant_Name").notNull(),
  slug: text("Restaurant_Name_Slug").notNull().unique(),
  order_phases: jsonb("Order_Phases").notNull(),
  delivery_location: jsonb("Delivery_Location").notNull(),
});

export const menus = pgTable("Menus", {
  id: serial("ID").notNull().primaryKey(),
  restaurant_id: integer("Restaurant_ID")
    .notNull()
    .references(() => restaurants.id, { onDelete: "cascade" }),
  name: text("Name").notNull(),
  description: text("Description"),
  price: decimal("Price").notNull(),
  stock: integer("Stock").notNull(),
  customizations: jsonb("Customizations").notNull(),
  order_phases_type: text("Order_Phases_Type").array().notNull(),
});

the relations

export const vendorsRelations = relations(vendors, ({ one, many }) => ({
  // a vendor can have multiple restaurants
  restaurants: many(restaurants),
}));

export const restaurantsRelations = relations(restaurants, ({ one, many }) => ({
  // a restaurant is owned by a vendor
  vendor: one(vendors, {
    fields: [restaurants.vendor_id],
    references: [vendors.id],
  }),

  // a restaurant can have multiple menu items
  menus: many(menus),
}));

export const menusRelations = relations(menus, ({ one, many }) => ({
  // a menu item is given by a restaurant
  restaurant: one(restaurants, {
    fields: [menus.restaurant_id],
    references: [restaurants.id],
  }),
}));

export const vendorsRelations = relations(vendors, ({ one, many }) => ({
  // a vendor can have multiple restaurants
  restaurants: many(restaurants),
}));

export const restaurantsRelations = relations(restaurants, ({ one, many }) => ({
  // a restaurant is owned by a vendor
  vendor: one(vendors, {
    fields: [restaurants.vendor_id],
    references: [vendors.id],
  }),

  // a restaurant can have multiple menu items
  menus: many(menus),
}));

export const menusRelations = relations(menus, ({ one, many }) => ({
  // a menu item is given by a restaurant
  restaurant: one(restaurants, {
    fields: [menus.restaurant_id],
    references: [restaurants.id],
  }),
}));

The query that might not be working?

const result = await db.query.restaurants.findMany({
  where: eq(restaurants.vendor_id, vendor_id),
  columns: {
    id: true,
  },
  with: {
    menus: {
      where: eq(menus.restaurant_id, id), // how do i make sure this id matches the restaurant id of that vendor?
    },
  },
});

const result = await db.query.restaurants.findMany({
  where: eq(restaurants.vendor_id, vendor_id),
  columns: {
    id: true,
  },
  with: {
    menus: {
      where: eq(menus.restaurant_id, id), // how do i make sure this id matches the restaurant id of that vendor?
    },
  },
});

I wonder if I should use join instead? but I thought findMany is similar to join?

Aaroned•9mo ago

@Chi Hao when you use drizzle relational queries, there is no need to add the where clause for the nested relations, these are automatically applied in the background (https://orm.drizzle.team/docs/rqb#include-relations)

Drizzle ORM - Query

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

Chi HaoOP•9mo ago

hi @Aaroned , I wonder why I'm getting this error TypeError: Cannot read properties of undefined (reading 'referencedTable')? this is the code

const result = await db.query.restaurants.findMany({
  with: {
    menus: true,
  },
});

const result = await db.query.restaurants.findMany({
  with: {
    menus: true,
  },
});

ok fixed, i forgot to export the relations in drizzle()

Gaming

Programming

How do I reuse the results from the where clause within the findMany?