Noronha
Noronha
PostsComments
DTDrizzle Team
Created by Noronha on 2/16/2024 in #help
Subquery not working
not "left join", I mean maybe a contract doesnt have any payments yet
21 replies
DTDrizzle Team
Created by Noronha on 2/16/2024 in #help
Subquery not working
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
21 replies
DTDrizzle Team
Created by Noronha on 2/16/2024 in #help
Subquery not working
dont worry man, I'm just so grateful for your attention and time, you're the best.
21 replies
DTDrizzle Team
Created by Noronha on 2/16/2024 in #help
Subquery not working
As I said without subquery would be better I think
21 replies
DTDrizzle Team
Created by Noronha on 2/16/2024 in #help
Subquery not working
Yes it does and I already shipped in to production. But
21 replies
DTDrizzle Team
Created by Noronha on 2/16/2024 in #help
Subquery not working
Nope Just one was correct.
21 replies
DTDrizzle Team
Created by Noronha on 2/16/2024 in #help
Subquery not working
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.
21 replies
DTDrizzle Team
Created by Noronha on 2/16/2024 in #help
Subquery not working
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?
21 replies
DTDrizzle Team
Created by Noronha on 2/16/2024 in #help
Subquery not working
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.
21 replies
DTDrizzle Team
Created by Noronha on 2/16/2024 in #help
Subquery not working
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));
21 replies
DTDrizzle Team
Created by Noronha on 2/16/2024 in #help
Subquery not working
Sorry for taking this long to give you some feedback
21 replies
DTDrizzle Team
Created by Noronha on 2/16/2024 in #help
Subquery not working
Good morning @solo , hope this message finds you well 🙂
21 replies
DTDrizzle Team
Created by Noronha on 2/16/2024 in #help
Subquery not working
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.
21 replies