How to pass parameter for sorting in sequelize.query-CodePudding

I have used sequelize and had to write a custom SELECT query. I also have to perform sorting based on the parameter passed by user. So I decided to use replacements. The query looks something like this:

sequelize.query(
      "SELECT SUM(amount), ...... FROM table1 GROUP BY address ORDER BY SUM(:orderBy);", {
      replacements: { orderBy: "amount" }
    })

The problem is sequelize seems to pass amount as string rather than as identifer. So I am getting the following error:

Could not choose a best candidate function. You might need to add explicit type casts

How can I resolve it. thanks.

CodePudding user response：

You can't do it by using replacements or bind options.
You can either use a string concatenation (the worst case scenario that might lead to SQL injection) or try to convert this query to a query against a Sequelize model with some custom attributes in the query.

CodePudding user response：

I`m not sure that you need custom query at all, you always can try:


const result = await Table1Model.findAll({
    replacements: ["amount"],
    attributes: [
        "id",
        [sequelize.literal('(SELECT SUM(amount)::int FROM "table1" WHERE "table1"."x" = "table2"."x")'), 'total_amount'],
    ],
    order: sequelize.literal(`? DESC`),
})