From 43b99f290a8070196919a68999db87873257b7b8 Mon Sep 17 00:00:00 2001 From: Jon Leighton Date: Wed, 10 Aug 2011 00:03:49 +0100 Subject: Support for multi-table updates with limits, offsets and orders --- .../abstract/database_statements.rb | 3 +++ .../connection_adapters/mysql2_adapter.rb | 23 +++++++++++++++++++++- .../connection_adapters/mysql_adapter.rb | 23 +++++++++++++++++++++- 3 files changed, 47 insertions(+), 2 deletions(-) (limited to 'activerecord/lib/active_record') diff --git a/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb b/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb index 7543d35d3b..83e64d3c43 100644 --- a/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb +++ b/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb @@ -312,6 +312,9 @@ module ActiveRecord def join_to_update(update, select) #:nodoc: subselect = select.clone subselect.ast.cores.last.projections = [update.ast.key] + + update.ast.limit = nil + update.ast.orders = [] update.wheres = [update.ast.key.in(subselect)] end diff --git a/activerecord/lib/active_record/connection_adapters/mysql2_adapter.rb b/activerecord/lib/active_record/connection_adapters/mysql2_adapter.rb index c01a64e354..172d08b6f4 100644 --- a/activerecord/lib/active_record/connection_adapters/mysql2_adapter.rb +++ b/activerecord/lib/active_record/connection_adapters/mysql2_adapter.rb @@ -577,8 +577,29 @@ module ActiveRecord where_sql end + # In the simple case, MySQL allows us to place JOINs directly into the UPDATE + # query. However, this does not allow for LIMIT, OFFSET and ORDER. To support + # these, we must use a subquery. However, MySQL is too stupid to create a + # temporary table for this automatically, so we have to give it some prompting + # in the form of a subsubquery. Ugh! def join_to_update(update, select) #:nodoc: - update.table select.ast.cores.last.source + if select.limit || select.offset || select.orders.any? + subsubselect = select.ast.clone + subsubselect.cores.last.projections = [update.ast.key] + subsubselect = Arel::Nodes::TableAlias.new( + Arel::Nodes::Grouping.new(subsubselect), + '__active_record_temp' + ) + + subselect = Arel::SelectManager.new(select.engine, subsubselect) + subselect.project(Arel::Table.new('__active_record_temp')[update.ast.key.name]) + + update.ast.limit = nil + update.ast.orders = [] + update.wheres = [update.ast.key.in(subselect)] + else + update.table select.ast.cores.last.source + end end protected diff --git a/activerecord/lib/active_record/connection_adapters/mysql_adapter.rb b/activerecord/lib/active_record/connection_adapters/mysql_adapter.rb index ea0970028c..bd6cb2d3b8 100644 --- a/activerecord/lib/active_record/connection_adapters/mysql_adapter.rb +++ b/activerecord/lib/active_record/connection_adapters/mysql_adapter.rb @@ -491,8 +491,29 @@ module ActiveRecord execute("RELEASE SAVEPOINT #{current_savepoint_name}") end + # In the simple case, MySQL allows us to place JOINs directly into the UPDATE + # query. However, this does not allow for LIMIT, OFFSET and ORDER. To support + # these, we must use a subquery. However, MySQL is too stupid to create a + # temporary table for this automatically, so we have to give it some prompting + # in the form of a subsubquery. Ugh! def join_to_update(update, select) #:nodoc: - update.table select.ast.cores.last.source + if select.limit || select.offset || select.orders.any? + subsubselect = select.ast.clone + subsubselect.cores.last.projections = [update.ast.key] + subsubselect = Arel::Nodes::TableAlias.new( + Arel::Nodes::Grouping.new(subsubselect), + '__active_record_temp' + ) + + subselect = Arel::SelectManager.new(select.engine, subsubselect) + subselect.project(Arel::Table.new('__active_record_temp')[update.ast.key.name]) + + update.ast.limit = nil + update.ast.orders = [] + update.wheres = [update.ast.key.in(subselect)] + else + update.table select.ast.cores.last.source + end end # SCHEMA STATEMENTS ======================================== -- cgit v1.2.3