Subquery/join in relational query

are the following queries identical?

await db.query.posts.findFirst({
  where: and(
    eq(posts.type, 'blog'),
    inArray(
      posts.userId,
      db
        .select({ id: users.id })
        .from(users)
        .where(and(eq(users.id, userId), isNotNull(users.email)))
    )
  )
});

await db.query.posts.findFirst({
  where: and(
    eq(posts.type, 'blog'),
    inArray(
      posts.userId,
      db
        .select({ id: users.id })
        .from(users)
        .where(and(eq(users.id, userId), isNotNull(users.email)))
    )
  )
});

await db.query.posts.findFirst({
  with: { user: true },
  where: and(
    eq(posts.type, 'blog'),
    isNotNull(users.email)
  )
});

await db.query.posts.findFirst({
  with: { user: true },
  where: and(
    eq(posts.type, 'blog'),
    isNotNull(users.email)
  )
});

2 Replies

jakeleventhalOP•12mo ago

gives an error, but they should be identical i think

jakeleventhalOP•12mo ago

created https://github.com/drizzle-team/drizzle-orm/issues/1781

GitHub

[FEATURE]: Allow relational query builder to work with subqueries/j...

Describe what you want The following should be identical and work: await db.query.posts.findFirst({ where: and( eq(posts.type, 'blog'), inArray( posts.userId, db .select({ id: users.id }) ....

Gaming

Programming

Subquery/join in relational query