在Ruby on Rails中原始的MySQL查询中传递数组

Question

我有一个问题。我有一个查询，返回来自一个表（比如table1）的id，我必须将这些id传递给使用table2的另一个查询。（由于某些原因，编写内部选择或连接不是一个选项）。

查询：

client = Mysql2::Client.new(:host => "localhost", :username => "", :password => "", :database =>"test")
query1 = %Q{select id from table1 where code='ABC123'}
ids = client.query(query1)
query2 = %Q{select * from table2 where `table2`.`table1_id` IN (#{ids}) and status="rejected"}
table2_data = client.query(query2)

ids是Mysql2 :: Result类型。并且，当我执行ids.to_a时，生成的数组具有以下数据：[{"id" => 1}，{"id" => 2}]。我需要一些可行的方法将ids传递给第二个查询。我尝试过ids.to_a，但由于括号[ ]而出错。我还尝试了连接，假设MySQL结果为：

array = ids.to_a  # [1,2,3]
id_new = "("+#{array.join(',')}+")"

id_new变成了字符串"(1,2,3)"，因此IN无法工作。

有没有人能够建议一下如何在原始的MySQL查询中传递id数组？ 我已经尝试寻找答案很久了，但是找不到一个合适的答案。

编辑: 我只能对query1使用Active Record，如果是这种情况，并且ids是一个Active Record对象，有人能否建议如何将其传递给query2中的IN子句，该子句应该是一个原始的SQL查询？

编辑2: 我不能使用Active Record（对于query2）或join，因为它会使查询变得沉重并需要长时间（>10s）才能获取结果（索引存在）。 因此，我正在使用原始查询进行优化。

- codevj

---

在使用ActiveRecord时，您可以使用pluck方法获取ID数组而不是整个模型：ModelForTable1.where(:code => 'ABC123').pluck(:id)将会得到类似[23,92,1,2]的结果。 - Sven Koschnicke

另外，您能否提供更多关于您情况的信息？当您仅排除标准的RoR方式（使用ActiveRecord），但不告诉我们原因时，很难回答您的问题。 - Sven Koschnicke

为什么不使用连接？使用连接，您可以在一个查询中获取所有这些ID。 - ReggieB

嗨，我编辑了问题，解释为什么我不能使用它们。我想我已经找到了一些解决方案。稍后会更新。 - codevj

Answer 1

当我运行类似的查询来模仿你的问题时，我发现ids返回的是一个数组的数组，例如[["1"], ["2"], ["3"]]。

如果你也遇到了这个问题，那么在调用join之前应该先调用ids.flatten：

query2 = %Q{select * from table2 where `table2`.`table1_id` IN (#{ids.flatten.join(',')}) and status="rejected"}

array.flatten 可以去除多余的括号，因此:

[[1], [2], [3]].flatten
# => [1,2,3]

[[1], [2], [3]].flatten.join(',')
# => "1,2,3"

编辑

根据您报告的情况，您收到了一个Mysql2 :: Result 对象，请执行以下操作：ids.to_a.map(&:values).flatten.join（'，'）

to_a 首先将Mysql2 :: Result 转换为类似于哈希表的数组：

[{"id"=>"1"}, {"id"=>"2"}]

然后使用map(&:values)将其转换为以下形式的数组：

[["1"], ["2"]]

这个数组与上面的数组（编辑之前）类似，因此运行flatten.join(',') 将其转换为您要查找的字符串。

请注意，您可以使用常见的快捷方式 flat_map（＆：values）代替执行 map（＆：values）.flatten ，它会产生相同的结果。

- AmitA

---

嗨，请阅读Sarwan Kumar答案下的评论。我的结果不同。因为ids是Mysql2 :: Result类型，所以flatten和join在其上不起作用。 - codevj

Answer 2

您确定它不能工作是因为它是一个字符串吗？我认为它不能工作是因为有重复的括号。请尝试这个：

array = ids.flat_map(&:values).join(',')
query2 = %Q{select * from table2 where `table2`.`table1_id` IN (#{array}) and status="rejected"}

我建议使用像ORM（对象关系映射）这样的工具，比如ActiveRecord或Sequel宝石 - 特别是因为手动构建数据库查询很容易出错，会导致 SQL 注入等漏洞。

- spickermann

---

我可以用它来查询query1，但是无论如何，我都不知道如何将ids传递给query2。你的解决方案也行不通。当我按照你的建议去做时，我得到了这样的结果："{"id"=>1},{"id"=>2}"，而我只需要(1,2)。 - codevj

@VJ_03：抱歉，我错过了ids不是一个数组的事实。我更新了我的答案，现在应该可以工作了。 - spickermann

Answer 3

如果您发布的主要原因是学习如何从哈希数组中提取数据，那么您可以忽略这个答案。

然而，如果您想要从数据库中获取数据的最佳方法，我建议您使用ActiveRecord来为您完成繁重的工作：

class Table1 < ActiveRecord::Base
  self.table_name = :table1
  has_many :table2s
end

class Table2 < ActiveRecord::Base
  self.table_name = :table2
  belongs_to :table1
end

table2_data = Table2.joins(:table1).where(table1: {code: 'ABC123'}, status: 'rejected') 

一个关键点是，SQL join 会有效地为您处理 ID 的处理。您可以自己编写 SQL join，但是 ActiveRecord 将为您完成此操作，并允许您添加其他查询，以便您可以在一个查询中获取所需的数据。

- ReggieB

Answer 4

你可以使用逗号来连接数组，就像下面的代码一样。

ids = ids.to_a.map{|h| h['id']}
query2 = %Q{select * from table2 where `table2`.`table1_id` IN (#{ids.join(',')}) and status="rejected"}  
table2_data = client.query(query2)

它将正常工作。

- Sarwan Kumar

---

但是ids是Mysql2 :: Result类型而不是数组，join（）无法在其上工作。此外，当我执行ids.to_a时，生成的数组具有类似于以下数据的数据：[{"id"=>1}，{"id"=>2}]。为了传递给第二个查询，我只需要（1,2），而不是作为字符串/数组，而是像我们通常在mysql查询中传递的那样。如果我没有表达清楚，请告诉我。谢谢。 - codevj

我已经更新了我的回答。 现在，你可以先将MySQL数组更改为Ruby数组： [{"id"=>1}, {"id"=>2}].to_a.map{|h| h['id']} 将返回 [1,2]，然后你可以使用 join(',') 连接 [1,2]，将返回 (1,2)。 - Sarwan Kumar