From d0be40cad168aae558eeb6a9b12acd8d113e3fc9 Mon Sep 17 00:00:00 2001 From: Ryuta Kamizono Date: Wed, 11 Nov 2015 23:05:44 +0900 Subject: Add `columns_for_distinct` for MySQL 5.7 with ONLY_FULL_GROUP_BY In MySQL 5.7.5 and up, ONLY_FULL_GROUP_BY affects handling of queries that use DISTINCT and ORDER BY. It requires the ORDER BY columns in the select list for distinct queries, and requires that the ORDER BY include the distinct column. See https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html --- .../connection_adapters/abstract/schema_statements.rb | 5 +++-- .../connection_adapters/abstract_mysql_adapter.rb | 15 +++++++++++++++ 2 files changed, 18 insertions(+), 2 deletions(-) (limited to 'activerecord/lib/active_record') diff --git a/activerecord/lib/active_record/connection_adapters/abstract/schema_statements.rb b/activerecord/lib/active_record/connection_adapters/abstract/schema_statements.rb index 7bf548fcba..a918a8b035 100644 --- a/activerecord/lib/active_record/connection_adapters/abstract/schema_statements.rb +++ b/activerecord/lib/active_record/connection_adapters/abstract/schema_statements.rb @@ -1028,11 +1028,12 @@ module ActiveRecord end # Given a set of columns and an ORDER BY clause, returns the columns for a SELECT DISTINCT. - # Both PostgreSQL and Oracle overrides this for custom DISTINCT syntax - they + # PostgreSQL, MySQL, and Oracle overrides this for custom DISTINCT syntax - they # require the order columns appear in the SELECT. # # columns_for_distinct("posts.id", ["posts.created_at desc"]) - def columns_for_distinct(columns, orders) #:nodoc: + # + def columns_for_distinct(columns, orders) # :nodoc: columns end diff --git a/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb b/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb index b9a9c22198..068bf356e8 100644 --- a/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb +++ b/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb @@ -786,6 +786,21 @@ module ActiveRecord end end + # In MySQL 5.7.5 and up, ONLY_FULL_GROUP_BY affects handling of queries that use + # DISTINCT and ORDER BY. It requires the ORDER BY columns in the select list for + # distinct queries, and requires that the ORDER BY include the distinct column. + # See https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html + def columns_for_distinct(columns, orders) # :nodoc: + order_columns = orders.reject(&:blank?).map { |s| + # Convert Arel node to string + s = s.to_sql unless s.is_a?(String) + # Remove any ASC/DESC modifiers + s.gsub(/\s+(?:ASC|DESC)\b/i, '') + }.reject(&:blank?).map.with_index { |column, i| "#{column} AS alias_#{i}" } + + [super, *order_columns].join(', ') + end + def strict_mode? self.class.type_cast_config_to_boolean(@config.fetch(:strict, true)) end -- cgit v1.2.3