Project

General

Profile

Feature #4939 » Redmine5.0.1-or-query-patch.patch

Tilman Klaeger, 2022-05-27 20:17

View differences:

app/helpers/queries_helper.rb
43 43
        group = :label_time_tracking
44 44
      elsif %w(attachment attachment_description).include?(field)
45 45
        group = :label_attachment
46
      elsif field_options[:group] == 'or_filter'
47
        group = :label_orfilter
46 48
      end
47 49
      if group
48 50
        (grouped[group] ||= []) << [field_options[:name], field]
app/models/issue_query.rb
269 269
    add_available_filter "issue_id", :type => :integer, :label => :label_issue
270 270

  
271 271
    Tracker.disabled_core_fields(trackers).each do |field|
272
    add_available_filter "and_any",
273
        :name => l(:label_orfilter_and_any),
274
        :type => :list,
275
        :values => [l(:general_text_Yes)],
276
        :group => 'or_filter'
277
    add_available_filter "or_any",
278
        :name => l(:label_orfilter_or_any),
279
        :type => :list,
280
        :values => [l(:general_text_Yes)],
281
        :group => 'or_filter'
282
    add_available_filter "or_all",
283
        :name => l(:label_orfilter_or_all),
284
        :type => :list,
285
        :values => [l(:general_text_Yes)],
286
        :group => 'or_filter'
287

  
272 288
      delete_available_filter field
273 289
    end
274 290
  end
app/models/query.rb
307 307
    "!p"  => :label_no_issues_in_project,
308 308
    "*o"  => :label_any_open_issues,
309 309
    "!o"  => :label_no_open_issues,
310
    "match"  => :label_match,
311
    "!match" => :label_not_match
310 312
  }
311 313

  
312 314
  class_attribute :operators_by_filter_type
......
318 320
    :date => [ "=", ">=", "<=", "><", "<t+", ">t+", "><t+", "t+", "nd", "t", "ld", "nw", "w", "lw", "l2w", "nm", "m", "lm", "y", ">t-", "<t-", "><t-", "t-", "!*", "*" ],
319 321
    :date_past => [ "=", ">=", "<=", "><", ">t-", "<t-", "><t-", "t-", "t", "ld", "w", "lw", "l2w", "m", "lm", "y", "!*", "*" ],
320 322
    :string => [ "~", "=", "!~", "!", "^", "$", "!*", "*" ],
321
    :text => [  "~", "!~", "^", "$", "!*", "*" ],
323
    :text => [  "~", "!~", "^", "$", "!*", "*", "match", "!match" ],
322 324
    :integer => [ "=", ">=", "<=", "><", "!*", "*" ],
323 325
    :float => [ "=", ">=", "<=", "><", "!*", "*" ],
324 326
    :relation => ["=", "!", "=p", "=!p", "!p", "*o", "!o", "!*", "*"],
......
959 961
  end
960 962

  
961 963
  def statement
962
    # filters clauses
963
    filters_clauses = []
964
    filters_clauses=[]
965
    and_clauses=[]
966
    and_any_clauses=[]
967
    or_any_clauses=[]
968
    or_all_clauses=[]
969
    and_any_op = ""
970
    or_any_op = ""
971
    or_all_op = ""
972

  
973
    #the AND filter start first
974
    filters_clauses = and_clauses
975

  
964 976
    filters.each_key do |field|
965 977
      next if field == "subproject_id"
978
      if field == "and_any"
979
         #start the and any part, point filters_clause to and_any_clauses
980
         filters_clauses = and_any_clauses
981
         and_any_op = operator_for(field) == "=" ? " AND " : " AND NOT "
982
         next
983
      elsif field == "or_any"
984
         #start the or any part, point filters_clause to or_any_clauses
985
         filters_clauses = or_any_clauses
986
         or_any_op = operator_for(field) == "=" ? " OR " : " OR NOT "
987
         next
988
      elsif  field == "or_all"
989
         #start the or any part, point filters_clause to or_any_clauses
990
         filters_clauses = or_all_clauses
991
         or_all_op = operator_for(field) == "=" ? " OR " : " OR NOT "
992
         next
993
      end
966 994

  
967 995
      v = values_for(field).clone
968 996
      next unless v and !v.empty?
......
997 1025
        filters_clauses << sql_for_custom_field(field, operator, v, $1)
998 1026
      elsif field =~ /^cf_(\d+)\.(.+)$/
999 1027
        filters_clauses << sql_for_custom_field_attribute(field, operator, v, $1, $2)
1000
      elsif respond_to?(method = "sql_for_#{field.tr('.', '_')}_field")
1028
      elsif respond_to?(method = "sql_for_#{field.gsub('.', '_')}_field")
1001 1029
        # specific statement
1002 1030
        filters_clauses << send(method, field, operator, v)
1003 1031
      else
......
1011 1039
      filters_clauses << c.custom_field.visibility_by_project_condition
1012 1040
    end
1013 1041

  
1014
    filters_clauses << project_statement
1015
    filters_clauses.reject!(&:blank?)
1042
    #now start build the full statement, project filter is allways AND
1043
    and_clauses.reject!(&:blank?)
1044
    and_statement = and_clauses.any? ? and_clauses.join(" AND ") : nil
1045

  
1046
    all_and_statement = ["#{project_statement}", "#{and_statement}"].reject(&:blank?)
1047
    all_and_statement = all_and_statement.any? ? all_and_statement.join(" AND ") : nil
1048

  
1049

  
1050
    # finish the traditional part. Now extended part
1051
    # add the and_any first
1052
    and_any_clauses.reject!(&:blank?)
1053
    and_any_statement = and_any_clauses.any? ? "("+ and_any_clauses.join(" OR ") +")" : nil
1054

  
1055
    full_statement_ext_1 = ["#{all_and_statement}", "#{and_any_statement}"].reject(&:blank?)
1056
    full_statement_ext_1 = full_statement_ext_1.any? ? full_statement_ext_1.join(and_any_op) : nil
1057

  
1058
    # then add the or_all
1059
    or_all_clauses.reject!(&:blank?)
1060
    or_all_statement = or_all_clauses.any? ? "("+ or_all_clauses.join(" AND ") +")" : nil
1061

  
1062
    full_statement_ext_2 = ["#{full_statement_ext_1}", "#{or_all_statement}"].reject(&:blank?)
1063
    full_statement_ext_2 = full_statement_ext_2.any? ? full_statement_ext_2.join(or_all_op) : nil
1064

  
1065
    # then add the or_any
1066
    or_any_clauses.reject!(&:blank?)
1067
    or_any_statement = or_any_clauses.any? ? "("+ or_any_clauses.join(" OR ") +")" : nil
1016 1068

  
1017
    filters_clauses.any? ? filters_clauses.join(' AND ') : nil
1069
    full_statement = ["#{full_statement_ext_2}", "#{or_any_statement}"].reject(&:blank?)
1070
    full_statement = full_statement.any? ? full_statement.join(or_any_op) : nil
1071

  
1072
    Rails.logger.info "STATEMENT #{full_statement}"
1073

  
1074
    return full_statement
1018 1075
  end
1019 1076

  
1020 1077
  # Returns the result count by group or nil if query is not grouped
......
1427 1484
      sql = sql_contains("#{db_table}.#{db_field}", value.first, :starts_with => true)
1428 1485
    when "$"
1429 1486
      sql = sql_contains("#{db_table}.#{db_field}", value.first, :ends_with => true)
1487
    when "match"
1488
      sql = sql_for_match_operators(field, operator, value, db_table, db_field, is_custom_filter)
1489
    when "!match"
1490
      sql = sql_for_match_operators(field, operator, value, db_table, db_field, is_custom_filter)
1430 1491
    else
1431 1492
      raise QueryError, "Unknown query operator #{operator}"
1432 1493
    end
......
1434 1495
    return sql
1435 1496
  end
1436 1497

  
1498
  def sql_for_match_operators(field, operator, value, db_table, db_field, is_custom_filter=false)
1499
    sql = ''
1500
    v = "(" + value.first.strip + ")"
1501

  
1502
    match = true
1503
    op = ""
1504
    term = ""
1505
    in_term = false
1506

  
1507
    in_bracket = false
1508

  
1509
    v.chars.each do |c|
1510

  
1511
      if (!in_bracket && "()+~!".include?(c) && in_term  ) || (in_bracket && "}".include?(c))
1512
        if !term.empty?
1513
          sql += "(" + sql_contains("#{db_table}.#{db_field}", term, match) + ")"
1514
        end
1515
        #reset
1516
        op = ""
1517
        term = ""
1518
        in_term = false
1519

  
1520
        in_bracket = (c == "{")
1521
      end
1522

  
1523
      if in_bracket && (!"{}".include? c)
1524
        term += c
1525
        in_term = true
1526
      else
1527

  
1528
        case c
1529
        when "{"
1530
          in_bracket = true
1531
        when "}"
1532
          in_bracket = false
1533
        when "("
1534
          sql += c
1535
        when ")"
1536
          sql += c
1537
        when "+"
1538
          sql += " AND " if sql.last != "("
1539
        when "~"
1540
          sql += " OR " if sql.last != "("
1541
        when "!"
1542
          sql += " NOT "
1543
        else
1544
          if c != " "
1545
            term += c
1546
            in_term = true
1547
          end
1548
        end
1549

  
1550
      end
1551
    end
1552

  
1553
    if operator.include? "!"
1554
      sql = " NOT " + sql
1555
    end
1556

  
1557
    Rails.logger.info "MATCH EXPRESSION: V=#{value.first}, SQL=#{sql}"
1558
    return sql
1559
  end
1560

  
1437 1561
  # Returns a SQL LIKE statement with wildcards
1438 1562
  def sql_contains(db_field, value, options={})
1439 1563
    options = {} unless options.is_a?(Hash)
config/locales/de.yml
843 843
  label_year: Jahr
844 844
  label_yesterday: gestern
845 845
  label_default_query: Standardabfrage
846
  label_orfilter: "ODER Filter"
847
  label_orfilter_and_any: "UND einer der folgenden"
848
  label_orfilter_or_any: "ODER einer der folgenden"
849
  label_orfilter_or_all: "ODER alle folgenden"
846 850

  
847 851
  mail_body_account_activation_request: "Ein neuer Benutzer (%{value}) hat sich registriert. Sein Konto wartet auf Ihre Genehmigung:"
848 852
  mail_body_account_information: Ihre Konto-Informationen
config/locales/en.yml
1124 1124
  label_my_bookmarks: My bookmarks
1125 1125
  label_assign_to_me: Assign to me
1126 1126
  label_default_query: Default query
1127
  label_orfilter: "OR filters"
1128
  label_orfilter_and_any: "AND any following"
1129
  label_orfilter_or_any: "OR any following"
1130
  label_orfilter_or_all: "OR all following"
1131
  label_match: "match"
1132
  label_not_match: "not match"
1127 1133

  
1128 1134
  button_login: Login
1129 1135
  button_submit: Submit
config/locales/ja.yml
819 819
  label_parent_revision: 親
820 820
  label_child_revision: 子
821 821
  label_gantt_progress_line: イナズマ線
822
  label_orfilter: "ORフィルタ"
823
  label_orfilter_and_any: "上記 かつ (以下のいずれか)"
824
  label_orfilter_or_any: "上記 または (以下のいずれか)"
825
  label_orfilter_or_all: "上記 または (以下の全て)"
826
  label_match: "match"
827
  label_not_match: "not match"
822 828

  
823 829
  button_login: ログイン
824 830
  button_submit: 送信
test/unit/query_test.rb
1598 1598
    assert_equal [5, 8, 9], issues.collect(&:id).sort
1599 1599
  end
1600 1600

  
1601
  def test_filter_on_subject_match
1602
    query = IssueQuery.new(:name => '_')
1603
    query.filters = {'subject' => {:operator => 'match', :values => ['issue']}}
1604
    issues = find_issues_with_query(query)
1605
    assert_equal [4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14], issues.collect(&:id).sort
1606

  
1607
    query = IssueQuery.new(:name => '_')
1608
    query.filters = {'subject' => {:operator => 'match', :values => ['(~project ~recipe) +!sub']}}
1609
    issues = find_issues_with_query(query)
1610
    assert_equal [1, 3, 4, 14], issues.collect(&:id).sort
1611

  
1612
    query = IssueQuery.new(:name => '_')
1613
    query.filters = {'subject' => {:operator => 'match', :values => ['!(~sub project ~block) +issue']}}
1614
    issues = find_issues_with_query(query)
1615
    assert_equal [4, 7, 8, 11, 12, 14], issues.collect(&:id).sort
1616

  
1617
    query = IssueQuery.new(:name => '_')
1618
    query.filters = {'subject' => {:operator => 'match', :values => ['+{closed ver} ~{locked ver}']}}
1619
    issues = find_issues_with_query(query)
1620
    assert_equal [11, 12], issues.collect(&:id).sort
1621
  end
1622

  
1623
  def test_filter_on_subject_not_match
1624
    query = IssueQuery.new(:name => '_')
1625
    query.filters = {'subject' => {:operator => '!match', :values => ['issue']}}
1626
    issues = find_issues_with_query(query)
1627
    assert_equal [1, 2, 3], issues.collect(&:id).sort
1628

  
1629
    query = IssueQuery.new(:name => '_')
1630
    query.filters = {'subject' => {:operator => '!match', :values => ['(~project ~recipe) +!sub']}}
1631
    issues = find_issues_with_query(query)
1632
    assert_equal [2, 5, 6, 7, 8, 9, 10, 11, 12, 13], issues.collect(&:id).sort
1633

  
1634
    query = IssueQuery.new(:name => '_')
1635
    query.filters = {'subject' => {:operator => '!match', :values => ['!(~sub project ~block) +issue']}}
1636
    issues = find_issues_with_query(query)
1637
    assert_equal [1, 2, 3, 5, 6, 9, 10, 13], issues.collect(&:id).sort
1638

  
1639
    query = IssueQuery.new(:name => '_')
1640
    query.filters = {'subject' => {:operator => '!match', :values => ['+{closed ver} ~{locked ver}']}}
1641
    issues = find_issues_with_query(query)
1642
    assert_equal [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 13, 14], issues.collect(&:id).sort
1643
  end
1644

  
1645
  def test_filter_on_orfilter_and_any
1646
    query = IssueQuery.new(:name => '_')
1647
    query.filters = {'project_id' => {:operator => '=', :values => [1]},
1648
                     'and_any' => {:operator => '=', :values => [1]},
1649
                     'status_id' => {:operator => '!', :values => [1]},
1650
                     'assigned_to_id' => {:operator => '=', :values => [3]}}
1651
    issues = find_issues_with_query(query)
1652
    assert_equal [2, 3, 8, 11, 12], issues.collect(&:id).sort
1653
  end
1654

  
1655
  def test_filter_on_orfilter_and_any_not
1656
    query = IssueQuery.new(:name => '_')
1657
    query.filters = {'project_id' => {:operator => '=', :values => [1]},
1658
                     'and_any' => {:operator => '!', :values => [1]},
1659
                     'status_id' => {:operator => '=', :values => [2]},
1660
                     'author_id' => {:operator => '=', :values => [3]}}
1661
    issues = find_issues_with_query(query)
1662
    assert_equal [1, 3, 7, 8, 11], issues.collect(&:id).sort
1663
  end
1664

  
1665
  def test_filter_on_orfilter_or_any
1666
    query = IssueQuery.new(:name => '_')
1667
    query.filters = {'status_id' => {:operator => '!', :values => [1]},
1668
                     'or_any' => {:operator => '=', :values => [1]},
1669
                     'project_id' => {:operator => '=', :values => [3]},
1670
                     'assigned_to_id' => {:operator => '=', :values => [2]}}
1671
    issues = find_issues_with_query(query)
1672
    assert_equal [2, 4, 5, 8, 11, 12, 13, 14], issues.collect(&:id).sort
1673
  end
1674

  
1675
  def test_filter_on_orfilter_or_any_not
1676
    query = IssueQuery.new(:name => '_')
1677
    query.filters = {'status_id' => {:operator => '!', :values => [1]},
1678
                     'or_any' => {:operator => '!', :values => [1]},
1679
                     'project_id' => {:operator => '=', :values => [3]},
1680
                     'assigned_to_id' => {:operator => '!', :values => [2]}}
1681
    issues = find_issues_with_query(query)
1682
    assert_equal [2, 4, 8, 11, 12], issues.collect(&:id).sort
1683
  end
1684

  
1685
  def test_filter_on_orfilter_or_all
1686
    query = IssueQuery.new(:name => '_')
1687
    query.filters = {'project_id' => {:operator => '=', :values => [3]},
1688
                     'or_all' => {:operator => '=', :values => [1]},
1689
                     'author_id' => {:operator => '=', :values => [2]},
1690
                     'assigned_to_id' => {:operator => '=', :values => [2]}}
1691
    issues = find_issues_with_query(query)
1692
    assert_equal [4, 5, 13, 14], issues.collect(&:id).sort
1693
  end
1694

  
1695
  def test_filter_on_orfilter_or_all_not
1696
    query = IssueQuery.new(:name => '_')
1697
    query.filters = {'project_id' => {:operator => '=', :values => [3]},
1698
                     'or_all' => {:operator => '!', :values => [1]},
1699
                     'author_id' => {:operator => '=', :values => [2]},
1700
                     'assigned_to_id' => {:operator => '=', :values => [2]}}
1701
    issues = find_issues_with_query(query)
1702
    assert_equal [2, 3, 5, 12, 13, 14], issues.collect(&:id).sort
1703
  end
1704

  
1601 1705
  def test_statement_should_be_nil_with_no_filters
1602 1706
    q = IssueQuery.new(:name => '_')
1603 1707
    q.filters = {}
(14-14/15)