Drizzle Team•5mo ago

How can I append a subquery as an array in the main query select?

How can I append a subquery as an array in the main query select? I'm currently using the alternative which is to use relational queries, but when using the db.select() syntax, is it possible to append subqueries or any leftJoins for that matter as an array in the final select?

const subquery = db
  .select({
    id: myTable1.id,
  })
  .from(myTable1)
  .as("subquery");

const query = db
  .select({
    id: myTable2.id,
    table1Ids: sql`array(${subquery})`.as("table1Ids")
    // Error. Syntax error at or near "subquery" 
  })
  .from(myTable2)
  .leftJoin(
    subquery,
    eq(subquery.id, myTable2.table1Id)
  );

const subquery = db
  .select({
    id: myTable1.id,
  })
  .from(myTable1)
  .as("subquery");

const query = db
  .select({
    id: myTable2.id,
    table1Ids: sql`array(${subquery})`.as("table1Ids")
    // Error. Syntax error at or near "subquery" 
  })
  .from(myTable2)
  .leftJoin(
    subquery,
    eq(subquery.id, myTable2.table1Id)
  );

0 Replies

No replies yetBe the first to reply to this messageJoin

Gaming

Programming

How can I append a subquery as an array in the main query select?