使用ActiveRecord update_all和Rails 4批量更新联接的表 [英] Bulk updating a joined table with ActiveRecord update_all and Rails 4

问题描述

我有一个想在ActiveRecord中编写的PostgreSQL查询(Rails 4)，但是我无法使其正常工作.

I have a PostgreSQL query that I would like to write in ActiveRecord (Rails 4), but I'm having trouble getting it to work correctly.

UPDATE chats AS c
SET email = m.source_name
FROM messages AS m
WHERE c.id = m.chat_id 
  AND m.created_at >= '2014-10-10'

这是我尝试过的:

Chat.joins(:messages)
  .where("message.created_at >= '2014-10-10'")
  .update_all('chat.email = message.source_name')

但是它会创建如下查询:

But it creates a query like this:

UPDATE "chats" 
SET chat.email = message.source_name 
WHERE "chats"."id" IN (
  SELECT "chats"."id" 
  FROM "chats" 
  INNER JOIN "messages" 
    ON "messages"."chat_id" = "chats"."id" 
  WHERE (message.created_at >= '2014-10-10')
)

对此有任何帮助吗?

推荐答案

由于Chat.update_all将添加UPDATE chats SET...，因此，我想到的使用别名(UPDATE chats AS c)进行更新的唯一途径是:使用connection.update和sql字符串:

Since Chat.update_all will add UPDATE chats SET... the only way that I can think of get rails to do an update with an alias (UPDATE chats AS c) is by using connection.update and a sql string:

Chat.connection.update(Q%{
  UPDATE chats AS c 
  SET email = m.source_name
  FROM messages AS m
  WHERE c.id = m.chat_id 
    AND m.created_at >= '2014-10-10'
});

如果您想避免SQL片段，那就不太好了，但是，如果您想使用AREL，则像问题中那样使用联接可能是唯一的方法.

Not great if you want to avoid SQL fragments, but using an a join as in your question may be the only way if you want to use AREL.

这篇关于使用ActiveRecord update_all和Rails 4批量更新联接的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！