Project

General

Profile

Actions

Feature #5893

closed

Filter issues by notes

Added by Peter Höpfl over 13 years ago. Updated over 1 year ago.

Status:
Closed
Priority:
Normal
Assignee:
Category:
Filters
Target version:
Start date:
2010-07-16
Due date:
% Done:

0%

Estimated time:
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


Files

HOW_TO.txt (5.04 KB) HOW_TO.txt HOW TO Simon RES, 2011-03-30 14:54
filter-by-notes.patch (1.32 KB) filter-by-notes.patch Alexander Achenbach, 2019-12-02 10:38
filter-by-notes-with-tests.patch (3.39 KB) filter-by-notes-with-tests.patch Yuichi HARADA, 2021-03-09 01:43
filter-by-notes-with-tests-v2.patch (3.42 KB) filter-by-notes-with-tests-v2.patch Original patch by Tomasz O, Alexander Achenbach, and Yuichi HARADA Go MAEDA, 2021-03-09 10:25
start-with-does-not-work.png (68.8 KB) start-with-does-not-work.png Go MAEDA, 2021-03-13 03:11
filter-by-notes-with-tests-v3.patch (3.4 KB) filter-by-notes-with-tests-v3.patch Yuichi HARADA, 2021-03-15 05:42
statement-invalid-log.txt (3.78 KB) statement-invalid-log.txt Go MAEDA, 2021-04-05 11:58

Related issues

Related to Redmine - Feature #1159: Allow issue description to be searchable as a filterClosedJean-Philippe Lang2008-05-01

Actions
Related to Redmine - Feature #14468: Search for text in description and comments in issue search and restrict projects.Closed

Actions
Related to Redmine - Feature #680: free text ticket filterClosed2008-02-17

Actions
Actions #1

Updated by Mischa The Evil over 13 years ago

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

Updated by Simon RES almost 13 years ago

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

Actions #3

Updated by Charles Monteiro over 12 years ago

We need to filter on description as well

Actions #4

Updated by Terence Mill about 11 years ago

related to #1159

Actions #5

Updated by Mischa The Evil over 10 years ago

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

Updated by Toshi MARUYAMA over 7 years ago

Actions #7

Updated by Tomasz O almost 5 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.

Actions #8

Updated by Alexander Achenbach about 4 years 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.

Actions #9

Updated by Go MAEDA about 4 years ago

  • Category changed from Issues to Issues filter
Actions #10

Updated by Yuichi HARADA almost 3 years 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.

Actions #11

Updated by Go MAEDA almost 3 years 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".

Actions #12

Updated by Go MAEDA almost 3 years ago

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

Setting the target version to 4.2.0.

Actions #13

Updated by Go MAEDA almost 3 years 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

Actions #14

Updated by Yuichi HARADA almost 3 years 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)
Actions #15

Updated by Go MAEDA almost 3 years ago

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

Setting the target version to 5.0.0.

Actions #16

Updated by Go MAEDA almost 3 years 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
Actions #17

Updated by Go MAEDA almost 3 years 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.

Actions #18

Updated by Go MAEDA almost 3 years 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.

Actions #19

Updated by Go MAEDA over 2 years ago

  • Tracker changed from Patch to Feature
Actions #20

Updated by Go MAEDA almost 2 years ago

  • Category changed from Issues filter to Filters
Actions

Also available in: Atom PDF