Drizzle Team•16mo ago

Relational Queries: How to query based on the value of a joined table?

I have a table posts, where each post has a uid. I use relational queries to perform a join of the user into the post. Now I want to write a where(..) clause which checks whether the user.public field is set to true. However, the where clause does not allow accessing values of the user but only that of posts. How can I accomplish this?

const res = await db.query.postsTable.findMany({
      with: {
        user: true,
      },
      where: (posts, { eq, and }) =>
        and(
          eq(posts.done, true), //<- This works
          eq(posts.user.public, true) // <- This not
        ),
    });

const res = await db.query.postsTable.findMany({
      with: {
        user: true,
      },
      where: (posts, { eq, and }) =>
        and(
          eq(posts.done, true), //<- This works
          eq(posts.user.public, true) // <- This not
        ),
    });

4 Replies

Angelelz•16mo ago

You can't use a relation in the eq function like that. This might be what you're looking for:

const res = await db.query.postsTable.findMany({
      with: {
        user: {
          where: (user, {eq}) => eq(user.public, true)
        },
      },
      where: (posts, { eq, and }) =>
        eq(posts.done, true)
    });

const res = await db.query.postsTable.findMany({
      with: {
        user: {
          where: (user, {eq}) => eq(user.public, true)
        },
      },
      where: (posts, { eq, and }) =>
        eq(posts.done, true)
    });

But if you are only looking for the posts where the user is public, I would invert it; get the public users, and then pull all the posts from those users that are done:

const res = await db.query.users.findMany({
      with: {
        posts: {
          where: (post, {eq}) => eq(post.done, true)
        },
      },
      where: where: (user, {eq}) => eq(user.public, true)
    });

const res = await db.query.users.findMany({
      with: {
        posts: {
          where: (post, {eq}) => eq(post.done, true)
        },
      },
      where: where: (user, {eq}) => eq(user.public, true)
    });

If you think about how you would do this using only Sql, you would probably use an inner join

Angelelz•16mo ago

This should only give you the posts that are done with users that are public.

const res = await db.select()
  .from(posts)
  .where(eq(post.done, true))
  .innerJoin(users, eq(users.public, true))

const res = await db.select()
  .from(posts)
  .where(eq(post.done, true))
  .innerJoin(users, eq(users.public, true))

https://orm.drizzle.team/docs/joins#inner-join

Joins [SQL] – DrizzleORM

Drizzle ORM | %s

elaranthedreamerOP•16mo ago

Thank you so much this helps a lot @angelelz

Angelelz•16mo ago

No problem

Gaming

Programming

Relational Queries: How to query based on the value of a joined table?