Reduces the number of subqueries when searching with many custom fields set as searchable (#15781).

git-svn-id: http://svn.redmine.org/redmine/trunk@12481 e93f8b46-1217-0410-a6f0-8f06a7374b81

1 files changed, 10 insertions, 5 deletions

diff --git a/lib/plugins/acts_as_searchable/lib/acts_as_searchable.rb b/lib/plugins/acts_as_searchable/lib/acts_as_searchable.rb
index 3e91c72dc..dbdfa8992 100644
--- a/lib/plugins/acts_as_searchable/lib/acts_as_searchable.rb
+++ b/lib/plugins/acts_as_searchable/lib/acts_as_searchable.rb
@@ -82,11 +82,16 @@ module Redmine
 
             if !options[:titles_only] && searchable_options[:search_custom_fields]
               searchable_custom_fields = CustomField.where(:type => "#{self.name}CustomField", :searchable => true)
-              searchable_custom_fields.each do |field|
-                sql = "#{table_name}.id IN (SELECT customized_id FROM #{CustomValue.table_name}" +
-                  " WHERE customized_type='#{self.name}' AND customized_id=#{table_name}.id AND LOWER(value) LIKE ?" +
-                  " AND #{CustomValue.table_name}.custom_field_id = #{field.id})" +
-                  " AND #{field.visibility_by_project_condition(searchable_options[:project_key], user)}"
+              fields_by_visibility = searchable_custom_fields.group_by {|field|
+                field.visibility_by_project_condition(searchable_options[:project_key], user, "cfs.custom_field_id")
+              }
+              # only 1 subquery for all custom fields with the same visibility statement
+              fields_by_visibility.each do |visibility, fields|
+                ids = fields.map(&:id).join(',')
+                sql = "#{table_name}.id IN (SELECT cfs.customized_id FROM #{CustomValue.table_name} cfs" +
+                  " WHERE cfs.customized_type='#{self.name}' AND cfs.customized_id=#{table_name}.id AND LOWER(cfs.value) LIKE ?" +
+                  " AND cfs.custom_field_id IN (#{ids})" +
+                  " AND #{visibility})"
                 token_clauses << sql
               end
             end