Drizzle Team•11mo ago

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 =]

13 Replies

Mykhailo•11mo ago

Hello, @Noronha! Could you please tell what you want to achieve with this query?

NoronhaOP•11mo ago

Hi @solo how are you? 🙂 I have two tables here: Contract and Transaction. Transaction holds financial info like, expiredAt, paidAt, value [...] and maybe a contractId. So When listing contracts, I need to get all the Transaction values that were paid for that contract. That's why I'm trying an subquery.

Mykhailo•11mo ago

@Noronha I am fine, thanks! Correct me if I am wrong, but I don't think that you need a subquery here. If you just want to get all contracts with the totalValue of transactions that are associated with each contract, you can do something like this:

const data = await db
  .select({
    id: contracts.id,
    totalValue: sql<number>`cast(${sum(transactions.value)} as integer)`,
  })
  .from(contracts)
  .leftJoin(transactions, eq(contracts.id, transactions.contractId))
  .groupBy(contracts.id);

const data = await db
  .select({
    id: contracts.id,
    totalValue: sql<number>`cast(${sum(transactions.value)} as integer)`,
  })
  .from(contracts)
  .leftJoin(transactions, eq(contracts.id, transactions.contractId))
  .groupBy(contracts.id);

In this code, I cast totalValue to integer just as an example (if you need to cast you should cast to value data type), because sum() returns a string. Also, I didn't include where clauses.

NoronhaOP•11mo ago

Good morning @solo , hope this message finds you well 🙂 Sorry for taking this long to give you some feedback I think you're right and we're getting close to a solution.

    const data = await db
      // .with(sq)
      .selectDistinct({
        id: contractTable.id,
        identifier: contractTable.identifier,
        contractObject: contractTable.contractObject,
        type: contractTable.type,
        statusId: contractTable.statusId,
        companyId: contractTable.companyId,
        clientId: contractTable.clientId,
        value: contractTable.value,
        osDate: contractTable.osDate,
        signedAt: contractTable.signedAt,
        aditionalValue: contractTable.aditionalValue,
        createdAt: contractTable.createdAt,
        updatedAt: contractTable.updatedAt,
        deletedAt: contractTable.deletedAt,
        createdBy: contractTable.createdBy,
        updatedBy: contractTable.updatedBy,
        deletedBy: contractTable.deletedBy,
        archivedAt: contractTable.archivedAt,
        archivedBy: contractTable.archivedBy,
        totalPaid: sql<number>`${sum(transactionTable.value)}`,
      })
      .from(contractTable)
      .leftJoin(
        transactionTable,
        and(
          eq(transactionTable.contractId, contractTable.id),
          eq(transactionTable.type, 'credit'),
          isNotNull(transactionTable.paidAt),
          isNull(transactionTable.deletedAt),
        ),
      )
      .innerJoin(companyTable, eq(companyTable.id, contractTable.companyId))
      .innerJoin(clientTable, eq(clientTable.id, contractTable.clientId))
      .innerJoin(statusTable, eq(statusTable.id, contractTable.statusId))
      .leftJoin(
        situationToContracts,
        eq(situationToContracts.contractId, contractTable.id),
      )
      // .leftJoin(sq, eq(sq.contractId, contractTable.id))
      .where(and(...whereList))
      .groupBy(contractTable.id)
      .orderBy(addSort(sort))
      .limit(itemsPerPage)
      .offset(itemsPerPage * (page - 1));

    const data = await db
      // .with(sq)
      .selectDistinct({
        id: contractTable.id,
        identifier: contractTable.identifier,
        contractObject: contractTable.contractObject,
        type: contractTable.type,
        statusId: contractTable.statusId,
        companyId: contractTable.companyId,
        clientId: contractTable.clientId,
        value: contractTable.value,
        osDate: contractTable.osDate,
        signedAt: contractTable.signedAt,
        aditionalValue: contractTable.aditionalValue,
        createdAt: contractTable.createdAt,
        updatedAt: contractTable.updatedAt,
        deletedAt: contractTable.deletedAt,
        createdBy: contractTable.createdBy,
        updatedBy: contractTable.updatedBy,
        deletedBy: contractTable.deletedBy,
        archivedAt: contractTable.archivedAt,
        archivedBy: contractTable.archivedBy,
        totalPaid: sql<number>`${sum(transactionTable.value)}`,
      })
      .from(contractTable)
      .leftJoin(
        transactionTable,
        and(
          eq(transactionTable.contractId, contractTable.id),
          eq(transactionTable.type, 'credit'),
          isNotNull(transactionTable.paidAt),
          isNull(transactionTable.deletedAt),
        ),
      )
      .innerJoin(companyTable, eq(companyTable.id, contractTable.companyId))
      .innerJoin(clientTable, eq(clientTable.id, contractTable.clientId))
      .innerJoin(statusTable, eq(statusTable.id, contractTable.statusId))
      .leftJoin(
        situationToContracts,
        eq(situationToContracts.contractId, contractTable.id),
      )
      // .leftJoin(sq, eq(sq.contractId, contractTable.id))
      .where(and(...whereList))
      .groupBy(contractTable.id)
      .orderBy(addSort(sort))
      .limit(itemsPerPage)
      .offset(itemsPerPage * (page - 1));

The problem is the sum value looks like an random number. Some examples: totalPaid: 1.387.332 should be: 231.222 totalPaid: 1035 should be: 345 totalPaid: 3123 This one was correct. Dont understand these results. I got it working like this:

    const data = await db
      .selectDistinct({
        id: contractTable.id,
        identifier: contractTable.identifier,
        contractObject: contractTable.contractObject,
        type: contractTable.type,
        statusId: contractTable.statusId,
        companyId: contractTable.companyId,
        clientId: contractTable.clientId,
        value: contractTable.value,
        osDate: contractTable.osDate,
        signedAt: contractTable.signedAt,
        aditionalValue: contractTable.aditionalValue,
        createdAt: contractTable.createdAt,
        updatedAt: contractTable.updatedAt,
        deletedAt: contractTable.deletedAt,
        createdBy: contractTable.createdBy,
        updatedBy: contractTable.updatedBy,
        deletedBy: contractTable.deletedBy,
        archivedAt: contractTable.archivedAt,
        archivedBy: contractTable.archivedBy,
        totalPaid: sql<number>`(SELECT coalesce(${sum(transactionTable.value)}, 0)
          FROM ${transactionTable}
          WHERE ${transactionTable.contractId} = ${contractTable.id}
          AND ${transactionTable.type} = 'credit'
          AND ${transactionTable.paidAt} is not null
          AND ${transactionTable.deletedAt} is null) as totalPaid`,
      })
      .from(contractTable)
       //....

    const data = await db
      .selectDistinct({
        id: contractTable.id,
        identifier: contractTable.identifier,
        contractObject: contractTable.contractObject,
        type: contractTable.type,
        statusId: contractTable.statusId,
        companyId: contractTable.companyId,
        clientId: contractTable.clientId,
        value: contractTable.value,
        osDate: contractTable.osDate,
        signedAt: contractTable.signedAt,
        aditionalValue: contractTable.aditionalValue,
        createdAt: contractTable.createdAt,
        updatedAt: contractTable.updatedAt,
        deletedAt: contractTable.deletedAt,
        createdBy: contractTable.createdBy,
        updatedBy: contractTable.updatedBy,
        deletedBy: contractTable.deletedBy,
        archivedAt: contractTable.archivedAt,
        archivedBy: contractTable.archivedBy,
        totalPaid: sql<number>`(SELECT coalesce(${sum(transactionTable.value)}, 0)
          FROM ${transactionTable}
          WHERE ${transactionTable.contractId} = ${contractTable.id}
          AND ${transactionTable.type} = 'credit'
          AND ${transactionTable.paidAt} is not null
          AND ${transactionTable.deletedAt} is null) as totalPaid`,
      })
      .from(contractTable)
       //....

But if there's a better way to do without the subquery I think its better, can you help me achive that?

Mykhailo•11mo ago

Hello, @Noronha! Just to clarify, are you aiming to sum up all transaction values for each contract with filters, pagination, etc. However, there seems to be an issue with the totalPaid value not being accurate. Additionally, you've provided three different results - is this due to changes in your filter criteria, or is there another reason?

NoronhaOP•11mo ago

You got it almost 100% right. The different values that I showed you was: - totalPaid coming from the Query - The actual value that it should print. This means that the SUM is not being executed on the correct values, for some reason.

Mykhailo•11mo ago

Ok, thanks totalPaid: 3123 This one was correct. did you change something to get correct value?

NoronhaOP•11mo ago

Nope Just one was correct.

Mykhailo•11mo ago

@Noronha sorry for many questions, do this code works for you as expected?

NoronhaOP•11mo ago

Yes it does and I already shipped in to production. But As I said without subquery would be better I think dont worry man, I'm just so grateful for your attention and time, you're the best.

Mykhailo•11mo ago

super, could you tell me please for what is used coalesce in subquery?

NoronhaOP•11mo ago

I use it because its a left join, so I might have null "sum"s, and in those cases I want the result to be 0 not "left join", I mean maybe a contract doesnt have any payments yet

Mykhailo•11mo ago

I believe using a subquery might be a good approach for this case as well. Considering other options might require a reproduction repository, but I'm not certain it's necessary for you to create one. However, I might be able to assist with the query to make it more readable: contract: contracts, - retrieve all columns from contracts table

const subquery = db
  .select({
    totalPaid: sql<number>`coalesce(${sum(transactions.value)}, 0)`,
  })
  .from(transactions)
  .where(
    and(
      eq(transactions.contractId, contracts.id),
      eq(transactions.type, 'credit'),
      isNotNull(transactions.paidAt),
      isNull(transactions.deletedAt),
    ),
  );

const data = await db
  .selectDistinct({
    contract: contracts,
    totalPaid: sql`${subquery}`,
  })
  .from(contracts)
  .leftJoin(transactions, eq(transactions.contractId, contracts.id))
  .groupBy(contracts.id);

const subquery = db
  .select({
    totalPaid: sql<number>`coalesce(${sum(transactions.value)}, 0)`,
  })
  .from(transactions)
  .where(
    and(
      eq(transactions.contractId, contracts.id),
      eq(transactions.type, 'credit'),
      isNotNull(transactions.paidAt),
      isNull(transactions.deletedAt),
    ),
  );

const data = await db
  .selectDistinct({
    contract: contracts,
    totalPaid: sql`${subquery}`,
  })
  .from(contracts)
  .leftJoin(transactions, eq(transactions.contractId, contracts.id))
  .groupBy(contracts.id);

Gaming

Programming

Subquery not working