Querying table based on a field of a related table
Hey everyone, I have no idea what im doing wrong, but the goal here is to get every subscription which its related plan, has the spaceId field according to an input. Here's what I got after reading the docs:
const subscriptions = await ctx.db.query.subscriptions.findMany({
with: {
plan: {
where: (plan, { eq }) => eq(plans.spaceId, input.spaceId),
},
},
});
Typescript is complaining on the "where" , saying I can't use it here. I saw a very similar example on the docs:
await db.query.posts.findMany({
where: (posts, { eq }) => (eq(posts.id, 1)),
with: {
comments: {
where: (comments, { lt }) => lt(comments.createdAt, new Date()),
},
},
});
The only difference is the type of relation, where in my case subscription has a relation with one plan, and the example on the docs the posts have a relation of many comments. Should I be querying for plans and filtering the subscriptions from there? Doesn't feel right when in fact what I want is the subscriptions.
1 Reply
This query doesn't seem correct. If you include a where on a
one relation, the return should be null. You should only have one plan for that subscription. And if you filter that plan then you might get a null
If you want to filter the subscription based on the value of the plan, then one way to do it is how you described it
Another way is with a subquery