Doing an update_all with joins in Rails

Question

It's times like this that Rails' abstraction from raw SQL drives me batty. In MySQL I could do this:

UPDATE FROM tasks AS t 
LEFT JOIN projects as p 
ON t.project_id = p.id 
SET t.invoice_id = 7
WHERE p.organization_id == 42
AND t.invoice_id IS NULL

How can I do this in Rails 3.0.1 with eager loading? I've tried all of the following:

Tasks.joins(:project).where('projects.organization_id' => 42, :invoice_id => nil).update_all( :invoice_id => 7 )

And all of the variations of the above. All either gave errors or didn't find anything.

Then I tried to use scope:

Task.scope :find => {:joins => :project, :conditions => ["projects.organization_id == ? AND invoice_id IS NULL", @organization.id] } do
  Task.update_all :invoice_id => @invoice.id
end

This one gave me the error undefined method 'to_sym' for #<Hash:0x1065c6438>.

I've spent way too many hours on this, just to replicate a simple SQL query. Please help!

---

EDIT: Temporary bad solution to get around n+1:

task_ids = Task.select('tasks.id').joins(:project).where('projects.organization_id' => @organization.id, :invoice_id => nil).collect{|t| t.id}
Task.update_all ['invoice_id = ?', @invoice.id], ["id in (#{task_ids.join(',')})"]

---

Answer 1

"UPDATE FROM" is not standard SQL, so it's not surprising if it's not directly supported by Active Record. However, Active Record does give you a way to bypass its abstractions and just issue straight SQL, for those times when you've got to do something it doesn't support. Inside a model:

sql = "UPDATE FROM tasks AS t 
LEFT JOIN projects as p 
ON t.project_id = p.id 
SET t.invoice_id = 7
WHERE p.organization_id == 42
AND t.invoice_id IS NULL"
connection.update_sql(sql)

ActiveRecord::Base also has a "select_by_sql" method that lets your non-standard select statements return regular active record model instances.

Answer 2

I believe at least @ rails 3.0.8 and ARel 2.0.10, we couldn't directly generate UPDATE FROM, but it is possible to get the same result with by resolving the join as a sub-query, e.g.

Task.where(:invoice_id=>nil).
where(:project_id=>Project.where(:organization_id=>42).collect(&:id)).
update_all(:invoice_id => 7)

This generates SQL like:

UPDATE "tasks"
SET "invoice_id" = 7 
WHERE "invoice_id" IS NULL AND "project_id" IN (1,2,3);
-- assuming projects 1,2,3 have organization_id = 42

Of ourse, that's resolving the sub-query in Rails not in SQL. To generate a sub-select in SQL, you can mix in a little Arel like this:

t = Task.arel_table
p = Project.arel_table
Task.where(:invoice_id=>nil).
where(
  t[:project_id].in(
    p.where(
      p[:organization_id].eq(42)
    ).project(p[:id])
  )
).update_all(:invoice_id => 7)

Which generates sql like this:

UPDATE "tasks"
SET "invoice_id" = 7 
WHERE "invoice_id" IS NULL 
AND "project_id" IN (
SELECT "projects"."id" FROM "projects" 
WHERE "projects"."organization_id" = 42
);

There's a pure ARel way of doing this, but the UpdateManager syntax is woefully under-documented