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?
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?
