##// END OF EJS Templates
jro_apps » redmine
RSS
Fixed: queries with multiple custom fields return no result....
Jean-Philippe Lang -
r662:8da5bad29516
parent child
Show More
Show file before | Show file after | Hide comments
@@ -0,0 +1,22
1 ---
2 queries_001:
3 name: Multiple custom fields query
4 project_id: 1
5 filters: |
6 ---
7 cf_1:
8 :values:
9 - MySQL
10 :operator: "="
11 status_id:
12 :values:
13 - "1"
14 :operator: o
15 cf_2:
16 :values:
17 - "125"
18 :operator: "="
19
20 id: 1
21 is_public: true
22 user_id: 1
Show file before | Show file after | Hide comments
@@ -0,0 +1,31
1 # redMine - project management software
2 # Copyright (C) 2006-2007 Jean-Philippe Lang
3 #
4 # This program is free software; you can redistribute it and/or
5 # modify it under the terms of the GNU General Public License
6 # as published by the Free Software Foundation; either version 2
7 # of the License, or (at your option) any later version.
8 #
9 # This program is distributed in the hope that it will be useful,
10 # but WITHOUT ANY WARRANTY; without even the implied warranty of
11 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
12 # GNU General Public License for more details.
13 #
14 # You should have received a copy of the GNU General Public License
15 # along with this program; if not, write to the Free Software
16 # Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
17
18 require File.dirname(__FILE__) + '/../test_helper'
19
20 class QueryTest < Test::Unit::TestCase
21 fixtures :projects, :users, :trackers, :issue_statuses, :issue_categories, :enumerations, :issues, :custom_fields, :custom_values, :queries
22
23 def test_query_with_multiple_custom_fields
24 query = Query.find(1)
25 assert query.valid?
26 assert query.statement.include?("custom_values.value IN ('MySQL')")
27 issues = Issue.find :all,:include => [ :assigned_to, :status, :tracker, :project, :priority ], :conditions => query.statement
28 assert_equal 1, issues.length
29 assert_equal Issue.find(3), issues.first
30 end
31 end
Show file before | Show file after | Hide comments
@@ -45,7 +45,7 class FeedsController < ApplicationController
45 45 end
46 46
47 47 Issue.with_scope(:find => @find_options) do
48 @issues = Issue.find :all, :include => [:project, :author, :tracker, :status, :custom_values],
48 @issues = Issue.find :all, :include => [:project, :author, :tracker, :status],
49 49 :order => "#{Issue.table_name}.created_on DESC"
50 50 end
51 51 @title = (@project ? @project.name : Setting.app_title) + ": " + (query ? query.name : l(:label_reported_issues))
@@ -65,7 +65,7 class FeedsController < ApplicationController
65 65 end
66 66
67 67 Journal.with_scope(:find => @find_options) do
68 @journals = Journal.find :all, :include => [ :details, :user, {:issue => [:project, :author, :tracker, :status, :custom_values]} ],
68 @journals = Journal.find :all, :include => [ :details, :user, {:issue => [:project, :author, :tracker, :status]} ],
69 69 :order => "#{Journal.table_name}.created_on DESC"
70 70 end
71 71
Show file before | Show file after | Hide comments
@@ -290,10 +290,10 class ProjectsController < ApplicationController
290 290 end
291 291
292 292 if @query.valid?
293 @issue_count = Issue.count(:include => [:status, :project, :custom_values], :conditions => @query.statement)
293 @issue_count = Issue.count(:include => [:status, :project], :conditions => @query.statement)
294 294 @issue_pages = Paginator.new self, @issue_count, @results_per_page, params['page']
295 295 @issues = Issue.find :all, :order => sort_clause,
296 :include => [ :assigned_to, :status, :tracker, :project, :priority, :custom_values ],
296 :include => [ :assigned_to, :status, :tracker, :project, :priority ],
297 297 :conditions => @query.statement,
298 298 :limit => @issue_pages.items_per_page,
299 299 :offset => @issue_pages.current.offset
@@ -369,7 +369,7 class ProjectsController < ApplicationController
369 369 render :action => 'list_issues' and return unless @query.valid?
370 370
371 371 @issues = Issue.find :all, :order => sort_clause,
372 :include => [ :author, :status, :tracker, :priority, :project, :custom_values ],
372 :include => [ :author, :status, :tracker, :priority, :project ],
373 373 :conditions => @query.statement,
374 374 :limit => Setting.issues_export_limit.to_i
375 375
Show file before | Show file after | Hide comments
@@ -164,7 +164,8 class Query < ActiveRecord::Base
164 164 end
165 165
166 166 def statement
167 sql = "1=1"
167 # project/subprojects clause
168 clause = ''
168 169 if has_filter?("subproject_id")
169 170 subproject_ids = []
170 171 if operator_for("subproject_id") == "="
@@ -172,27 +173,29 class Query < ActiveRecord::Base
172 173 else
173 174 subproject_ids = project.active_children.collect{|p| p.id}
174 175 end
175 sql << " AND #{Issue.table_name}.project_id IN (%d,%s)" % [project.id, subproject_ids.join(",")] if project
176 clause << "#{Issue.table_name}.project_id IN (%d,%s)" % [project.id, subproject_ids.join(",")] if project
176 177 else
177 sql << " AND #{Issue.table_name}.project_id=%d" % project.id if project
178 clause << "#{Issue.table_name}.project_id=%d" % project.id if project
178 179 end
180
181 # filters clauses
182 filters_clauses = []
179 183 filters.each_key do |field|
180 184 next if field == "subproject_id"
181 185 v = values_for(field).clone
182 186 next unless v and !v.empty?
183
184 sql = sql + " AND " unless sql.empty?
185 sql << "("
186
187
188 sql = ''
187 189 if field =~ /^cf_(\d+)$/
188 190 # custom field
189 191 db_table = CustomValue.table_name
190 db_field = "value"
191 sql << "#{db_table}.custom_field_id = #{$1} AND "
192 db_field = 'value'
193 sql << "#{Issue.table_name}.id IN (SELECT #{db_table}.customized_id FROM #{db_table} where #{db_table}.customized_type='Issue' AND #{db_table}.customized_id=#{Issue.table_name}.id AND #{db_table}.custom_field_id=#{$1} AND "
192 194 else
193 195 # regular field
194 196 db_table = Issue.table_name
195 197 db_field = field
198 sql << '('
196 199 end
197 200
198 201 # "me" value subsitution
@@ -232,9 +235,11 class Query < ActiveRecord::Base
232 235 when "!~"
233 236 sql = sql + "#{db_table}.#{db_field} NOT LIKE '%#{connection.quote_string(v.first)}%'"
234 237 end
235 sql << ")"
236
238 sql << ')'
239 filters_clauses << sql
237 240 end if filters and valid?
238 sql
241
242 clause << (' AND ' + filters_clauses.join(' AND ')) unless filters_clauses.empty?
243 clause
239 244 end
240 245 end
Show file before | Show file after | Hide comments
@@ -34,10 +34,16 custom_values_004:
34 34 custom_field_id: 2
35 35 customized_id: 1
36 36 id: 7
37 value: "101"
37 value: "125"
38 38 custom_values_005:
39 39 customized_type: Issue
40 40 custom_field_id: 2
41 41 customized_id: 2
42 42 id: 8
43 43 value: ""
44 custom_values_008:
45 customized_type: Issue
46 custom_field_id: 1
47 customized_id: 3
48 id: 11
49 value: "MySQL" No newline at end of file
General Comments 0
You need to be logged in to leave comments. Login now