I have this subquery that is used to check the existence of a column related to the source model.
const defaultingLoans = await Loan.findAll({
where: {
[Op.and]: database.sequelize.literal('EXISTS(SELECT * FROM "Instalments" WHERE "Instalments"."loanId" = "Loan"."id" AND "Instalments"."status" = 'pending')')
}
});
The query works fine but the value pending
ideally won't be fixed so I'll like to have a variable there that can be used to query for different status.
How can I replace the pending string with a variable.
Concatenation didn't work here because Sequelize has a weird way of parsing concatenated SQL queries which result in an error. An example is here https://pastebin.com/u8tr4Xbt and I took a screenshot of the error here
Function fnCreates a object representing a database function. This can be used in search queries, both in where and order parts, and as default values in column definitions. If you want to refer to columns in your function, you should use sequelize.
sequelize. literal(`EXISTS(SELECT * FROM "Instalments" WHERE "Instalments". "loanId" = "Loan". "id" AND "Instalments".
The Sequelize setters and getters might not be used here. So setting raw to true provides me the desired output with a cleaner look & feel.
The answer: by combining the attributes option of the finder methods (such as findAll ) with the sequelize. literal utility function, that allows you to directly insert arbitrary content into the query without any automatic escaping.
You can turn defaultingLoans
into a function which accepts an amount:
const defaultingLoans = amount => await Loan.findAll({
where: {
[Op.and]: database.sequelize.literal(`EXISTS(SELECT * FROM "Instalments" WHERE "Instalments"."loanId" = "Loan"."id" AND "Instalments"."amount" = ${amount})`)
}
});
usage:
const loans = defaultingLoans(2000);
EDIT: Although it should be noted here that you need to sanitize or quote the amount
getting passed into this function to avoid SQL injection attacks.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With