How can I bind a variable to sequelize literal?

user_4748949 0
Viewed 28738

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

sequelize.js node.js javascript
2 Answers

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.

user_7532370

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}
});
user_53632240
Related