Closed15

Redmineにすべてのテキストフィールドが対象のフィルタを実装したい

前田剛前田剛

Redmineのチケットのフィルタに、すべてのテキスト型フィールドに対して検索を行うフィルタを追加したい。

チケットを検索するとき、特定のキーワードがいずれかに含まれるものを探したいことは多い。しかし現状ではチケットのフィルタでは「Subject」「Description」「Notes」などいずれかのフィルタを指定してチケット一覧を絞り込むことはできるが、いずれかのフィールドに特定のキーワードが含まれるチケットを絞り込んで表示することはできない。

たとえば、「Subject」か「Description」か「Notes」のいずれかに「Redmine」というキーワードを含むチケットを一覧表示するということはチケット一覧画面では表示できない。

このように、フィールドを特定せずに、とにかくどこかのフィールドに指定したキーワードを含むチケットを探すフィルタを実装したい。

前田剛前田剛

画面右上の検索窓で使われている Issue#search_result_ranks_and_ids を使うと、特定のキーワードを含むチケットを探すことができる。標準フィールドだけではなくカスタムフィールドも対象になる。これを使うと簡単そう。

また、Issue#search_result_ranks_and_ids を使うことで、全文検索プラグイン を使用しているときには高速に検索できることが期待できる。

Issue.search_result_ranks_and_ids('recipe')
# => [[1679649643, 1], [1153336047, 3], [1153335861, 2]]

使い方:

# Searches the model for the given tokens and user visibility.
# The projects argument can be either nil (will search all projects), a project or an array of projects.
# Returns an array that contains the rank and id of all results.
# In current implementation, the rank is the record timestamp converted as an integer.
#
# Valid options:
# * :titles_only - searches tokens in the first searchable column only
# * :all_words - searches results that match all token
# * :
# * :limit - maximum number of results to return
前田剛前田剛

フィルタの名称を "Keyword" とし、チケット一覧の「フィルタ」欄に表示されるようにした。単にフィルタ欄に表示されるだけで動作はしない。

--- a/app/models/issue_query.rb
+++ b/app/models/issue_query.rb
@@ -70,7 +70,8 @@ class IssueQuery < Query
     QueryColumn.new(:relations, :caption => :label_related_issues),
     QueryColumn.new(:attachments, :caption => :label_attachment_plural),
     QueryColumn.new(:description, :inline => false),
-    QueryColumn.new(:last_notes, :caption => :label_last_notes, :inline => false)
+    QueryColumn.new(:last_notes, :caption => :label_last_notes, :inline => false),
+    QueryColumn.new(:keyword, :caption => 'Keyword')
   ]
 
   has_many :projects, foreign_key: 'default_issue_query_id', dependent: :nullify, inverse_of: 'default_issue_query'
@@ -200,6 +201,7 @@ class IssueQuery < Query
     add_available_filter "subject", :type => :text
     add_available_filter "description", :type => :text
     add_available_filter "notes", :type => :text
+    add_available_filter "keyword", :type => :keyword
     add_available_filter "created_on", :type => :date_past
     add_available_filter "updated_on", :type => :date_past
     add_available_filter "closed_on", :type => :date_past
@@ -828,4 +830,8 @@ class IssueQuery < Query
 
     joins.any? ? joins.join(' ') : nil
   end
+
+  def sql_for_keyword_field(field, operator, value, options={})
+    ''
+  end
 end
diff --git a/app/models/query.rb b/app/models/query.rb
index 473ea4d86..d626eafc5 100644
--- a/app/models/query.rb
+++ b/app/models/query.rb
@@ -325,6 +325,7 @@ class Query < ActiveRecord::Base
     :date_past => [ "=", ">=", "<=", "><", ">t-", "<t-", "><t-", "t-", "t", "ld", "w", "lw", "l2w", "m", "lm", "y", "!*", "*" ],
     :string => [ "~", "=", "!~", "!", "^", "$", "!*", "*" ],
     :text => [  "~", "!~", "^", "$", "!*", "*" ],
+    :keyword => [ "=" ],
     :integer => [ "=", ">=", "<=", "><", "!*", "*" ],
     :float => [ "=", ">=", "<=", "><", "!*", "*" ],
     :relation => ["=", "!", "=p", "=!p", "!p", "*o", "!o", "!*", "*"],
diff --git a/config/locales/en.yml b/config/locales/en.yml
index 2e1378ff7..fa5464d11 100644
--- a/config/locales/en.yml
+++ b/config/locales/en.yml
@@ -417,6 +417,7 @@ en:
   field_default_issue_query: Default issue query
   field_default_project_query: Default project query
   field_default_time_entry_activity: Default spent time activity
+  field_keyword: Keyword
 
   setting_app_title: Application title
   setting_welcome_text: Welcome text
diff --git a/public/javascripts/application.js b/public/javascripts/application.js
index 7b2e7725e..0ca636319 100644
--- a/public/javascripts/application.js
+++ b/public/javascripts/application.js
@@ -220,6 +220,7 @@ function buildFilterRow(field, operator, values) {
     break;
   case "string":
   case "text":
+  case "keyword":
     tr.find('td.values').append(
       '<span style="display:none;"><input type="text" name="v['+field+'][]" id="values_'+fieldId+'" size="30" class="value" /></span>'
     );
前田剛前田剛

sql_for_keyword_field メソッドの仮実装。これで "Keyword" フィルタが一応それらしい動作をするようになる。

  def sql_for_keyword_field(field, operator, value, options={})
    return unless operator == '='

    ids = Issue.search_result_ranks_and_ids(value).map(&:last)
    if ids.present?
      "#{Issue.table_name}.id IN (#{ids.join(",")})"
    else
      '1=0'
    end
  end
前田剛前田剛

よく考えたら Keyword フィルタの動作は完全一致ではなく部分一致なので、オペレータは "is" ではなく "contains" であるべき。また、検索ボックス同様に、スペース区切りで複数のキーワードを指定した場合はOR検索が行われるべき。

上記2点の修正をした。

  def sql_for_keyword_field(field, operator, value, options={})
    tokens = Redmine::Search::Tokenizer.new(value.first).tokens
    ids = Issue.search_result_ranks_and_ids(tokens).map(&:last)
    if ids.present?
      sw = operator == "!~" ? 'NOT' : ''
      "#{Issue.table_name}.id #{sw} IN (#{ids.join(",")})"
    else
      '1=0'
    end
  end
前田剛前田剛

現在のパッチ。

diff --git a/app/models/issue_query.rb b/app/models/issue_query.rb
index 9f541cc54..ebfc37029 100644
--- a/app/models/issue_query.rb
+++ b/app/models/issue_query.rb
@@ -70,7 +70,8 @@ class IssueQuery < Query
     QueryColumn.new(:relations, :caption => :label_related_issues),
     QueryColumn.new(:attachments, :caption => :label_attachment_plural),
     QueryColumn.new(:description, :inline => false),
-    QueryColumn.new(:last_notes, :caption => :label_last_notes, :inline => false)
+    QueryColumn.new(:last_notes, :caption => :label_last_notes, :inline => false),
+    QueryColumn.new(:keyword, :caption => 'Keyword')
   ]
 
   has_many :projects, foreign_key: 'default_issue_query_id', dependent: :nullify, inverse_of: 'default_issue_query'
@@ -200,6 +201,7 @@ class IssueQuery < Query
     add_available_filter "subject", :type => :text
     add_available_filter "description", :type => :text
     add_available_filter "notes", :type => :text
+    add_available_filter "keyword", :type => :keyword
     add_available_filter "created_on", :type => :date_past
     add_available_filter "updated_on", :type => :date_past
     add_available_filter "closed_on", :type => :date_past
@@ -828,4 +830,15 @@ class IssueQuery < Query
 
     joins.any? ? joins.join(' ') : nil
   end
+
+  def sql_for_keyword_field(field, operator, value, options={})
+    tokens = Redmine::Search::Tokenizer.new(value.first).tokens
+    ids = Issue.search_result_ranks_and_ids(tokens).map(&:last)
+    if ids.present?
+      sw = operator == "!~" ? 'NOT' : ''
+      "#{Issue.table_name}.id #{sw} IN (#{ids.join(",")})"
+    else
+      '1=0'
+    end
+  end
 end
diff --git a/app/models/query.rb b/app/models/query.rb
index 473ea4d86..8080849f9 100644
--- a/app/models/query.rb
+++ b/app/models/query.rb
@@ -325,6 +325,7 @@ class Query < ActiveRecord::Base
     :date_past => [ "=", ">=", "<=", "><", ">t-", "<t-", "><t-", "t-", "t", "ld", "w", "lw", "l2w", "m", "lm", "y", "!*", "*" ],
     :string => [ "~", "=", "!~", "!", "^", "$", "!*", "*" ],
     :text => [  "~", "!~", "^", "$", "!*", "*" ],
+    :keyword => [ "~", "!~" ],
     :integer => [ "=", ">=", "<=", "><", "!*", "*" ],
     :float => [ "=", ">=", "<=", "><", "!*", "*" ],
     :relation => ["=", "!", "=p", "=!p", "!p", "*o", "!o", "!*", "*"],
diff --git a/config/locales/en.yml b/config/locales/en.yml
index 2e1378ff7..fa5464d11 100644
--- a/config/locales/en.yml
+++ b/config/locales/en.yml
@@ -417,6 +417,7 @@ en:
   field_default_issue_query: Default issue query
   field_default_project_query: Default project query
   field_default_time_entry_activity: Default spent time activity
+  field_keyword: Keyword
 
   setting_app_title: Application title
   setting_welcome_text: Welcome text
diff --git a/public/javascripts/application.js b/public/javascripts/application.js
index 7b2e7725e..0ca636319 100644
--- a/public/javascripts/application.js
+++ b/public/javascripts/application.js
@@ -220,6 +220,7 @@ function buildFilterRow(field, operator, values) {
     break;
   case "string":
   case "text":
+  case "keyword":
     tr.find('td.values').append(
       '<span style="display:none;"><input type="text" name="v['+field+'][]" id="values_'+fieldId+'" size="30" class="value" /></span>'
     );
前田剛前田剛

TODO:

  • フィルタの名前を "Keyword" から "Text in the issue" か何かに変える。フィルタの画面で "Keyword contains" という表示の後でキーワードを指定するのはおかしいので
  • Issue.search_result_ranks_and_ids が常に全プロジェクトを検索しているので、現在のプロジェクトとサブプロジェクトのみを検索するようにする
前田剛前田剛

検索対象のプロジェクトを Project#self_and_descendants で限定した。

  def sql_for_keyword_field(field, operator, value, options={})
    tokens = Redmine::Search::Tokenizer.new(value.first).tokens
    projects = project&.self_and_descendants
    ids = Issue.search_result_ranks_and_ids(tokens, User.current, projects).map(&:last)
    if ids.present?
      sw = operator == "!~" ? 'NOT' : ''
      "#{Issue.table_name}.id #{sw} IN (#{ids.join(",")})"
    else
      '1=0'
    end
  end
前田剛前田剛

フィルタ名称を Keyword から Any text に変更した。

前田剛前田剛

現在のパッチ:

diff --git a/app/models/issue_query.rb b/app/models/issue_query.rb
index 9f541cc54..9bf7c92c0 100644
--- a/app/models/issue_query.rb
+++ b/app/models/issue_query.rb
@@ -70,7 +70,8 @@ class IssueQuery < Query
     QueryColumn.new(:relations, :caption => :label_related_issues),
     QueryColumn.new(:attachments, :caption => :label_attachment_plural),
     QueryColumn.new(:description, :inline => false),
-    QueryColumn.new(:last_notes, :caption => :label_last_notes, :inline => false)
+    QueryColumn.new(:last_notes, :caption => :label_last_notes, :inline => false),
+    QueryColumn.new(:any_text)
   ]
 
   has_many :projects, foreign_key: 'default_issue_query_id', dependent: :nullify, inverse_of: 'default_issue_query'
@@ -200,6 +201,7 @@ class IssueQuery < Query
     add_available_filter "subject", :type => :text
     add_available_filter "description", :type => :text
     add_available_filter "notes", :type => :text
+    add_available_filter "any_text", :type => :any_text
     add_available_filter "created_on", :type => :date_past
     add_available_filter "updated_on", :type => :date_past
     add_available_filter "closed_on", :type => :date_past
@@ -828,4 +830,16 @@ class IssueQuery < Query
 
     joins.any? ? joins.join(' ') : nil
   end
+
+  def sql_for_any_text_field(field, operator, value, options={})
+    tokens = Redmine::Search::Tokenizer.new(value.first).tokens
+    projects = project&.self_and_descendants
+    ids = Issue.search_result_ranks_and_ids(tokens, User.current, projects).map(&:last)
+    if ids.present?
+      sw = operator == "!~" ? 'NOT' : ''
+      "#{Issue.table_name}.id #{sw} IN (#{ids.join(",")})"
+    else
+      '1=0'
+    end
+  end
 end
diff --git a/app/models/query.rb b/app/models/query.rb
index 473ea4d86..50ecb35e6 100644
--- a/app/models/query.rb
+++ b/app/models/query.rb
@@ -325,6 +325,7 @@ class Query < ActiveRecord::Base
     :date_past => [ "=", ">=", "<=", "><", ">t-", "<t-", "><t-", "t-", "t", "ld", "w", "lw", "l2w", "m", "lm", "y", "!*", "*" ],
     :string => [ "~", "=", "!~", "!", "^", "$", "!*", "*" ],
     :text => [  "~", "!~", "^", "$", "!*", "*" ],
+    :any_text => [ "~", "!~" ],
     :integer => [ "=", ">=", "<=", "><", "!*", "*" ],
     :float => [ "=", ">=", "<=", "><", "!*", "*" ],
     :relation => ["=", "!", "=p", "=!p", "!p", "*o", "!o", "!*", "*"],
diff --git a/config/locales/en.yml b/config/locales/en.yml
index 2e1378ff7..24dc7a7e8 100644
--- a/config/locales/en.yml
+++ b/config/locales/en.yml
@@ -417,6 +417,7 @@ en:
   field_default_issue_query: Default issue query
   field_default_project_query: Default project query
   field_default_time_entry_activity: Default spent time activity
+  field_any_text: Any text
 
   setting_app_title: Application title
   setting_welcome_text: Welcome text
diff --git a/public/javascripts/application.js b/public/javascripts/application.js
index 7b2e7725e..1ef2b2c92 100644
--- a/public/javascripts/application.js
+++ b/public/javascripts/application.js
@@ -220,6 +220,7 @@ function buildFilterRow(field, operator, values) {
     break;
   case "string":
   case "text":
+  case "any_text":
     tr.find('td.values').append(
       '<span style="display:none;"><input type="text" name="v['+field+'][]" id="values_'+fieldId+'" size="30" class="value" /></span>'
     );
前田剛前田剛

検索結果を得るのに現在は Issue.search_result_ranks_and_ids を呼び出しているが、 Redmine::Search::Fetcher.new を呼ぶようにした。

なお、パラメータとして cache: true を渡すと検索結果のキャッシュが行われるようになるが、現在のコードでは使用していない。キャッシュをクリアする適切なタイミングがないため。

  def sql_for_any_text_field(field, operator, value)
    question = value.first
    projects = project&.self_and_descendants
    fetcher = Redmine::Search::Fetcher.new(
      question, User.current, ['issue'], projects, attachments: '0'
    )
    ids = fetcher.result_ids.map(&:last)
    if ids.present?
      sw = operator == '!~' ? 'NOT' : ''
      "#{Issue.table_name}.id #{sw} IN (#{ids.join(",")})"
    else
      '1=0'
    end
  end
前田剛前田剛

フィルタの名称を "Any text" から "Any searchable text" に変更。

フィルタであるのにもかかわらず、"Searchable" にチェックされたカスタムクエリのみが対象で "Used as a filter" だけがチェックされているカスタムクエリが対象にならないのは分かりにくいので、フィルタの名称に searchable を含めた。

前田剛前田剛
diff --git a/app/models/issue_query.rb b/app/models/issue_query.rb
index 9f541cc54..3cb28ae16 100644
--- a/app/models/issue_query.rb
+++ b/app/models/issue_query.rb
@@ -200,6 +200,7 @@ class IssueQuery < Query
     add_available_filter "subject", :type => :text
     add_available_filter "description", :type => :text
     add_available_filter "notes", :type => :text
+    add_available_filter "any_searchable", :type => :search
     add_available_filter "created_on", :type => :date_past
     add_available_filter "updated_on", :type => :date_past
     add_available_filter "closed_on", :type => :date_past
@@ -828,4 +829,19 @@ class IssueQuery < Query
 
     joins.any? ? joins.join(' ') : nil
   end
+
+  def sql_for_any_searchable_field(field, operator, value)
+    question = value.first
+    projects = project&.self_and_descendants
+    fetcher = Redmine::Search::Fetcher.new(
+      question, User.current, ['issue'], projects, attachments: '0'
+    )
+    ids = fetcher.result_ids.map(&:last)
+    if ids.present?
+      sw = operator == '!~' ? 'NOT' : ''
+      "#{Issue.table_name}.id #{sw} IN (#{ids.join(",")})"
+    else
+      '1=0'
+    end
+  end
 end
diff --git a/app/models/query.rb b/app/models/query.rb
index 473ea4d86..289d683f6 100644
--- a/app/models/query.rb
+++ b/app/models/query.rb
@@ -325,6 +325,7 @@ class Query < ActiveRecord::Base
     :date_past => [ "=", ">=", "<=", "><", ">t-", "<t-", "><t-", "t-", "t", "ld", "w", "lw", "l2w", "m", "lm", "y", "!*", "*" ],
     :string => [ "~", "=", "!~", "!", "^", "$", "!*", "*" ],
     :text => [  "~", "!~", "^", "$", "!*", "*" ],
+    :search => [ "~", "!~" ],
     :integer => [ "=", ">=", "<=", "><", "!*", "*" ],
     :float => [ "=", ">=", "<=", "><", "!*", "*" ],
     :relation => ["=", "!", "=p", "=!p", "!p", "*o", "!o", "!*", "*"],
diff --git a/config/locales/en.yml b/config/locales/en.yml
index 2e1378ff7..678aab66d 100644
--- a/config/locales/en.yml
+++ b/config/locales/en.yml
@@ -417,6 +417,7 @@ en:
   field_default_issue_query: Default issue query
   field_default_project_query: Default project query
   field_default_time_entry_activity: Default spent time activity
+  field_any_searchable: Any searchable text
 
   setting_app_title: Application title
   setting_welcome_text: Welcome text
diff --git a/public/javascripts/application.js b/public/javascripts/application.js
index 7b2e7725e..6d01dc2b9 100644
--- a/public/javascripts/application.js
+++ b/public/javascripts/application.js
@@ -220,6 +220,7 @@ function buildFilterRow(field, operator, values) {
     break;
   case "string":
   case "text":
+  case "search":
     tr.find('td.values').append(
       '<span style="display:none;"><input type="text" name="v['+field+'][]" id="values_'+fieldId+'" size="30" class="value" /></span>'
     );
前田剛前田剛

テスト追加。

diff --git a/test/unit/query_test.rb b/test/unit/query_test.rb
index b41aa507c..2b02a1e07 100644
--- a/test/unit/query_test.rb
+++ b/test/unit/query_test.rb
@@ -844,6 +844,36 @@ class QueryTest < ActiveSupport::TestCase
     assert_equal [1, 3], find_issues_with_query(query).map(&:id).sort
   end
 
+  def test_fileter_any_searchable
+    User.current = User.find(1)
+    query = IssueQuery.new(
+      :name => '_',
+      :filters => {
+        'any_searchable' => {
+          :operator => '~',
+          :values => ['recipe']
+        }
+      }
+    )
+    result = find_issues_with_query(query)
+    assert_equal [1, 2, 3], result.map(&:id).sort
+  end
+
+  def test_fileter_any_searchable_should_search_searchable_custom_fields
+    User.current = User.find(1)
+    query = IssueQuery.new(
+      :name => '_',
+      :filters => {
+        'any_searchable' => {
+          :operator => '~',
+          :values => ['125']
+        }
+      }
+    )
+    result = find_issues_with_query(query)
+    assert_equal [1, 3], result.map(&:id).sort
+  end
+
   def test_filter_updated_by
     user = User.generate!
     Journal.create!(:user_id => user.id, :journalized => Issue.find(2), :notes => 'Notes')
このスクラップは2023/04/02にクローズされました