Ruby应用PostgreSQL IN查询参数类型错误的排查与解决

最近我在开发一个连接PostgreSQL的Ruby应用时，碰到了一个参数类型不匹配的坑——用ID列表做IN查询时，PostgreSQL报错说字符串无法解析成整数。

错误详情 #

直接看报错信息：

ActionView::Template::Error (PG::InvalidTextRepresentation: ERROR: invalid input syntax for integer: "91,147,149,150,152,140,160,121,125,161"
LINE 1: ...= '2018-05-19 21:59:59.999999') AND (items.id IN ('91,147,14...
                                                             ^
: SELECT "items".* FROM "items"
INNER JOIN taggings ON taggings.taggable_id = items.id
INNER JOIN tags on taggings.tag_id = tags.id
WHERE "items"."venue_id" = $1
AND "items"."is_active" = $2
AND "items"."category_id" IN (SELECT "categories"."id" FROM "categories" INNER JOIN "report_categories" ON "categories"."id" = "report_categories"."category_id" WHERE "report_categories"."report_id" = $3)
AND ("items"."state" IN ('queued','published','archived'))
AND (found_at >= '2018-05-01')
AND (found_at <= '2018-05-19 21:59:59.999999')
AND (items.id IN ('91,147,149,150,152,140,160,121,125,161'))):

问题出在items.id IN ('91,147,149,150,152,140,160,121,125,161')这部分：整个ID列表被当成了一个带引号的字符串，PostgreSQL自然没法把它解析成整数类型的ID。

原实现代码（问题所在） #

if report_tags
  tolist = JSON.parse(report_tags).join(',').gsub(/"/, '').sub!(/^,/, '')
  scoped = scoped.joins("INNER JOIN taggings ON taggings.taggable_id = items.id INNER JOIN tags on taggings.tag_id = tags.id").where("items.id IN (?)", tolist)
end

这里的问题很明显：我把JSON解析后的数组用join(',')转成了单个字符串，传给Active Record的where方法时，它会自动给这个字符串加上引号，导致SQL里变成了单个字符串参数，而不是多个整数参数。

修正后的代码 #

if report_tags
  tolist = JSON.parse(report_tags).reject { |e| e.to_s.empty? }
  scoped = scoped
    .joins("INNER JOIN taggings ON taggings.taggable_id = items.id INNER JOIN tags on taggings.tag_id = tags.id")
    .where("tags.id IN (?)", tolist)
end

修正点有两个：

1. 保留数组格式：不再把数组转成字符串，而是用reject过滤掉空元素，直接把数组传给where方法——Active Record会自动把数组转换成SQL里的多个整数参数，生成正确的IN (91,147,...)格式。
2. 修正查询字段：结合JOIN的表结构，应该查询tags.id而不是items.id，这样逻辑上更合理（通过关联标签ID来筛选对应的item）。

调整后，参数类型不匹配的问题就解决了，SQL能正确解析整数ID列表。

内容的提问来源于stack exchange，提问作者Nezir