Feature #4939 » or-query-patch-for-Redmine-5.1.0.patch
| app/helpers/queries_helper.rb | ||
|---|---|---|
|
group = :label_attachment
|
||
|
elsif [:string, :text, :search].include?(field_options[:type])
|
||
|
group = :label_string
|
||
|
elsif field_options[:group] == 'or_filter'
|
||
|
group = :label_orfilter
|
||
|
end
|
||
|
if group
|
||
|
(grouped[group] ||= []) << [field_options[:name], field]
|
||
| app/models/issue_query.rb | ||
|---|---|---|
|
add_available_filter "any_searchable", :type => :search
|
||
|
add_available_filter "and_any",
|
||
|
:name => l(:label_orfilter_and_any),
|
||
|
:type => :list,
|
||
|
:values => [l(:general_text_Yes)],
|
||
|
:group => 'or_filter'
|
||
|
add_available_filter "or_any",
|
||
|
:name => l(:label_orfilter_or_any),
|
||
|
:type => :list,
|
||
|
:values => [l(:general_text_Yes)],
|
||
|
:group => 'or_filter'
|
||
|
add_available_filter "or_all",
|
||
|
:name => l(:label_orfilter_or_all),
|
||
|
:type => :list,
|
||
|
:values => [l(:general_text_Yes)],
|
||
|
:group => 'or_filter'
|
||
|
Tracker.disabled_core_fields(trackers).each do |field|
|
||
|
delete_available_filter field
|
||
|
end
|
||
| app/models/query.rb | ||
|---|---|---|
|
"!o" => :label_no_open_issues,
|
||
|
"ev" => :label_has_been, # "ev" stands for "ever"
|
||
|
"!ev" => :label_has_never_been,
|
||
|
"cf" => :label_changed_from
|
||
|
"cf" => :label_changed_from,
|
||
|
"match" => :label_match,
|
||
|
"!match" => :label_not_match
|
||
|
}
|
||
|
class_attribute :operators_by_filter_type
|
||
| ... | ... | |
|
:date => [ "=", ">=", "<=", "><", "<t+", ">t+", "><t+", "t+", "nd", "t", "ld", "nw", "w", "lw", "l2w", "nm", "m", "lm", "y", ">t-", "<t-", "><t-", "t-", "!*", "*" ],
|
||
|
:date_past => [ "=", ">=", "<=", "><", ">t-", "<t-", "><t-", "t-", "t", "ld", "w", "lw", "l2w", "m", "lm", "y", "!*", "*" ],
|
||
|
:string => [ "~", "*~", "=", "!~", "!", "^", "$", "!*", "*" ],
|
||
|
:text => [ "~", "*~", "!~", "^", "$", "!*", "*" ],
|
||
|
:text => [ "~", "*~", "!~", "^", "$", "!*", "*", "match", "!match" ],
|
||
|
:search => [ "~", "*~", "!~" ],
|
||
|
:integer => [ "=", ">=", "<=", "><", "!*", "*" ],
|
||
|
:float => [ "=", ">=", "<=", "><", "!*", "*" ],
|
||
| ... | ... | |
|
end
|
||
|
def statement
|
||
|
# filters clauses
|
||
|
filters_clauses = []
|
||
|
filters_clauses=[]
|
||
|
and_clauses=[]
|
||
|
and_any_clauses=[]
|
||
|
or_any_clauses=[]
|
||
|
or_all_clauses=[]
|
||
|
and_any_op = ""
|
||
|
or_any_op = ""
|
||
|
or_all_op = ""
|
||
|
#the AND filter start first
|
||
|
filters_clauses = and_clauses
|
||
|
filters.each_key do |field|
|
||
|
next if field == "subproject_id"
|
||
|
if field == "and_any"
|
||
|
#start the and any part, point filters_clause to and_any_clauses
|
||
|
filters_clauses = and_any_clauses
|
||
|
and_any_op = operator_for(field) == "=" ? " AND " : " AND NOT "
|
||
|
next
|
||
|
elsif field == "or_any"
|
||
|
#start the or any part, point filters_clause to or_any_clauses
|
||
|
filters_clauses = or_any_clauses
|
||
|
or_any_op = operator_for(field) == "=" ? " OR " : " OR NOT "
|
||
|
next
|
||
|
elsif field == "or_all"
|
||
|
#start the or any part, point filters_clause to or_any_clauses
|
||
|
filters_clauses = or_all_clauses
|
||
|
or_all_op = operator_for(field) == "=" ? " OR " : " OR NOT "
|
||
|
next
|
||
|
end
|
||
|
v = values_for(field).clone
|
||
|
next unless v and !v.empty?
|
||
| ... | ... | |
|
filters_clauses << sql_for_custom_field(field, operator, v, $1)
|
||
|
elsif field =~ /^cf_(\d+)\.(.+)$/
|
||
|
filters_clauses << sql_for_custom_field_attribute(field, operator, v, $1, $2)
|
||
|
elsif respond_to?(method = "sql_for_#{field.tr('.', '_')}_field")
|
||
|
elsif respond_to?(method = "sql_for_#{field.gsub('.', '_')}_field")
|
||
|
# specific statement
|
||
|
filters_clauses << send(method, field, operator, v)
|
||
|
else
|
||
| ... | ... | |
|
filters_clauses << c.custom_field.visibility_by_project_condition
|
||
|
end
|
||
|
filters_clauses << project_statement
|
||
|
filters_clauses.reject!(&:blank?)
|
||
|
#now start build the full statement, project filter is allways AND
|
||
|
and_clauses.reject!(&:blank?)
|
||
|
and_statement = and_clauses.any? ? and_clauses.join(" AND ") : nil
|
||
|
# finish the traditional part. Now extended part
|
||
|
# add the and_any first
|
||
|
and_any_clauses.reject!(&:blank?)
|
||
|
and_any_statement = and_any_clauses.any? ? "("+ and_any_clauses.join(" OR ") +")" : nil
|
||
|
full_statement_ext_1 = ["#{and_statement}", "#{and_any_statement}"].reject(&:blank?)
|
||
|
full_statement_ext_1 = full_statement_ext_1.any? ? full_statement_ext_1.join(and_any_op) : nil
|
||
|
# then add the or_all
|
||
|
or_all_clauses.reject!(&:blank?)
|
||
|
or_all_statement = or_all_clauses.any? ? "("+ or_all_clauses.join(" AND ") +")" : nil
|
||
|
full_statement_ext_2 = ["#{full_statement_ext_1}", "#{or_all_statement}"].reject(&:blank?)
|
||
|
full_statement_ext_2 = full_statement_ext_2.any? ? full_statement_ext_2.join(or_all_op) : nil
|
||
|
# then add the or_any
|
||
|
or_any_clauses.reject!(&:blank?)
|
||
|
or_any_statement = or_any_clauses.any? ? "("+ or_any_clauses.join(" OR ") +")" : nil
|
||
|
# full statement without project
|
||
|
full_statement_without_project = ["#{full_statement_ext_2}", "#{or_any_statement}"].reject(&:blank?)
|
||
|
full_statement_without_project = full_statement_without_project.any? ? "("+ full_statement_without_project.join(or_any_op) +")" : nil
|
||
|
# full statement
|
||
|
full_statement = ["#{project_statement}", "#{full_statement_without_project}"].reject(&:blank?)
|
||
|
full_statement = full_statement.any? ? full_statement.join(" AND ") : nil
|
||
|
filters_clauses.any? ? filters_clauses.join(' AND ') : nil
|
||
|
Rails.logger.info "STATEMENT #{full_statement}"
|
||
|
return full_statement
|
||
|
end
|
||
|
# Returns the result count by group or nil if query is not grouped
|
||
| ... | ... | |
|
else
|
||
|
sql = '1=0'
|
||
|
end
|
||
|
when "match"
|
||
|
sql = sql_for_match_operators(field, operator, value, db_table, db_field, is_custom_filter)
|
||
|
when "!match"
|
||
|
sql = sql_for_match_operators(field, operator, value, db_table, db_field, is_custom_filter)
|
||
|
else
|
||
|
raise QueryError, "Unknown query operator #{operator}"
|
||
|
end
|
||
| ... | ... | |
|
return sql
|
||
|
end
|
||
|
def sql_for_match_operators(field, operator, value, db_table, db_field, is_custom_filter=false)
|
||
|
sql = ''
|
||
|
v = "(" + value.first.strip + ")"
|
||
|
match = true
|
||
|
op = ""
|
||
|
term = ""
|
||
|
in_term = false
|
||
|
in_bracket = false
|
||
|
v.chars.each do |c|
|
||
|
if (!in_bracket && "()+~!".include?(c) && in_term ) || (in_bracket && "}".include?(c))
|
||
|
if !term.empty?
|
||
|
sql += "(" + sql_contains("#{db_table}.#{db_field}", term, match) + ")"
|
||
|
end
|
||
|
#reset
|
||
|
op = ""
|
||
|
term = ""
|
||
|
in_term = false
|
||
|
in_bracket = (c == "{")
|
||
|
end
|
||
|
if in_bracket && (!"{}".include? c)
|
||
|
term += c
|
||
|
in_term = true
|
||
|
else
|
||
|
case c
|
||
|
when "{"
|
||
|
in_bracket = true
|
||
|
when "}"
|
||
|
in_bracket = false
|
||
|
when "("
|
||
|
sql += c
|
||
|
when ")"
|
||
|
sql += c
|
||
|
when "+"
|
||
|
sql += " AND " if sql.last != "("
|
||
|
when "~"
|
||
|
sql += " OR " if sql.last != "("
|
||
|
when "!"
|
||
|
sql += " NOT "
|
||
|
else
|
||
|
if c != " "
|
||
|
term += c
|
||
|
in_term = true
|
||
|
end
|
||
|
end
|
||
|
end
|
||
|
end
|
||
|
if operator.include? "!"
|
||
|
sql = " NOT " + sql
|
||
|
end
|
||
|
Rails.logger.info "MATCH EXPRESSION: V=#{value.first}, SQL=#{sql}"
|
||
|
return sql
|
||
|
end
|
||
|
# Returns a SQL LIKE statement with wildcards
|
||
|
#
|
||
|
# valid options:
|
||
| config/locales/de.yml | ||
|---|---|---|
|
label_year: Jahr
|
||
|
label_yesterday: gestern
|
||
|
label_default_query: Standardabfrage
|
||
|
label_orfilter: "ODER Filter"
|
||
|
label_orfilter_and_any: "UND einer der folgenden"
|
||
|
label_orfilter_or_any: "ODER einer der folgenden"
|
||
|
label_orfilter_or_all: "ODER alle folgenden"
|
||
|
mail_body_account_activation_request: "Ein neuer Benutzer (%{value}) hat sich registriert. Sein Konto wartet auf Ihre Genehmigung:"
|
||
|
mail_body_account_information: Ihre Konto-Informationen
|
||
| config/locales/en.yml | ||
|---|---|---|
|
label_default_query: Default query
|
||
|
label_edited: Edited
|
||
|
label_time_by_author: "%{time} by %{author}"
|
||
|
label_orfilter: "OR filters"
|
||
|
label_orfilter_and_any: "AND any following"
|
||
|
label_orfilter_or_any: "OR any following"
|
||
|
label_orfilter_or_all: "OR all following"
|
||
|
label_match: "match"
|
||
|
label_not_match: "not match"
|
||
|
button_login: Login
|
||
|
button_submit: Submit
|
||
| config/locales/ja.yml | ||
|---|---|---|
|
label_parent_revision: 親
|
||
|
label_child_revision: 子
|
||
|
label_gantt_progress_line: イナズマ線
|
||
|
label_orfilter: "ORフィルタ"
|
||
|
label_orfilter_and_any: "上記 かつ (以下のいずれか)"
|
||
|
label_orfilter_or_any: "上記 または (以下のいずれか)"
|
||
|
label_orfilter_or_all: "上記 または (以下の全て)"
|
||
|
label_match: "match"
|
||
|
label_not_match: "not match"
|
||
|
button_login: ログイン
|
||
|
button_submit: 送信
|
||
| test/unit/query_test.rb | ||
|---|---|---|
|
assert_equal [5, 8, 9], issues.collect(&:id).sort
|
||
|
end
|
||
|
def test_filter_on_subject_match
|
||
|
query = IssueQuery.new(:name => '_')
|
||
|
query.filters = {'subject' => {:operator => 'match', :values => ['issue']}}
|
||
|
issues = find_issues_with_query(query)
|
||
|
assert_equal [4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14], issues.collect(&:id).sort
|
||
|
query = IssueQuery.new(:name => '_')
|
||
|
query.filters = {'subject' => {:operator => 'match', :values => ['(~project ~recipe) +!sub']}}
|
||
|
issues = find_issues_with_query(query)
|
||
|
assert_equal [1, 3, 4, 14], issues.collect(&:id).sort
|
||
|
query = IssueQuery.new(:name => '_')
|
||
|
query.filters = {'subject' => {:operator => 'match', :values => ['!(~sub project ~block) +issue']}}
|
||
|
issues = find_issues_with_query(query)
|
||
|
assert_equal [4, 7, 8, 11, 12, 14], issues.collect(&:id).sort
|
||
|
query = IssueQuery.new(:name => '_')
|
||
|
query.filters = {'subject' => {:operator => 'match', :values => ['+{closed ver} ~{locked ver}']}}
|
||
|
issues = find_issues_with_query(query)
|
||
|
assert_equal [11, 12], issues.collect(&:id).sort
|
||
|
end
|
||
|
def test_filter_on_subject_not_match
|
||
|
query = IssueQuery.new(:name => '_')
|
||
|
query.filters = {'subject' => {:operator => '!match', :values => ['issue']}}
|
||
|
issues = find_issues_with_query(query)
|
||
|
assert_equal [1, 2, 3], issues.collect(&:id).sort
|
||
|
query = IssueQuery.new(:name => '_')
|
||
|
query.filters = {'subject' => {:operator => '!match', :values => ['(~project ~recipe) +!sub']}}
|
||
|
issues = find_issues_with_query(query)
|
||
|
assert_equal [2, 5, 6, 7, 8, 9, 10, 11, 12, 13], issues.collect(&:id).sort
|
||
|
query = IssueQuery.new(:name => '_')
|
||
|
query.filters = {'subject' => {:operator => '!match', :values => ['!(~sub project ~block) +issue']}}
|
||
|
issues = find_issues_with_query(query)
|
||
|
assert_equal [1, 2, 3, 5, 6, 9, 10, 13], issues.collect(&:id).sort
|
||
|
query = IssueQuery.new(:name => '_')
|
||
|
query.filters = {'subject' => {:operator => '!match', :values => ['+{closed ver} ~{locked ver}']}}
|
||
|
issues = find_issues_with_query(query)
|
||
|
assert_equal [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 13, 14], issues.collect(&:id).sort
|
||
|
end
|
||
|
def test_filter_on_orfilter_and_any
|
||
|
query = IssueQuery.new(:name => '_')
|
||
|
query.filters = {'project_id' => {:operator => '=', :values => [1]},
|
||
|
'and_any' => {:operator => '=', :values => [1]},
|
||
|
'status_id' => {:operator => '!', :values => [1]},
|
||
|
'assigned_to_id' => {:operator => '=', :values => [3]}}
|
||
|
issues = find_issues_with_query(query)
|
||
|
assert_equal [2, 3, 8, 11, 12], issues.collect(&:id).sort
|
||
|
end
|
||
|
def test_filter_on_orfilter_and_any_not
|
||
|
query = IssueQuery.new(:name => '_')
|
||
|
query.filters = {'project_id' => {:operator => '=', :values => [1]},
|
||
|
'and_any' => {:operator => '!', :values => [1]},
|
||
|
'status_id' => {:operator => '=', :values => [2]},
|
||
|
'author_id' => {:operator => '=', :values => [3]}}
|
||
|
issues = find_issues_with_query(query)
|
||
|
assert_equal [1, 3, 7, 8, 11], issues.collect(&:id).sort
|
||
|
end
|
||
|
def test_filter_on_orfilter_or_any
|
||
|
query = IssueQuery.new(:name => '_')
|
||
|
query.filters = {'status_id' => {:operator => '!', :values => [1]},
|
||
|
'or_any' => {:operator => '=', :values => [1]},
|
||
|
'project_id' => {:operator => '=', :values => [3]},
|
||
|
'assigned_to_id' => {:operator => '=', :values => [2]}}
|
||
|
issues = find_issues_with_query(query)
|
||
|
assert_equal [2, 4, 5, 8, 11, 12, 13, 14], issues.collect(&:id).sort
|
||
|
end
|
||
|
def test_filter_on_orfilter_or_any_not
|
||
|
query = IssueQuery.new(:name => '_')
|
||
|
query.filters = {'status_id' => {:operator => '!', :values => [1]},
|
||
|
'or_any' => {:operator => '!', :values => [1]},
|
||
|
'project_id' => {:operator => '=', :values => [3]},
|
||
|
'assigned_to_id' => {:operator => '!', :values => [2]}}
|
||
|
issues = find_issues_with_query(query)
|
||
|
assert_equal [2, 4, 8, 11, 12], issues.collect(&:id).sort
|
||
|
end
|
||
|
def test_filter_on_orfilter_or_all
|
||
|
query = IssueQuery.new(:name => '_')
|
||
|
query.filters = {'project_id' => {:operator => '=', :values => [3]},
|
||
|
'or_all' => {:operator => '=', :values => [1]},
|
||
|
'author_id' => {:operator => '=', :values => [2]},
|
||
|
'assigned_to_id' => {:operator => '=', :values => [2]}}
|
||
|
issues = find_issues_with_query(query)
|
||
|
assert_equal [4, 5, 13, 14], issues.collect(&:id).sort
|
||
|
end
|
||
|
def test_filter_on_orfilter_or_all_not
|
||
|
query = IssueQuery.new(:name => '_')
|
||
|
query.filters = {'project_id' => {:operator => '=', :values => [3]},
|
||
|
'or_all' => {:operator => '!', :values => [1]},
|
||
|
'author_id' => {:operator => '=', :values => [2]},
|
||
|
'assigned_to_id' => {:operator => '=', :values => [2]}}
|
||
|
issues = find_issues_with_query(query)
|
||
|
assert_equal [2, 3, 5, 12, 13, 14], issues.collect(&:id).sort
|
||
|
end
|
||
|
def test_statement_should_be_nil_with_no_filters
|
||
|
q = IssueQuery.new(:name => '_')
|
||
|
q.filters = {}
|
||
- « Previous
- 1
- …
- 13
- 14
- 15
- Next »