javascript - 如何在 ORDER BY 和限制之后动态添加查询参数

Question

我正在开发一个需要动态向查询添加参数的项目。我的查询是这样的：

 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];

我想在下面做：

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

我怎样才能做到这一点？

标签： javascriptmysqlnode.js

Answer 1

查询参数必须在 ORDER BY 和 LIMIT 之前。

首先添加您的动态参数，然后添加排序和限制。

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);