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 and I took a screenshot of the error here

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

• javascript
• node.js
• sequelize.js

• 1 Are you familiar with concatenating variables to strings? – Taplar Commented Dec 28, 2018 at 23:26
• @Hydrothermal String concatenation doesn't work with sequelize literal – proton Commented Dec 28, 2018 at 23:27
• 1 What makes you say that? literal() is being given a string as an argument. It doesn't matter how it's constructed. Just that it is a valid string. literal('abc') and literal('ab'+ 'c') would both pass the same string to the method – Taplar Commented Dec 28, 2018 at 23:29
• There seems to be a problem with the way sequelize parses concatenated SQL strings, take a look at the query: pastebin.com/u8tr4Xbt The image is the error gotten when I run that: imagebin.ca/v/4RbsQOAPaxvO – proton Commented Dec 28, 2018 at 23:39

2 Answers 2

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.

You can put your bind object right inside your query object:

let status = 'Pending';
const defaultingLoans = await Loan.findAll({
    where: database.sequelize.literal('EXISTS(SELECT * FROM "Instalments" WHERE "Instalments"."loanId" = "Loan"."id" AND "Instalments"."status" = $status)'),
    bind: {status}
});