Noronha
Noronha
PostsComments
DTDrizzle Team
Created by Noronha on 2/16/2024 in #help
Subquery not working
Guys, do you know how to create a subquery to bring extra data on the SQL? I've look on the docs, but it doesnt seem to work (I only get null values). This is the subQuery: 
    const sq = db.$with('sq').as(
      db
        .select({
          totalValue: sum(transactionTable.value).as('totalValue'),
          contractId: transactionTable.contractId,
        })
        .from(transactionTable)
        .where(
            // ...
        ),
    );
    const sq = db.$with('sq').as(
      db
        .select({
          totalValue: sum(transactionTable.value).as('totalValue'),
          contractId: transactionTable.contractId,
        })
        .from(transactionTable)
        .where(
            // ...
        ),
    );
And in the main query: 
    const data = await db
      .with(sq)
      .selectDistinct({
        // ...
        totalPaid: sq.totalValue,
      })
      .from(contractTable)
       // Here...
      .leftJoin(sq, eq(sq.contractId, contractTable.id))
      .where(and(...whereList))
    const data = await db
      .with(sq)
      .selectDistinct({
        // ...
        totalPaid: sq.totalValue,
      })
      .from(contractTable)
       // Here...
      .leftJoin(sq, eq(sq.contractId, contractTable.id))
      .where(and(...whereList))
Thank you in advance =]
21 replies