Feature #5893

Filter issues by notes

Added by Peter Höpfl about 11 years ago. Updated about 1 month ago.

Status:ClosedStart date:2010-07-16
Priority:NormalDue date:
Assignee:Go MAEDA% Done:

0%

Category:Issues filter
Target version:5.0.0
Resolution:

Description

We run a big Redmine installation with more than 20000 issues. What we are missing is the ability to apply a filter on description and notes.

For Redmine version 0.9.4 I made a patch that allows us to filter for these fields and now I would like to contribute this patch to all Redmine users.
Unfortunately I have no svn access to provide an official patch file. Also I don't know if my code (first Ruby code) is ok.

Here is what i did:

File app/models/query.rb:

Line 125ff:
Old:
    QueryColumn.new(:subject, :sortable => "#{Issue.table_name}.subject"),

New:
    QueryColumn.new(:subject, :sortable => "#{Issue.table_name}.subject"),
    QueryColumn.new(:description, :sortable => 

Line 176ff:
Old:
                           "subject" => { :type => :text, :order => 8 },

New:
                           "subject" => { :type => :text, :order => 8 },
                           "description" => { :type => :text, :order => 16 },
                           "notes" => { :type => :text, :order => 17 },

Line 400ff:
Old:
      elsif field == 'watcher_id'
        db_table = Watcher.table_name
        db_field = 'user_id'
        sql << "#{Issue.table_name}.id #{ operator == '=' ? 'IN' : 'NOT IN' } (SELECT #{db_table}.watchable_id FROM #{db_table} WHERE #{db_table}.watchable_type='Issue' A$
        sql << sql_for_field(field, '=', v, db_table, db_field) + ')'
      else

New:
      elsif field == 'watcher_id'
        db_table = Watcher.table_name
        db_field = 'user_id'
        sql << "#{Issue.table_name}.id #{ operator == '=' ? 'IN' : 'NOT IN' } (SELECT #{db_table}.watchable_id FROM #{db_table} WHERE #{db_table}.watchable_type='Issue' A$
        sql << sql_for_field(field, '=', v, db_table, db_field) + ')'
      elsif field == 'notes'
        db_table = Journal.table_name
        db_field = 'notes'
        sql << '((' + sql_for_field(field, operator, v, db_table, db_field) + ') OR (' + sql_for_field(field, operator, v, Issue.table_name, 'description') + '))'
      else

Line 423ff:
Old:
  def issue_count
    Issue.count(:include => [:status, :project], :conditions => statement)

New:
  def issue_count
    r = false
    filters.each_key do |field|
      next unless field == "notes" 
      r = true
    end
    if r
      Issue.count(:include => [:status, :project, :journals], :conditions => statement)
    else
      Issue.count(:include => [:status, :project], :conditions => statement)
    end

Line 439ff:
Old:
  def issue_count_by_group
    r = nil
    if grouped?
      begin
        # Rails will raise an (unexpected) RecordNotFound if there's only a nil group value
        r = Issue.count(:group => group_by_statement, :include => [:status, :project], :conditions => statement)

New:
  def issue_count_by_group
    r = nil
    if grouped?
      begin
        x = false
        filters.each_key do |field|
          next unless field == "notes" 
          x = true
        end
        if x
          # Rails will raise an (unexpected) RecordNotFound if there's only a nil group value
          r = Issue.count(:group => group_by_statement, :include => [:status, :project, :journals], :conditions => statement)
        else
          # Rails will raise an (unexpected) RecordNotFound if there's only a nil group value
          r = Issue.count(:group => group_by_statement, :include => [:status, :project], :conditions => statement)
        end

Line 470ff:
Old:
  def issues(options={})
    order_option = [group_by_sort_order, options[:order]].reject {|s| s.blank?}.join(',')
    order_option = nil if order_option.blank?

    Issue.find :all, :include => ([:status, :project] + (options[:include] || [])).uniq,
                     :conditions => Query.merge_conditions(statement, options[:conditions]),
                     :order => order_option,
                     :limit  => options[:limit],
                     :offset => options[:offset]

New:
  def issues(options={})
    order_option = [group_by_sort_order, options[:order]].reject {|s| s.blank?}.join(',')
    order_option = nil if order_option.blank?

    r = false
    filters.each_key do |field|
      next unless field == "notes" 
      r = true
    end
    if r
    Issue.find :all, :include => ([:status, :project, :journals] + (options[:include] || [])).uniq,
                     :conditions => Query.merge_conditions(statement, options[:conditions]),
                     :order => order_option,
                     :limit  => options[:limit],
                     :offset => options[:offset]
    else
    Issue.find :all, :include => ([:status, :project] + (options[:include] || [])).uniq,
                     :conditions => Query.merge_conditions(statement, options[:conditions]),
                     :order => order_option,
                     :limit  => options[:limit],
                     :offset => options[:offset]
    end

It would be great if you would integrate this code into Redmine.

Thanks,

Peter

HOW_TO.txt Magnifier - HOW TO (5.04 KB) Simon RES, 2011-03-30 14:54

filter-by-notes.patch Magnifier (1.32 KB) Alexander Achenbach, 2019-12-02 10:38

filter-by-notes-with-tests.patch Magnifier (3.39 KB) Yuichi HARADA, 2021-03-09 01:43

filter-by-notes-with-tests-v2.patch Magnifier - Original patch by Tomasz O, Alexander Achenbach, and Yuichi HARADA (3.42 KB) Go MAEDA, 2021-03-09 10:25

start-with-does-not-work.png (68.8 KB) Go MAEDA, 2021-03-13 03:11

filter-by-notes-with-tests-v3.patch Magnifier (3.4 KB) Yuichi HARADA, 2021-03-15 05:42

statement-invalid-log.txt Magnifier (3.78 KB) Go MAEDA, 2021-04-05 11:58


Related issues

Related to Redmine - Feature #1159: Allow issue description to be searchable as a filter Closed 2008-05-01
Related to Redmine - Feature #14468: Search for text in description and comments in issue sear... Closed
Related to Redmine - Feature #680: free text ticket filter New 2008-02-17

Associated revisions

Revision 20955
Added by Go MAEDA 3 months ago

Filter issues by notes (#5893).

Patch by Yuichi HARADA.

History

#1 Updated by Mischa The Evil about 11 years ago

  • Tracker changed from Feature to Patch
  • Category set to Issues
Fixed and improved:
  • Tracker
  • Description
  • Category

#2 Updated by Simon RES over 10 years ago

Here is a how-to add a public/private field for journals.

#3 Updated by Charles Monteiro over 9 years ago

We need to filter on description as well

#4 Updated by Terence Mill over 8 years ago

related to #1159

#5 Updated by Mischa The Evil almost 8 years ago

  • Related to Feature #14468: Search for text in description and comments in issue search and restrict projects. added

#6 Updated by Toshi MARUYAMA almost 5 years ago

#7 Updated by Tomasz O about 2 years ago

This patch enables applying filter on notes in Redmine version 3.4.4:

 diff -c app/models/issue_query.rb.orig app/models/issue_query.rb
*** app/models/issue_query.rb.orig    2018-01-08 13:38:16.000000000 -0600
--- app/models/issue_query.rb    2019-05-24 04:41:38.709524653 -0500
***************
*** 136,141 ****
--- 136,143 ----
      add_available_filter "estimated_hours", :type => :float
      add_available_filter "done_ratio", :type => :integer

+     add_available_filter "notes", :type => :text
+ 
      if User.current.allowed_to?(:set_issues_private, nil, :global => true) ||
        User.current.allowed_to?(:set_own_issues_private, nil, :global => true)
        add_available_filter "is_private",
***************
*** 359,364 ****
--- 361,376 ----
      raise StatementInvalid.new(e.message)
    end

+   def sql_for_notes_field(field, operator, value)
+     neg = (operator == '!' ? 'NOT' : '')
+     subquery = "SELECT 1 FROM #{Journal.table_name}" +
+       " WHERE #{Journal.table_name}.journalized_type='Issue' AND #{Journal.table_name}.journalized_id=#{Issue.table_name}.id" +
+       " AND (#{sql_for_field field, operator , value, Journal.table_name, 'notes'})" +
+       " AND (#{Journal.visible_notes_condition(User.current, :skip_pre_condition => true)})" 
+ 
+     "#{neg} EXISTS (#{subquery})" 
+   end
+ 
    def sql_for_updated_by_field(field, operator, value)
      neg = (operator == '!' ? 'NOT' : '')
      subquery = "SELECT 1 FROM #{Journal.table_name}" +

Maybe someone else will find them usefull.

#8 Updated by Alexander Achenbach over 1 year ago

As a follow-up to #5893#note-7, here's a revised patch of the notes query against Redmine 4.0.5.
It fixes logic problems in operator handling that were in the original patch.

#9 Updated by Go MAEDA over 1 year ago

  • Category changed from Issues to Issues filter

#10 Updated by Yuichi HARADA 5 months ago

Alexander Achenbach wrote:

As a follow-up to #5893#note-7, here's a revised patch of the notes query against Redmine 4.0.5.
It fixes logic problems in operator handling that were in the original patch.

Thank you for creating the patch.
I adjusted filter-by-notes.patch to work with Redmine-trunk (r20766) and added tests.

#11 Updated by Go MAEDA 5 months ago

This is a feature I have wanted for years. Thank you for updating the patch and adding the test.

I have changed the patch to move the "notes" position in the filters dropdown to below the "description".

#12 Updated by Go MAEDA 5 months ago

  • Target version changed from Candidate for next major release to 4.2.0

Setting the target version to 4.2.0.

#13 Updated by Go MAEDA 5 months ago

The patch needs to be fixed.

"starts with" and "ends with" operators for notes don't work as expected. They behave like "any" operator. This means that even if you give a nonexistent string to those operators, the issues list displays issues that have any notes.

My environment:

Environment:
  Redmine version                4.1.1.devel
  Ruby version                   2.7.2-p137 (2020-10-01) [x86_64-darwin19]
  Rails version                  5.2.4.4
  Environment                    development
  Database adapter               SQLite
  Mailer queue                   ActiveJob::QueueAdapters::InlineAdapter
  Mailer delivery                smtp

#14 Updated by Yuichi HARADA 4 months ago

Go MAEDA wrote:

"starts with" and "ends with" operators for notes don't work as expected. They behave like "any" operator. This means that even if you give a nonexistent string to those operators, the issues list displays issues that have any notes.

I've confirmed. I fixed the patch as follows.

diff --git a/app/models/issue_query.rb b/app/models/issue_query.rb
index deb06dc82..4848fcb2d 100644
--- a/app/models/issue_query.rb
+++ b/app/models/issue_query.rb
@@ -451,9 +451,9 @@ class IssueQuery < Query
   def sql_for_notes_field(field, operator, value)
     subquery = "SELECT 1 FROM #{Journal.table_name}" +
       " WHERE #{Journal.table_name}.journalized_type='Issue' AND #{Journal.table_name}.journalized_id=#{Issue.table_name}.id" +
-      " AND (#{sql_for_field field, operator.ends_with?("~") ? "~" : "*", value, Journal.table_name, 'notes'})" +
+      " AND (#{sql_for_field field, operator.sub(/^!/, ''), value, Journal.table_name, 'notes'})" +
       " AND (#{Journal.visible_notes_condition(User.current, :skip_pre_condition => true)})" 
-    "#{operator.starts_with?("!") ? "NOT EXISTS" : "EXISTS"} (#{subquery})" 
+    "#{/^!/.match?(operator) ? "NOT EXISTS" : "EXISTS"} (#{subquery})" 
   end

   def sql_for_updated_by_field(field, operator, value)

#15 Updated by Go MAEDA 4 months ago

  • Target version changed from Candidate for next major release to 5.0.0

Setting the target version to 5.0.0.

#16 Updated by Go MAEDA 4 months ago

While I was trying the patch, I came across an SQL error (sorry, I don't know how to reproduce).

Query::StatementInvalid: SQLite3::SQLException: no such column: issues.notes

#17 Updated by Go MAEDA 3 months ago

  • Target version changed from Candidate for next major release to 5.0.0

Go MAEDA wrote:

While I was trying the patch, I came across an SQL error (sorry, I don't know how to reproduce).

[...]

I have been investigating this issue for a while, and the only time I could reproduce the error was when I removed the sql_for_notes_field method.

The error reported in #5893#note-16 is probably due to my tampering the source code while checking the patch.

Setting the target version to 5.0.0 again.

#18 Updated by Go MAEDA 3 months ago

  • Subject changed from Filter Options: Allow to filter for description and notes to Filter issues by notes
  • Status changed from New to Closed
  • Assignee set to Go MAEDA

Committed the patch. Thank you for your contribution.

#19 Updated by Go MAEDA about 1 month ago

  • Tracker changed from Patch to Feature

Also available in: Atom PDF