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

CHChi Hao4/1/2024
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? 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?
AAaroned4/1/2024
@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.
CHChi Hao4/1/2024
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()

Looking for more? Join the community!

Want results from more Discord servers?
Add your server
Recommended Posts
drizzle kit generate schemas for mysql had a little type mismatched problem.Has anyone encountered a similar problem of mismatched types with the error message "MySqlVarCharBuiHow to make a text based timestamp type for sqlite with zod schema inferenceWhen I use the built in integer with mode timestamp, the generated zod schemas correctly declare thaWhat's the best way to query my db?I'm running Supabase Postgres locally with Drizzle. In production, I'd use the connection pooler wHow to query many-many with mysqlI setup a simple example to test if many-many query and ran again into the same issue. ```ts Error:How do I access config in fromDriver and toDriver when defining a custom type using customType?None of the examples do this so it's not clear how/if this is possible?Cannot get user roleI'm using Next.js with next auth and the drizzle adapter. I must be doing something wrong because th`drizzle-kit generate:sqlite` doesn't work`lib/drizzle.ts` ```ts import { text, blob, sqliteTable } from "drizzle-orm/sqlite-core"; export coStudio not enough info to infer relationI'm getting this error: ``` throw new Error( ^ Error: There is not enough information toTurso DB reads (alot)Using turso for a project with drizzle ( I usually use mysql ), and i'm getting way more database reAppending to array OnConflictUpdate()? (Postgres)**full question on stack overflow: **https://stackoverflow.com/questions/78246191/error-appending-toTypeError: Cannot read properties of undefined (reading 'compositePrimaryKeys')I'm getting this error when I want to rename a table (there are no other changes). Old table: ``` ebetter way to check truthy / falsey from selectHey all, currently checking if the length > 0 and <1 to check truthy / falsey is there a better way Relation join conditionsHey - let's say I have the following SQL: ```sql SELECT blocks.id, blocks.type FROM inHow do I use the sql operator with better-sqlite3?Hello. I'm trying to build a custom query with the sql operator in drizzle-orm, and to run it agains