DT
Drizzle Team•9mo ago
Noronha

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
Mykhailo•9mo ago
Hello, @Noronha! Could you please tell what you want to achieve with this query?
Noronha
NoronhaOP•9mo 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
Mykhailo•9mo 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.
Noronha
NoronhaOP•9mo 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
Mykhailo•9mo 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?
Noronha
NoronhaOP•9mo 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
Mykhailo•9mo ago
Ok, thanks totalPaid: 3123 This one was correct. did you change something to get correct value?
Noronha
NoronhaOP•9mo ago
Nope Just one was correct.
Mykhailo
Mykhailo•9mo ago
@Noronha sorry for many questions, do this code works for you as expected?
Noronha
NoronhaOP•9mo 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
Mykhailo•9mo ago
super, could you tell me please for what is used coalesce in subquery?
Noronha
NoronhaOP•9mo 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
Mykhailo•9mo 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);
Want results from more Discord servers?
Add your server