From 77ac3b7891d40196b04f41af4da357a95b90d149 Mon Sep 17 00:00:00 2001 From: Jean-Philippe Lang Date: Sun, 5 Jan 2014 14:23:36 +0000 Subject: 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 --- lib/plugins/acts_as_searchable/lib/acts_as_searchable.rb | 15 ++++++++++----- 1 file changed, 10 insertions(+), 5 deletions(-) (limited to 'lib/plugins') 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 -- cgit v1.2.3