jsee
jsee
PostsComments
DTDrizzle Team
Created by jsee on 2/4/2024 in #help
Subqueries in relational queries
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.
5 replies
DTDrizzle Team
Created by jsee on 2/4/2024 in #help
Subqueries in relational queries
Ah, I think this may be what I'm looking for https://discord.com/channels/1043890932593987624/1190608171027398738/1190660643594981507
5 replies