Drizzle Team•8mo ago

Subqueries in relational queries

I'd like to use the query API but combine the where with a subquery. Meaning get all posts where the author's name is "John". I understand I can use the select syntax, but would prefer the ORM approach if possible:

await db.query.posts.findMany({
  // assuming `posts` has an ID field:
  where: (fields, ops) => ops.eq(fields.id, **SOME SORT OF SUBQUERY HERE**)
  with: {
    author: true,
  },
});

await db.query.posts.findMany({
  // assuming `posts` has an ID field:
  where: (fields, ops) => ops.eq(fields.id, **SOME SORT OF SUBQUERY HERE**)
  with: {
    author: true,
  },
});

I haven't seen any mention of it but seems doable with a subquery, any suggestions?

2 Replies

jsee•8mo ago

Ah, I think this may be what I'm looking for https://discord.com/channels/1043890932593987624/1190608171027398738/1190660643594981507 For posterity, I had tried something very similar to this, but didn't understand the syntax:

// this is incorrect
const sq = this.db
  .select({ id: schema.authors.id })
  .from(schema.authors)
  .where(eq(schema.authors.name, "Some Name"))
  .as("sq")

await db.query.posts.findMany({
  where: (fields, ops) => ops.eq(fields.id, sq.id)
  with: {
    author: true,
  },
});

// this is incorrect
const sq = this.db
  .select({ id: schema.authors.id })
  .from(schema.authors)
  .where(eq(schema.authors.name, "Some Name"))
  .as("sq")

await db.query.posts.findMany({
  where: (fields, ops) => ops.eq(fields.id, sq.id)
  with: {
    author: true,
  },
});

But the correct syntax is (note the lack of .as("sq") and the inArray operator:

const sq = this.db
  .select({ id: schema.authors.id })
  .from(schema.authors)
  .where(eq(schema.authors.name, "Some Name"));

await db.query.posts.findMany({
  where: (fields, ops) => ops.inArray(fields.id, sq)
  with: {
    author: true,
  },
});

const sq = this.db
  .select({ id: schema.authors.id })
  .from(schema.authors)
  .where(eq(schema.authors.name, "Some Name"));

await db.query.posts.findMany({
  where: (fields, ops) => ops.inArray(fields.id, sq)
  with: {
    author: true,
  },
});

Tried a few things around this but never got the right combination. Would be curious if anyone can explain a bit more about the difference there.

Angelelz•8mo ago

Yes, when you use the as method, you are converting the subquery into an alias, to be used for example in the from clause When you need to inline it as you are, you can't alias it, you need to provide the actual subquery

Gaming

Programming

Subqueries in relational queries