Handle DELETE with LIMIT in Arel

MySQL supports DELETE with LIMIT and ORDER BY.

https://dev.mysql.com/doc/refman/8.0/en/delete.html

Before:

```
  Post Destroy (1.0ms)  DELETE FROM `posts` WHERE `posts`.`id` IN (SELECT `id` FROM (SELECT `posts`.`id` FROM `posts` WHERE `posts`.`author_id` = ? ORDER BY `posts`.`id` ASC LIMIT ?) __active_record_temp)  [["author_id", 1], ["LIMIT", 1]]
```

After:

```
  Post Destroy (0.4ms)  DELETE FROM `posts` WHERE `posts`.`author_id` = ? ORDER BY `posts`.`id` ASC LIMIT ?  [["author_id", 1], ["LIMIT", 1]]
```

9 files changed, 78 insertions, 69 deletions

diff --git a/activerecord/lib/active_record/relation.rb b/activerecord/lib/active_record/relation.rb
index db06bd9e26..5bad5bfcc2 100644
--- a/activerecord/lib/active_record/relation.rb
+++ b/activerecord/lib/active_record/relation.rb
@@ -484,9 +484,12 @@ module ActiveRecord
       stmt = Arel::DeleteManager.new
       stmt.from(table)
 
-      if has_join_values? || has_limit_or_offset?
+      if has_join_values? || offset_value
         @klass.connection.join_to_delete(stmt, arel, arel_attribute(primary_key))
       else
+        stmt.key = arel_attribute(primary_key)
+        stmt.take(arel.limit)
+        stmt.order(*arel.orders)
         stmt.wheres = arel.constraints
       end
 
diff --git a/activerecord/lib/arel/delete_manager.rb b/activerecord/lib/arel/delete_manager.rb
index 2def581009..fdba937d64 100644
--- a/activerecord/lib/arel/delete_manager.rb
+++ b/activerecord/lib/arel/delete_manager.rb
@@ -2,6 +2,8 @@
 
 module Arel # :nodoc: all
   class DeleteManager < Arel::TreeManager
+    include TreeManager::StatementMethods
+
     def initialize
       super
       @ast = Nodes::DeleteStatement.new
@@ -12,14 +14,5 @@ module Arel # :nodoc: all
       @ast.relation = relation
       self
     end
-
-    def take(limit)
-      @ast.limit = Nodes::Limit.new(Nodes.build_quoted(limit)) if limit
-      self
-    end
-
-    def wheres=(list)
-      @ast.wheres = list
-    end
   end
 end
diff --git a/activerecord/lib/arel/nodes/delete_statement.rb b/activerecord/lib/arel/nodes/delete_statement.rb
index eaac05e2f6..5be42a084a 100644
--- a/activerecord/lib/arel/nodes/delete_statement.rb
+++ b/activerecord/lib/arel/nodes/delete_statement.rb
@@ -3,8 +3,7 @@
 module Arel # :nodoc: all
   module Nodes
     class DeleteStatement < Arel::Nodes::Node
-      attr_accessor :left, :right
-      attr_accessor :limit
+      attr_accessor :left, :right, :orders, :limit, :key
 
       alias :relation :left
       alias :relation= :left=
@@ -15,6 +14,9 @@ module Arel # :nodoc: all
         super()
         @left = relation
         @right = wheres
+        @orders = []
+        @limit = nil
+        @key = nil
       end
 
       def initialize_copy(other)
@@ -24,13 +26,16 @@ module Arel # :nodoc: all
       end
 
       def hash
-        [self.class, @left, @right].hash
+        [self.class, @left, @right, @orders, @limit, @key].hash
       end
 
       def eql?(other)
         self.class == other.class &&
           self.left == other.left &&
-          self.right == other.right
+          self.right == other.right &&
+          self.orders == other.orders &&
+          self.limit == other.limit &&
+          self.key == other.key
       end
       alias :== :eql?
     end
diff --git a/activerecord/lib/arel/nodes/update_statement.rb b/activerecord/lib/arel/nodes/update_statement.rb
index 5184b1180f..017a553c4c 100644
--- a/activerecord/lib/arel/nodes/update_statement.rb
+++ b/activerecord/lib/arel/nodes/update_statement.rb
@@ -3,8 +3,7 @@
 module Arel # :nodoc: all
   module Nodes
     class UpdateStatement < Arel::Nodes::Node
-      attr_accessor :relation, :wheres, :values, :orders, :limit
-      attr_accessor :key
+      attr_accessor :relation, :wheres, :values, :orders, :limit, :key
 
       def initialize
         @relation = nil
diff --git a/activerecord/lib/arel/tree_manager.rb b/activerecord/lib/arel/tree_manager.rb
index ed47b09a37..149c69ce7a 100644
--- a/activerecord/lib/arel/tree_manager.rb
+++ b/activerecord/lib/arel/tree_manager.rb
@@ -4,6 +4,35 @@ module Arel # :nodoc: all
   class TreeManager
     include Arel::FactoryMethods
 
+    module StatementMethods
+      def take(limit)
+        @ast.limit = Nodes::Limit.new(Nodes.build_quoted(limit)) if limit
+        self
+      end
+
+      def order(*expr)
+        @ast.orders = expr
+        self
+      end
+
+      def key=(key)
+        @ast.key = Nodes.build_quoted(key)
+      end
+
+      def key
+        @ast.key
+      end
+
+      def wheres=(exprs)
+        @ast.wheres = exprs
+      end
+
+      def where(expr)
+        @ast.wheres << expr
+        self
+      end
+    end
+
     attr_reader :ast
 
     def initialize
diff --git a/activerecord/lib/arel/update_manager.rb b/activerecord/lib/arel/update_manager.rb
index fe444343ba..a809dbb307 100644
--- a/activerecord/lib/arel/update_manager.rb
+++ b/activerecord/lib/arel/update_manager.rb
@@ -2,30 +2,14 @@
 
 module Arel # :nodoc: all
   class UpdateManager < Arel::TreeManager
+    include TreeManager::StatementMethods
+
     def initialize
       super
       @ast = Nodes::UpdateStatement.new
       @ctx = @ast
     end
 
-    def take(limit)
-      @ast.limit = Nodes::Limit.new(Nodes.build_quoted(limit)) if limit
-      self
-    end
-
-    def key=(key)
-      @ast.key = Nodes.build_quoted(key)
-    end
-
-    def key
-      @ast.key
-    end
-
-    def order(*expr)
-      @ast.orders = expr
-      self
-    end
-
     ###
     # UPDATE +table+
     def table(table)
@@ -33,15 +17,6 @@ module Arel # :nodoc: all
       self
     end
 
-    def wheres=(exprs)
-      @ast.wheres = exprs
-    end
-
-    def where(expr)
-      @ast.wheres << expr
-      self
-    end
-
     def set(values)
       if String === values
         @ast.values = [values]
diff --git a/activerecord/lib/arel/visitors/mysql.rb b/activerecord/lib/arel/visitors/mysql.rb
index ee75b6bb25..0f7d5aa803 100644
--- a/activerecord/lib/arel/visitors/mysql.rb
+++ b/activerecord/lib/arel/visitors/mysql.rb
@@ -65,6 +65,19 @@ module Arel # :nodoc: all
             collector = inject_join o.values, collector, ", "
           end
 
+          collect_where_for(o, collector)
+        end
+
+        def visit_Arel_Nodes_Concat(o, collector)
+          collector << " CONCAT("
+          visit o.left, collector
+          collector << ", "
+          visit o.right, collector
+          collector << ") "
+          collector
+        end
+
+        def collect_where_for(o, collector)
           unless o.wheres.empty?
             collector << " WHERE "
             collector = inject_join o.wheres, collector, " AND "
@@ -77,15 +90,6 @@ module Arel # :nodoc: all
 
           maybe_visit o.limit, collector
         end
-
-        def visit_Arel_Nodes_Concat(o, collector)
-          collector << " CONCAT("
-          visit o.left, collector
-          collector << ", "
-          visit o.right, collector
-          collector << ") "
-          collector
-        end
     end
   end
 end
diff --git a/activerecord/lib/arel/visitors/to_sql.rb b/activerecord/lib/arel/visitors/to_sql.rb
index 87f8c36f9b..575bfd6e36 100644
--- a/activerecord/lib/arel/visitors/to_sql.rb
+++ b/activerecord/lib/arel/visitors/to_sql.rb
@@ -76,12 +76,8 @@ module Arel # :nodoc: all
         def visit_Arel_Nodes_DeleteStatement(o, collector)
           collector << "DELETE FROM "
           collector = visit o.relation, collector
-          if o.wheres.any?
-            collector << WHERE
-            collector = inject_join o.wheres, collector, AND
-          end
 
-          maybe_visit o.limit, collector
+          collect_where_for(o, collector)
         end
 
         # FIXME: we should probably have a 2-pass visitor for this
@@ -97,12 +93,6 @@ module Arel # :nodoc: all
         end
 
         def visit_Arel_Nodes_UpdateStatement(o, collector)
-          if o.orders.empty? && o.limit.nil?
-            wheres = o.wheres
-          else
-            wheres = [Nodes::In.new(o.key, [build_subselect(o.key, o)])]
-          end
-
           collector << "UPDATE "
           collector = visit o.relation, collector
           unless o.values.empty?
@@ -110,12 +100,7 @@ module Arel # :nodoc: all
             collector = inject_join o.values, collector, ", "
           end
 
-          unless wheres.empty?
-            collector << " WHERE "
-            collector = inject_join wheres, collector, " AND "
-          end
-
-          collector
+          collect_where_for(o, collector)
         end
 
         def visit_Arel_Nodes_InsertStatement(o, collector)
@@ -814,6 +799,21 @@ module Arel # :nodoc: all
           }
         end
 
+        def collect_where_for(o, collector)
+          if o.orders.empty? && o.limit.nil?
+            wheres = o.wheres
+          else
+            wheres = [Nodes::In.new(o.key, [build_subselect(o.key, o)])]
+          end
+
+          unless wheres.empty?
+            collector << " WHERE "
+            collector = inject_join wheres, collector, " AND "
+          end
+
+          collector
+        end
+
         def infix_value(o, collector, value)
           collector = visit o.left, collector
           collector << value
diff --git a/activerecord/test/cases/arel/delete_manager_test.rb b/activerecord/test/cases/arel/delete_manager_test.rb
index 17a5271039..0bad02f4d2 100644
--- a/activerecord/test/cases/arel/delete_manager_test.rb
+++ b/activerecord/test/cases/arel/delete_manager_test.rb
@@ -15,6 +15,7 @@ module Arel
       dm = Arel::DeleteManager.new
       dm.take 10
       dm.from table
+      dm.key = table[:id]
       assert_match(/LIMIT 10/, dm.to_sql)
     end