jro_apps/redmine Commit - r662:8da5bad29516

Fixed: queries with multiple custom fields return no result....

Jean-Philippe Lang -

r662:8da5bad29516

parent child

Context file:

r662:8da5bad29516

Collapse all files

test/fixtures/queries.yml created 644 +22 0

@@ -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

test/unit/query_test.rb created 644 +31 0

@@ -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

app/controllers/feeds_controller.rb +2 -2

                 end
                 Issue.with_scope(:find => @find_options) do
-                  @issues = Issue.find :all, :include => [:project, :author, :tracker, :status, :custom_values],
+                  @issues = Issue.find :all, :include => [:project, :author, :tracker, :status],
                                              :order => "#{Issue.table_name}.created_on DESC"
                 end
                 @title = (@project ? @project.name : Setting.app_title) + ": " + (query ? query.name : l(:label_reported_issues))
                 end
                 Journal.with_scope(:find => @find_options) do
-                  @journals = Journal.find :all, :include => [ :details, :user, {:issue => [:project, :author, :tracker, :status, :custom_values]} ],
+                  @journals = Journal.find :all, :include => [ :details, :user, {:issue => [:project, :author, :tracker, :status]} ],
                                                  :order => "#{Journal.table_name}.created_on DESC"
                 end

app/controllers/projects_controller.rb +3 -3

                 end
                 if @query.valid?
-                  @issue_count = Issue.count(:include => [:status, :project, :custom_values], :conditions => @query.statement)
+                  @issue_count = Issue.count(:include => [:status, :project], :conditions => @query.statement)
                   @issue_pages = Paginator.new self, @issue_count, @results_per_page, params['page']
                   @issues = Issue.find :all, :order => sort_clause,
-              						:include => [ :assigned_to, :status, :tracker, :project, :priority, :custom_values ],
+              						:include => [ :assigned_to, :status, :tracker, :project, :priority ],
               						:conditions => @query.statement,
               						:limit  =>  @issue_pages.items_per_page,
               						:offset =>  @issue_pages.current.offset
                 render :action => 'list_issues' and return unless @query.valid?
                 @issues =  Issue.find :all, :order => sort_clause,
-            						:include => [ :author, :status, :tracker, :priority, :project, :custom_values ],
+            						:include => [ :author, :status, :tracker, :priority, :project ],
             						:conditions => @query.statement,
             						:limit => Setting.issues_export_limit.to_i

app/models/query.rb +17 -12

               end
               def statement
-                sql = "1=1"
+                # project/subprojects clause
+                clause = ''
                 if has_filter?("subproject_id")
                   subproject_ids = []
                   if operator_for("subproject_id") == "="
                   else
                     subproject_ids = project.active_children.collect{|p| p.id}
                   end
-                  sql << " AND #{Issue.table_name}.project_id IN (%d,%s)" % [project.id, subproject_ids.join(",")] if project
+                  clause << "#{Issue.table_name}.project_id IN (%d,%s)" % [project.id, subproject_ids.join(",")] if project
                 else
-                  sql << " AND #{Issue.table_name}.project_id=%d" % project.id if project
+                  clause << "#{Issue.table_name}.project_id=%d" % project.id if project
                 end
+                # filters clauses
+                filters_clauses = []
                 filters.each_key do |field|
                   next if field == "subproject_id"
                   v = values_for(field).clone
                   next unless v and !v.empty?
-                  sql = sql + " AND " unless sql.empty?
+                  sql = ''
-                  sql << "("
                   if field =~ /^cf_(\d+)$/
                     # custom field
                     db_table = CustomValue.table_name
-                    db_field = "value"
+                    db_field = 'value'
-                    sql << "#{db_table}.custom_field_id = #{$1} AND "
+                    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 "
                   else
                     # regular field
                     db_table = Issue.table_name
                     db_field = field
+                    sql << '('
                   end
                   # "me" value subsitution
                   when "!~"
                     sql = sql + "#{db_table}.#{db_field} NOT LIKE '%#{connection.quote_string(v.first)}%'"
                   end
-                  sql << ")"
+                  sql << ')'
+                  filters_clauses << sql
                 end if filters and valid?
-                sql
+                clause << (' AND ' + filters_clauses.join(' AND ')) unless filters_clauses.empty?
+                clause
               end
             end

test/fixtures/custom_values.yml +7 -1

               custom_field_id: 2
               customized_id: 1
               id: 7
-              value: "101"
+              value: "125"
             custom_values_005:
               customized_type: Issue
               custom_field_id: 2
               customized_id: 2
               id: 8
               value: ""
+            custom_values_008:
+              customized_type: Issue
+              custom_field_id: 1
+              customized_id: 3
+              id: 11
+              value: "MySQL"
  No newline at end of file

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