jro_apps/redmine Commit - r12206:77ac3b7891d4

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

Jean-Philippe Lang -

r12206:77ac3b7891d4

parent child

Context file:

r12206:77ac3b7891d4

Collapse all files

app/models/custom_field.rb +3 -2

                 format.join_for_order_statement(self)
               end
-              def visibility_by_project_condition(project_key=nil, user=User.current)
+              def visibility_by_project_condition(project_key=nil, user=User.current, id_column=nil)
                 if visible? || user.admin?
                   "1=1"
                 elsif user.anonymous?
                   "1=0"
                 else
                   project_key ||= "#{self.class.customized_class.table_name}.project_id"
+                  id_column ||= id
                   "#{project_key} IN (SELECT DISTINCT m.project_id FROM #{Member.table_name} m" +
                     " INNER JOIN #{MemberRole.table_name} mr ON mr.member_id = m.id" +
                     " INNER JOIN #{table_name_prefix}custom_fields_roles#{table_name_suffix} cfr ON cfr.role_id = mr.role_id" +
-                    " WHERE m.user_id = #{user.id} AND cfr.custom_field_id = #{id})"
+                    " WHERE m.user_id = #{user.id} AND cfr.custom_field_id = #{id_column})"
                 end
               end

app/models/issue_custom_field.rb +7 -6

                 super || (roles & user.roles_for_project(project)).present?
               end
-              def visibility_by_project_condition(*args)
+              def visibility_by_project_condition(project_key=nil, user=User.current, id_column=nil)
                 sql = super
-                additional_sql = "#{Issue.table_name}.tracker_id IN (SELECT tracker_id FROM #{table_name_prefix}custom_fields_trackers#{table_name_suffix} WHERE custom_field_id = #{id})"
+                id_column ||= id
-                unless is_for_all?
+                tracker_condition = "#{Issue.table_name}.tracker_id IN (SELECT tracker_id FROM #{table_name_prefix}custom_fields_trackers#{table_name_suffix} WHERE custom_field_id = #{id_column})"
-                  additional_sql << " AND #{Issue.table_name}.project_id IN (SELECT project_id FROM #{table_name_prefix}custom_fields_projects#{table_name_suffix} WHERE custom_field_id = #{id})"
+                project_condition = "EXISTS (SELECT 1 FROM #{CustomField.table_name} ifa WHERE ifa.is_for_all = #{connection.quoted_true} AND ifa.id = #{id_column})" +
-                end
+                  " OR #{Issue.table_name}.project_id IN (SELECT project_id FROM #{table_name_prefix}custom_fields_projects#{table_name_suffix} WHERE custom_field_id = #{id_column})"
-                "((#{sql}) AND (#{additional_sql}))"
+                "((#{sql}) AND (#{tracker_condition}) AND (#{project_condition}))"
               end
               def validate_custom_field

lib/plugins/acts_as_searchable/lib/acts_as_searchable.rb +10 -5

@@ -82,11 +82,16 module Redmine
82		82
83	if !options[:titles_only] && searchable_options[:search_custom_fields]	83	if !options[:titles_only] && searchable_options[:search_custom_fields]
84	searchable_custom_fields = CustomField.where(:type => "#{self.name}CustomField", :searchable => true)	84	searchable_custom_fields = CustomField.where(:type => "#{self.name}CustomField", :searchable => true)
85	searchable_custom_fields.~~each~~ do \|field\|	85	fields_by_visibility = searchable_custom_fields.group_by {\|field\|
86	sql = "#{table_name}.id IN (SELECT customized_id FROM #{CustomValue.table_name}" +	86	field.visibility_by_project_condition(searchable_options[:project_key], user, "cfs.custom_field_id")
87	" WHERE customized_type='#{self.name}' AND customized_id=#{table_name}.id AND LOWER(value) LIKE ?" +	87	}
88	" AND #{CustomValue.table_name}.custom_field_id = #{field.id})" +	88	# only 1 subquery for all custom fields with the same visibility statement
89	" AND #{field.visibility_by_project_condition(searchable_options[:project_key], user)}"	89	fields_by_visibility.each do \|visibility, fields\|
		90	ids = fields.map(&:id).join(',')
		91	sql = "#{table_name}.id IN (SELECT cfs.customized_id FROM #{CustomValue.table_name} cfs" +
		92	" WHERE cfs.customized_type='#{self.name}' AND cfs.customized_id=#{table_name}.id AND LOWER(cfs.value) LIKE ?" +
		93	" AND cfs.custom_field_id IN (#{ids})" +
		94	" AND #{visibility})"
90	token_clauses << sql	95	token_clauses << sql
91	end	96	end
92	end	97	end

General Comments 0

Write
Preview

You need to be logged in to leave comments. Login now

No TODOs yet

	Site-wide shortcuts
/	Use quick search box
g h	Goto home page
g g	Goto my private gists page
g G	Goto my public gists page
g 0-9	Goto bookmarked items from 0-9
n r	New repository page
n g	New gist page

	Repositories
g s	Goto summary page
g c	Goto changelog page
g f	Goto files page
g F	Goto files page with file search activated
g p	Goto pull requests page
g o	Goto repository settings
g O	Goto repository access permissions settings
t s	Toggle sidebar on some pages