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(',')})"]