With Query mode, is possible filter by a related table?

Is possible using db.query to filter (where) using a related table? Example:

const users = await db.query.posts.findMany({
    where: (users, { eq }) => eq(posts.user.id, 1), // related user with post
})

const users = await db.query.posts.findMany({
    where: (users, { eq }) => eq(posts.user.id, 1), // related user with post
})

I know we can use with, but with will return the result and we can filter the with, I wanna filter the actual query using the related table. We can do using the select

await db.select().from(posts).rightJoin(users, eq(users.id, posts.userId)).where(eq(posts.userId, users.id)

await db.select().from(posts).rightJoin(users, eq(users.id, posts.userId)).where(eq(posts.userId, users.id)

2 Replies

SKUZZIE•2mo ago

Not currently, you can follow the discussion around this here: https://github.com/drizzle-team/drizzle-orm/discussions/1152 Basically, wait for the V2 release or just stick to manually building the queries with aggregations.

GitHub

Add nested filters for relational queries · drizzle-team drizzle-or...

I would like to create queries that filter by single values from a nested partial fields select. Imagine we have a users table with a one-to-one relation to a profile. The profiles table has a like...

Angelelz•2mo ago

There are ways. Take a look at this reply https://discord.com/channels/1043890932593987624/1303402726897750059/1303787075325136917

Gaming

Programming

With Query mode, is possible filter by a related table?