DT
Drizzle Team•17mo ago
Paul

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

Did you find this page helpful?