Struggling with Complex Query Filters in Drizzle: Need Help with Global and Specific Conditions
Hey everyone,
I have a query in Drizzle where I'm trying to create a view with multiple filters, but I'm stuck trying to figure out how to combine them effectively.
This part of the query works fine:
const data = await adminDB.query.dish.findFirst({
where: and(eq(topping_categories.restaurantID, restaurantId), eq(dish.id, dishId)),
with: {
toppingsDishesConnect: {
with: {
toppingCategory: {
with: {
toppings: true,
},
},
},
},
},
});
But now, I need to extend this to include additional conditions. I want not only the dish.id to match, but also certain global conditions that I want to retrieve, even if the dish.id doesn't match.
Here’s how I’m trying to define the condition:
Short version:
where: and(eq(topping_categories.restaurantID, restaurantId), or(eq(topping_categories.isGlobal, true), eq(dish.id, dishId))),
Long version:
where: or(
and(eq(topping_categories.restaurantID, restaurantId), eq(dish.id, dishId)),
and(eq(topping_categories.restaurantID, restaurantId), eq(topping_categories.isGlobal, true))),
However, I keep getting the following error:
[Error [PostgresError]: column dish.is_global does not exist] {
severity_local: 'ERROR',
severity: 'ERROR',
code: '42703',
position: '2826',
file: 'parse_relation.c',
line: '3651',
routine: 'errorMissingColumn'}
Could anyone explain how the filtering works in this case? I’m having trouble understanding how the different levels of filtering interact and why this error is happening. Specifically, how to combine these conditions properly and avoid this error.
Any help would be really appreciated! Thanks in advance!5 Replies
Schema?
@TOSL
export const toppings = pgTable('topping', {
id: uuid()
.primaryKey()
.default(sqlgen_random_uuid()),
createdAt: timestamp({ mode: 'date' }).defaultNow(),
updatedAt: timestamp({ mode: 'date' }).defaultNow(),
name: text().notNull(),
description: text(),
price: real().notNull(),
calories: integer(),
allergens: text(),
containsMeat: boolean().notNull().default(true),
isAvailable: boolean().notNull().default(false),
toppingCategoryID: integer()
.notNull()
.references(() => topping_categories.id, { onDelete: 'cascade' }),
});
export const topping_categories = pgTable(
'topping_category',
{
id: serial().primaryKey(),
createdAt: timestamp({ mode: 'date' }).defaultNow(),
updatedAt: timestamp({ mode: 'date' }).defaultNow(),
name: text().notNull(),
required: boolean().notNull().default(false),
isGlobal: boolean().notNull().default(false),
restaurantID: text()
.notNull()
.references(() => restaurants.id, { onDelete: 'cascade' }),
},
);
export const toppings_dishes_connect = pgTable('topping_dish', {
id: serial().primaryKey(),
createdAt: timestamp({ mode: 'date' }).defaultNow(),
// updatedAt: timestamp({ mode: 'date' }).defaultNow(),
dishID: uuid()
.notNull()
.references(() => dish.id, { onDelete: 'cascade' }),
toppingCategoryID: integer()
.notNull()
.references(() => topping_categories.id, { onDelete: 'cascade' }),
});
I would be happy if someone could help me understand how filtering works exactly and why what I wrote is wrong.Sorry I din't respond lost this post in a bunch of others
Can you update these code examples to use code blocks.
You just use three
to open and three to close it
Do pretend these quotes are it looks like
"""
your code
"""
You issue doesn't seem to be related to the actually filtering but that isGlobal column doesn't exist. Have you verified that it does?No problem, I don’t fully understand your question. The value of isGlobal is visible in the schema:
topping_category,
{
id: serial().primaryKey(),
createdAt: timestamp({ mode: 'date' }).defaultNow(),
updatedAt: timestamp({ mode: 'date' }).defaultNow(),
name: text().notNull(),
required: boolean().notNull().default(false),
isGlobal: boolean().notNull().default(false),
}
I submitted this as well. Is this what you mean?"
Did you push/migrate your schema?