How to add where clause for one to one relations in drizzle query?
I have these two schemas:
export const users = mysqlTable("User", {
id: text("id").primaryKey(),
name: text("name"),
email: text("email"),
emailVerified: datetime("emailVerified", { mode: "date", fsp: 3 }),
image: text("image"),
role: text("role").default("artist").notNull(),,
});
export const payments = mysqlTable("Payment", {
userId: text("userId").notNull(),
customerId: text("customerId").notNull(),
subscriptionId: text("subscriptionId"),
planName: text("planName").notNull(),
subscriptionStatus: stripeSubscriptionStatusEnum("subscriptionStatus"),
subscriptionCreated: datetime("subscriptionCreated", {
mode: "date",
fsp: 3,
}),
});
With relation:
export const paymentsRelations = relations(payments, (helpers) => ({
user: helpers.one(users, {
relationName: "PaymentToUser",
fields: [payments.userId],
references: [users.id],
}),
}));
export const usersRelations = relations(users, (helpers) => ({
Payment: helpers.one(payments, {
relationName: "PaymentToUser",
fields: [users.id],
references: [payments.userId],
}),
}));
I want to retrieve all the users with planName 'Plan A', but having difficulties in forming a drizzle relational query for this.
The one I am using is:
db.users.query.findMany({
with: {
Payment: true
},
where: eq(Payment.planName, 'Plan A')
})
This is giving me 'Unknown column Payment.planName' error and rightly so because in resulting SQL, there is no reference to Payment table on its own. My question is how can I achieve this using queries? I know I can use drizzle query builders with joins for this but I want to do it in relations query as query builders does not give me output in desired format.
In other words, how can I filter parent table based on a condition given to a related table which are related with one-to-one relation?
5 Replies
One solution I found is adding this in WHERE clause : sql
id IN (SELECT userId from Payment WHERE planName=${plan}).
But I would appreciate a cleaner solution@Se7enVision try querying from the payment instead
db.payments.query.findMany({
with: {
user: true
},
where: eq(Payment.planName, 'Plan A')
})
Hey @Aaroned Thanks for the response. This would work fine but I have several other relations which I am using with users table to get my data. I only shared the Payment one to shorten the question.
I have a datatable in which I have to render users along with user related info present in other tables, so I can only do db.users.query in order to accomodate for them. This is how my complete query looks like:
const users = await ctx.db.query.users.findMany({
with: {
albums: {
columns: {},
with: {
tracks: true,
MusicVideo: true,
}
},
......bunch of relations
Payment: {
columns: {
planName: true
}
}
},
where: !operator || operator === "and"
? and(
...where,
filters?.planName ? eq(payment.planName, filters.planName) : undefined,
)
: or(
...where,
filters?.planName ? eq(payment.planName, filters.planName) : undefined
),
limit: input.limit ?? 10,
offset: input.offset ?? 0,
})
The line filters?.planName ? eq(payment.planName, filters.planName) : undefined obviously does not work and gives the error 'user.planName is unknown' which it is in this context. So I have to use above method for it.
@Se7enVision if that’s the case, you will need to use the exists clause with a subquery.
Something like
@Aaroned Thanks I'll look into it.