CODEWITHSUNDEEP
javascriptmysqlnode.js
Kunal Dholiya
Kunal Dholiya

Reputation: 385

how to add query parameters dynamically after ORDER BY and limit

I'm working on one project where I need to add parameters to the query dynamically. My query is like:

 let query2 = "SELECT al.*,CONVERT_TZ(al.date_created,'+00:00',?) as date_created, user.first_name as agent_name  FROM `account_log` as `al`  inner join  `user` on user.id = al.user_id  WHERE `al`.`account_id` =  ?  ORDER BY `al`.`id` DESC  limit ?,?";
 let params2 = [tz_offset,account_id, offset, limit];

And I want to do below:

query2 += " AND `event_type` = ? ";
params2.push(filter.event_type);

How can I achieve this?

Upvotes: 1

Views: 603

Answers (1)

atokle
atokle

Reputation: 61

Query parameters have to be before ORDER BY and LIMIT.

Add your dynamic parameters first, and add sorting and limit after.

let query2 = "SELECT al.*,CONVERT_TZ(al.date_created,'+00:00',?) as date_created, user.first_name as agent_name  FROM `account_log` as `al`  inner join  `user` on user.id = al.user_id  WHERE `al`.`account_id` =  ?  ";
let orderAndLimit = "ORDER BY `al`.`id` DESC  limit ?,?";
let params2 = [tz_offset,account_id];
let orderAndLimitParams = [offset, limit]

query2 += " AND `event_type` = ? ";
params2.push(filter.event_type);

query2 += orderAndLimit;
params2.push(orderAndLimitParams);

Upvotes: 2

Related Questions