mysql - 如何为使用子查询的 MySQL 编写 AREL UpdateManager 查询

Question

我正在使用 ruby​​ 对 MySQL 数据库运行带有子查询的更新查询。我正在使用 ruby​​ 1.9.3 和 Rails 4.1。

我尝试创建的查询如下:

 UPDATE `items` 
 SET 
     `items`.`status_id` = 12
 WHERE
     `items`.`id` IN (SELECT DISTINCT
             `items`.`id`
         FROM
             `items`
                 LEFT OUTER JOIN
             `statuses` ON `items`.`status_id` = `statuses`.`id`
                 LEFT OUTER JOIN
             `resources` ON `items`.`resource_id` = `resources`.`id`
         WHERE
             `statuses`.`title` LIKE 'On Loan'
                 AND `items`.`duedate` < '2015-04-24'
                 AND `items`.`return_date` IS NULL
         ORDER BY `items`.`duedate`)

我可以使用 AREL 在 ruby​​ 中生成此查询，代码如下:

# Declare Arel objects
i = Item.arel_table
s = Status.arel_table
r = Resource.arel_table

# This is the AREL query that returns the data
overdues = i.project(i[:id]).
join(s, Arel::Nodes::OuterJoin).on(i[:status_id].eq(s[:id])).
join(r, Arel::Nodes::OuterJoin).on(i[:resource_id].eq(r[:id])).
where(s[:title].matches("On Loan").
  and(i[:duedate].lt(DateTime.now.to_date)).
  and(i[:return_date].eq(nil))
  ).
order(i[:duedate])

# Note: You can't chain distinct, otherwise "overdues" becomes a string with the value "DISTINCT".
overdues.distinct

# This creates the update...
u = Arel::UpdateManager.new i.engine
u.table(i)
u.set([[i[:status_id], 10]]).where(i[:id].in(overdues))

这不起作用并返回错误消息:

ActiveRecord::StatementInvalid: Mysql2::Error: 您无法在 FROM 子句中指定要更新的目标表“items”:

我尝试使用 AR“update_all”，但它产生相同的 SQL，因此产生相同的错误。

Item.where(i[:id].in(overdues)).update_all(:status_id => (Status.find_by(:title => "Overdue").id))

经过一些研究，我发现您无法使用引用您要在 MySQL 中更新的表的子查询来运行更新。我在这个网站和更广泛的互联网上看到了许多详细介绍解决方法的帖子。

一个建议是更新应该使用联接而不是子查询。查看了更新管理器背后的代码后，它没有“加入”，所以我不能这样做。

另一个说分两部分运行，但我不知道如何运行，因为 AREL 和 AciveRecord 都是链式操作。

我认为执行此操作的唯一方法是为表添加别名并添加额外的选择(见下文)。这不是很好，但看看是否可以做到这一点会很有用。

UPDATE `items` 
SET `status_id` = 10 
WHERE `items`.`id` IN (
  SELECT x.id 
  FROM
    (SELECT DISTINCT `items`.`id` 
      FROM `items` 
      LEFT OUTER JOIN `statuses` ON `items`.`status_id` = `statuses`.`id` 
      LEFT OUTER JOIN `resources` ON `items`.`resource_id` = `resources`.`id` 
      WHERE `statuses`.`title` LIKE 'On Loan' 
      AND `items`.`duedate` < '2015-04-24' 
      AND `items`.`return_date` IS NULL  
      ORDER BY `items`.`duedate`) x
);

如果我无法让它发挥作用，我可以采用其他两种方法:

1) 我可以对 SQL 进行硬编码，但我想使用 ActiveRecord 并引用模型以使其与数据库无关。

2) 另一种方法是返回所有记录的实例并循环遍历它们进行单独更新。这会产生性能问题，但我可以接受这一点，因为它是一项后台作业，每天不会更新超过几条记录。

更新

我有下面的 AREL 查询，它以我需要的格式生成子查询。

x = Arel::Table.new('x')

overdues = Item.select(x[:id]).from(
Item.select(Item.arel_table[:id]).where(
    Status.arel_table[:title].matches("On Loan").and(
    Item.arel_table[:duedate].lt(DateTime.now.to_date).and(
    Item.arel_table[:return_date].eq(nil))
    )
  ).joins(
    Item.arel_table.join(Status.arel_table, Arel::Nodes::OuterJoin).on(
      Item.arel_table[:status_id].eq(Status.arel_table[:id])
    ).join_sources
  ).joins(
    Item.arel_table.join(Resource.arel_table, Arel::Nodes::OuterJoin).on(
      Item.arel_table[:resource_id].eq(Resource.arel_table[:id])
    ).join_sources
  ).order(Item.arel_table[:duedate]).uniq.as('x')
)

遗憾的是，当我在更新语句中使用它时，它返回一个错误。

TypeError: Cannot visit Item::ActiveRecord_Relation

Answer 1

重新审视这个问题后，我得出的结论是，由于 MySQL 的限制，不可能做到这一点:

ActiveRecord::StatementInvalid: Mysql2::Error: 您无法在 FROM 子句中指定要更新的目标表“items”:

应该可以与其他数据库一起使用(尽管我还没有测试过)。

我可以创建一个临时表，它是原始表的副本，引用它，然后删除临时表，就像这篇文章建议的那样: http://richtextblog.blogspot.co.uk/2007/09/mysql-temporary-tables-and-rails.html 。执行一个简单的子查询似乎需要很大的开销。

我要做的是找到所有 ID 并循环它们并以这种方式更新记录(使用简单的查找和更新)。这会产生开销，但每次运行应该只更新少量记录(不超过 100 条)。该更新将作为计划作业在用户工作时间之外运行，因此不会影响性能。

我仍然觉得奇怪的是，在所有其他类型的 SQL 中我以前从未遇到过这个问题。但你仍然生活和学习。

更新:自从更新了我的 MySQL 版本后，select 语句现在可以工作了。我必须先取出订单才能使其正常工作。

ORDER BY `items`.`duedate`

我现在使用的版本:5.7.19。