Question 114817 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to pass parameter for sorting in sequelize.query

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.

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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`),
})
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search