##// END OF EJS Templates
jro_apps » redmine
RSS
Search custom fields and journals with different queries to take advantage of indexes on text columns if present....
Jean-Philippe Lang -
r13473:742895183aeb
parent child
Show More
Side by Side Unified
Context file:
r13473:742895183aeb Loading diff...:
Show file before | Show file after | Hide comments
@@ -46,13 +46,8 class Issue < ActiveRecord::Base
46 acts_as_attachable :after_add => :attachment_added, :after_remove => :attachment_removed
46 acts_as_attachable :after_add => :attachment_added, :after_remove => :attachment_removed
47 acts_as_customizable
47 acts_as_customizable
48 acts_as_watchable
48 acts_as_watchable
49 acts_as_searchable :columns => ['subject', "#{table_name}.description", "#{Journal.table_name}.notes"],
49 acts_as_searchable :columns => ['subject', "#{table_name}.description"],
50 :preload => [:project, :status, :tracker],
50 :preload => [:project, :status, :tracker]
51 :scope => lambda { joins(:project).
52 joins("LEFT OUTER JOIN #{Journal.table_name} ON #{Journal.table_name}.journalized_type='Issue'" +
53 " AND #{Journal.table_name}.journalized_id = #{Issue.table_name}.id" +
54 " AND (#{Journal.table_name}.private_notes = #{connection.quoted_false}" +
55 " OR (#{Project.allowed_to_condition(User.current, :view_private_notes)}))") }
56
51
57 acts_as_event :title => Proc.new {|o| "#{o.tracker.name} ##{o.id} (#{o.status}): #{o.subject}"},
52 acts_as_event :title => Proc.new {|o| "#{o.tracker.name} ##{o.id} (#{o.status}): #{o.subject}"},
58 :url => Proc.new {|o| {:controller => 'issues', :action => 'show', :id => o.id}},
53 :url => Proc.new {|o| {:controller => 'issues', :action => 'show', :id => o.id}},
Show file before | Show file after | Hide comments
@@ -48,8 +48,9 module Redmine
48 searchable_options[:project_key] ||= "#{table_name}.project_id"
48 searchable_options[:project_key] ||= "#{table_name}.project_id"
49 searchable_options[:date_column] ||= :created_on
49 searchable_options[:date_column] ||= :created_on
50
50
51 # Should we search custom fields on this model ?
51 # Should we search additional associations on this model ?
52 searchable_options[:search_custom_fields] = !reflect_on_association(:custom_values).nil?
52 searchable_options[:search_custom_fields] = reflect_on_association(:custom_values).present?
53 searchable_options[:search_journals] = reflect_on_association(:journals).present?
53
54
54 send :include, Redmine::Acts::Searchable::InstanceMethods
55 send :include, Redmine::Acts::Searchable::InstanceMethods
55 end
56 end
@@ -75,11 +76,6 module Redmine
75 # Issue.search_result_ranks_and_ids("foo")
76 # Issue.search_result_ranks_and_ids("foo")
76 # # => [[1419595329, 69], [1419595622, 123]]
77 # # => [[1419595329, 69], [1419595622, 123]]
77 def search_result_ranks_and_ids(tokens, user=User.current, projects=nil, options={})
78 def search_result_ranks_and_ids(tokens, user=User.current, projects=nil, options={})
78 if projects.is_a?(Array) && projects.empty?
79 # no results
80 return []
81 end
82
83 tokens = [] << tokens unless tokens.is_a?(Array)
79 tokens = [] << tokens unless tokens.is_a?(Array)
84 projects = [] << projects if projects.is_a?(Project)
80 projects = [] << projects if projects.is_a?(Project)
85
81
@@ -87,36 +83,63 module Redmine
87 columns = columns[0..0] if options[:titles_only]
83 columns = columns[0..0] if options[:titles_only]
88
84
89 token_clauses = columns.collect {|column| "(#{search_token_match_statement(column)})"}
85 token_clauses = columns.collect {|column| "(#{search_token_match_statement(column)})"}
86 sql = (['(' + token_clauses.join(' OR ') + ')'] * tokens.size).join(options[:all_words] ? ' AND ' : ' OR ')
87 tokens_conditions = [sql, * (tokens.collect {|w| "%#{w}%"} * token_clauses.size).sort]
88
89 r = fetch_ranks_and_ids(search_scope(user, projects).where(tokens_conditions), options[:limit])
90 sort_and_limit_results = false
90
91
91 if !options[:titles_only] && searchable_options[:search_custom_fields]
92 if !options[:titles_only] && searchable_options[:search_custom_fields]
92 searchable_custom_fields = CustomField.where(:type => "#{self.name}CustomField", :searchable => true)
93 searchable_custom_fields = CustomField.where(:type => "#{self.name}CustomField", :searchable => true).to_a
93 fields_by_visibility = searchable_custom_fields.group_by {|field|
94
94 field.visibility_by_project_condition(searchable_options[:project_key], user, "cfs.custom_field_id")
95 if searchable_custom_fields.any?
95 }
96 fields_by_visibility = searchable_custom_fields.group_by {|field|
96 # only 1 subquery for all custom fields with the same visibility statement
97 field.visibility_by_project_condition(searchable_options[:project_key], user, "#{CustomValue.table_name}.custom_field_id")
97 fields_by_visibility.each do |visibility, fields|
98 }
98 ids = fields.map(&:id).join(',')
99 clauses = []
99 sql = "#{table_name}.id IN (SELECT cfs.customized_id FROM #{CustomValue.table_name} cfs" +
100 fields_by_visibility.each do |visibility, fields|
100 " WHERE cfs.customized_type='#{self.name}' AND cfs.customized_id=#{table_name}.id" +
101 clauses << "(#{CustomValue.table_name}.custom_field_id IN (#{fields.map(&:id).join(',')}) AND (#{visibility}))"
101 " AND cfs.custom_field_id IN (#{ids})" +
102 end
102 " AND #{search_token_match_statement('cfs.value')}" +
103 visibility = clauses.join(' OR ')
103 " AND #{visibility})"
104
104 token_clauses << sql
105 sql = ([search_token_match_statement("#{CustomValue.table_name}.value")] * tokens.size).join(options[:all_words] ? ' AND ' : ' OR ')
106 tokens_conditions = [sql, * tokens.collect {|w| "%#{w}%"}.sort]
107
108 r |= fetch_ranks_and_ids(
109 search_scope(user, projects).
110 joins(:custom_values).
111 where(visibility).
112 where(tokens_conditions),
113 options[:limit]
114 )
115
116 sort_and_limit_results = true
105 end
117 end
106 end
118 end
107
119
108 sql = (['(' + token_clauses.join(' OR ') + ')'] * tokens.size).join(options[:all_words] ? ' AND ' : ' OR ')
120 if !options[:titles_only] && searchable_options[:search_journals]
121 sql = ([search_token_match_statement("#{Journal.table_name}.notes")] * tokens.size).join(options[:all_words] ? ' AND ' : ' OR ')
122 tokens_conditions = [sql, * tokens.collect {|w| "%#{w}%"}.sort]
109
123
110 tokens_conditions = [sql, * (tokens.collect {|w| "%#{w}%"} * token_clauses.size).sort]
124 r |= fetch_ranks_and_ids(
125 search_scope(user, projects).
126 joins(:journals).
127 where("#{Journal.table_name}.private_notes = ? OR (#{Project.allowed_to_condition(user, :view_private_notes)})", false).
128 where(tokens_conditions),
129 options[:limit]
130 )
111
131
112 search_scope(user, projects).
132 sort_and_limit_results = true
113 reorder(searchable_options[:date_column] => :desc, :id => :desc).
133 end
114 where(tokens_conditions).
134
115 limit(options[:limit]).
135 if sort_and_limit_results
116 uniq.
136 r = r.sort.reverse
117 pluck(searchable_options[:date_column], :id).
137 if options[:limit] && r.size > options[:limit]
118 # converts timestamps to integers for faster sort
138 r = r[0, options[:limit]]
119 map {|timestamp, id| [timestamp.to_i, id]}
139 end
140 end
141
142 r
120 end
143 end
121
144
122 def search_token_match_statement(column, value='?')
145 def search_token_match_statement(column, value='?')
@@ -129,8 +152,24 module Redmine
129 end
152 end
130 private :search_token_match_statement
153 private :search_token_match_statement
131
154
155 def fetch_ranks_and_ids(scope, limit)
156 scope.
157 reorder(searchable_options[:date_column] => :desc, :id => :desc).
158 limit(limit).
159 uniq.
160 pluck(searchable_options[:date_column], :id).
161 # converts timestamps to integers for faster sort
162 map {|timestamp, id| [timestamp.to_i, id]}
163 end
164 private :fetch_ranks_and_ids
165
132 # Returns the search scope for user and projects
166 # Returns the search scope for user and projects
133 def search_scope(user, projects)
167 def search_scope(user, projects)
168 if projects.is_a?(Array) && projects.empty?
169 # no results
170 return none
171 end
172
134 scope = (searchable_options[:scope] || self)
173 scope = (searchable_options[:scope] || self)
135 if scope.is_a? Proc
174 if scope.is_a? Proc
136 scope = scope.call
175 scope = scope.call
General Comments 0
You need to be logged in to leave comments. Login now