Edgewall Software
Modify

Opened 15 years ago

Last modified 9 years ago

#8559 new enhancement

New Report: Tickets of Interest

Reported by: mpotter@… Owned by:
Priority: normal Milestone: next-major-releases
Component: report system Version: 0.11.4
Severity: normal Keywords: USER
Cc: Ryan J Ollos, bobinabottle1@…, Jun Omae Branch:
Release Notes:
API Changes:
Internal Changes:

Description

When one visits a Trac system as a user of a product and not as a developer of the product, one often wants to search for those tickets of personal interest. Such tickets would be defined as those tickets where the user is the Reporter OR in the CC (or maybe additionally as a commenter).

  • It would be nice to see such a report added here.
  • Additionally, it would also be nice to see this added as one of the default reports thus would likely to be found on other Trac installations.
  • This report is easy to write as an SQL based report. However, I do not see how one could implement this as a Custom Query report. Thus it is currently not possible to add such a report when the SQL report system is fully deprecated. Also it is not possible for a user to implement this as a custom query that they could save as a book mark.

Attachments (0)

Change History (29)

in reply to:  description ; comment:1 by Remy Blank, 15 years ago

Replying to mpotter@…:

This report is easy to write as an SQL based report.

Care to give it a shot? Just paste it here!

Thus it is currently not possible to add such a report when the SQL report system is fully deprecated.

Just for clarification: the report system is not going away anytime soon. We do try to make the query system as powerful as possible to give flexibility to the user, but the report system will still be around.

in reply to:  1 ; comment:2 by mpotter@…, 15 years ago

Replying to rblank:

Replying to mpotter@…:

This report is easy to write as an SQL based report.

Care to give it a shot? Just paste it here!

Here is a first attempt:

Report Title:

My User Tickets

Description:

Tickets of interest as a user. That is tickets that the current user reported, commented on, or marked for CC.

Query for Report:

-- Tickets that the current user reported, commented on, or marked for CC. 
-- (including closed).

SELECT 
  DISTINCT 
   p.value AS __color__,
   (CASE status 
      WHEN 'closed' THEN 'color: #777; background: #ddd; border-color: #ccc;'
    END) AS __style__,
   id AS ticket, summary, component, milestone, status, resolution, 
   t.time AS created, changetime AS modified,
   priority AS _priority, reporter AS _reporter, cc AS _cc
  FROM ticket t
  LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
  LEFT JOIN ticket_change tc ON id = tc.ticket
  WHERE reporter = '$USER' 
   OR cc LIKE '%$USER%'
   OR (tc.author='$USER' AND tc.field = 'comment')
  ORDER BY
    (status = 'closed'), 
    (CASE status
       WHEN 'closed' THEN changetime
       ELSE (-1) * CAST(p.value AS int)
    END) DESC

in reply to:  2 ; comment:3 by mpotter@…, 15 years ago

Replying to mpotter@…:

   OR cc LIKE '%$USER%'

Is that the best way to test for a user being in the CC? I would think that this could pick up more that what is desired. Example: If the user was 'bob' wouldn't this also pickup tickets that 'bobby' commented on? Is there a better/more correct way to doing this test?

comment:4 by mpotter@…, 15 years ago

Can see several possible changes to this depending on desired results.

  • What columns to include?
  • How to order?
  • What tickets to include?
    • Currently includes all tickets that the user reported, that the user is in the CC list, or the user made a comment to.
    • For just reported or in CC:
      • Remove the "OR (tc.author='$USER' AND tc.field = 'comment')" line.
    • To include tickets that the user made any changes to, with or without a comment:
      • Change the "OR (tc.author='$USER' AND tc.field = 'comment')" line to "OR tc.author='$USER'".

in reply to:  3 comment:5 by mpotter@…, 15 years ago

Replying to mpotter@…: Correction:

Example: If the user was 'bob' wouldn't this also pickup tickets that 'bobby' commented on CC'ed?

in reply to:  3 ; comment:6 by mpotter@…, 15 years ago

Replying to mpotter@…:

Replying to mpotter@…:

   OR cc LIKE '%$USER%'

Is that the best way to test for a user being in the CC? I would think that this could pick up more that what is desired. Example: If the user was 'bob' wouldn't this also pickup tickets that 'bobby' commented on CC'ed? Is there a better/more correct way to doing this test?

Thinking about this more: Since the cc is normalized as a comma separated list, to truly test for $USER being in cc would be:

   OR cc LIKE '$USER,%'    -- USER at beginning of list
   OR cc LIKE '% $USER'    -- USER at end of list
   OR cc LIKE '% $USER,%'  -- USER in middle of list
   OR cc = '$USER'         -- USER is only member of list

comment:7 by Ryan Ollos <ryano@…>, 15 years ago

Cc: ryano@… added

comment:8 by Christian Boos, 14 years ago

Milestone: → next-major-0.1X

comment:9 by bobinabottle <bobinabottle1@…>, 14 years ago

Cc: bobinabottle1@… added

in reply to:  2 ; comment:10 by bobinabottle <bobinabottle1@…>, 14 years ago

Replying to mpotter@…:

-- Tickets that the current user reported, commented on, or marked for CC. 
-- (including closed).

SELECT 
  DISTINCT 
   p.value AS __color__,
   (CASE status 
      WHEN 'closed' THEN 'color: #777; background: #ddd; border-color: #ccc;'
    END) AS __style__,
   id AS ticket, summary, component, milestone, status, resolution, 
   t.time AS created, changetime AS modified,
   priority AS _priority, reporter AS _reporter, cc AS _cc
  FROM ticket t
  LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
  LEFT JOIN ticket_change tc ON id = tc.ticket
  WHERE reporter = '$USER' 
   OR cc LIKE '%$USER%'
   OR (tc.author='$USER' AND tc.field = 'comment')
  ORDER BY
    (status = 'closed'), 
    (CASE status
       WHEN 'closed' THEN changetime
       ELSE (-1) * CAST(p.value AS int)
    END) DESC

When I run this query (under postgres) I get the error:

ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select list

comment:11 by Ryan Ollos <ryano@…>, 14 years ago

I think it would be very useful to create a CookBook/ReportRecipes page in the CookBook, where a short description and SQL for a report could be posted.

in reply to:  11 comment:12 by Christian Boos, 14 years ago

Replying to Ryan Ollos <ryano@…>:

I think it would be very useful to create a CookBook/ReportRecipes page in the CookBook, where a short description and SQL for a report could be posted.

Good idea, but rather a CookBook/Configuration/Reports page, as we already have CookBook/Configuration/Workflow.

in reply to:  6 comment:13 by Ryan Ollos <ryano@…>, 14 years ago

Replying to mpotter@…:

Thinking about this more: Since the cc is normalized as a comma separated list, to truly test for $USER being in cc would be: …

I don't know SQL, but it seems like something that would be handled with a regular expression. Does SQL have regular expression functionality?

comment:14 by Ryan Ollos <ryano@…>, 14 years ago

Replying to cboos:

Good idea, but rather a CookBook/Configuration/Reports page, as we already have CookBook/Configuration/Workflow.

Page now exists at CookBook/Configuration/Reports. (I made an error, and the page at CookBook/Configuration/Report should be deleted).

I've been using the report from this ticket created by mpotter, and it seems to work well. I was thinking I'd add this as the first report for the page, unless the ticket reporter would like to do this.

Report from comment:2 with modifications described in comment:6.

SELECT 
  DISTINCT 
   p.value AS __color__,
   (CASE status 
      WHEN 'closed' THEN 'color: #777; background: #ddd; border-color: #ccc;'
    END) AS __style__,
   id AS ticket, summary, component, milestone, status, resolution, 
   t.time AS created, changetime AS modified,
   priority AS _priority, reporter AS _reporter, cc AS _cc
  FROM ticket t
  LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
  LEFT JOIN ticket_change tc ON id = tc.ticket
  WHERE reporter = '$USER' 
   OR cc LIKE '$USER,%'
   OR cc LIKE '% $USER'
   OR cc LIKE '% $USER,%'
   OR cc = '$USER'
   OR (tc.author='$USER' AND tc.field = 'comment')
  ORDER BY
    (status = 'closed'), 
    (CASE status
       WHEN 'closed' THEN changetime
       ELSE (-1) * CAST(p.value AS int)
    END) DESC

in reply to:  14 comment:15 by mpotter@…, 14 years ago

Replying to Ryan Ollos <ryano@…>:

Page now exists at CookBook/Configuration/Reports.

I've been using the report from this ticket created by mpotter, and it seems to work well. I was thinking I'd add this as the first report for the page, unless the ticket reporter would like to do this.

Done.

in reply to:  description ; comment:16 by mpotter@…, 14 years ago

Reviewing some of the earlier points of this ticket.

Replying to mpotter@…:

  • It would be nice to see such a report added here.

Any chance of this?

  • Additionally, it would also be nice to see this added as one of the default reports thus would likely to be found on other Trac installations.

Any change of this? I can see that the initial reports are "sample" reports, but many sites just leave these, so it would be nice to be one of the initial reports so that it would be more common.

  • This report is easy to write as an SQL based report.

Done.

However, I do not see how one could implement this as a Custom Query report. […] Also it is not possible for a user to implement this as a custom query that they could save as a book mark.

In general I always prefer to use a Custom Query Report instead of an SQL Report when building an available report, when I can; that way a user can make changes to the Query as needed (e.g. Adding or remove columns or conditions). This points to the possibility of some new feature requests. What could be done the the Custom Query to add the ability for a User to build up such a query? With 0.12 the OR capability was added, therefore a user can now do a "Reporter OR in CC", but cannot do "… OR commenter" and the in CC isn't exact (but very close). Therefore suggestions for possible improvements to the Custom Query:

  • Add a new CC filter option (what to call it I do not know since the generic "contains" already exists) which add the SQL conditions:
       OR cc LIKE '{fieldValue},%'
       OR cc LIKE '% {fieldValue}'
       OR cc LIKE '% {fieldValue},%'
       OR cc = '{fieldValue}'
    
  • Add a new Commenter filter, which adds the SQL join and condition:
      LEFT JOIN ticket_change tc ON id = tc.ticket
      ...
      OR (tc.author='{fieldValue}' AND tc.field = 'comment')
    
  • Add a new Contributer filter, which adds the SQL join and condition:
      LEFT JOIN ticket_change tc ON id = tc.ticket
      ...
      OR tc.author='{fieldValue}'
    
  • The last two could be combined into one, a new Contributer filter, which one can then select an additional type with two of the choices being "any" or "commenter".

in reply to:  16 comment:17 by mpotter@…, 14 years ago

Replying to mpotter@…:

  • Add a new Commenter filter, which adds the SQL join and condition:
      LEFT JOIN ticket_change tc ON id = tc.ticket
      ...
      OR (tc.author='{fieldValue}' AND tc.field = 'comment')
    
  • Add a new Contributer filter, which adds the SQL join and condition:
      LEFT JOIN ticket_change tc ON id = tc.ticket
      ...
      OR tc.author='{fieldValue}'
    
  • The last two could be combined into one, a new Contributer filter, which one can then select an additional type with two of the choices being "any" or "commenter".

Forgot, the DISTINCT modifier also needs to be added.

in reply to:  10 ; comment:18 by mpotter@…, 14 years ago

Replying to bobinabottle <bobinabottle1@…>:

When I run this query (under postgres) I get the error:

ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select list

Do not have postgress to validate these changes, but I would assume the following changes would resolve the issue.

  • .sql

    old new  
    1010    END) AS __style__,
    1111   id AS ticket, summary, component, milestone, status, resolution,
    1212   t.time AS created, changetime AS modified,
    13    priority AS _priority, reporter AS _reporter, cc AS _cc
     13   priority AS _priority, reporter AS _reporter, cc AS _cc,
     14   (status = 'closed') AS _IsClosed,
     15   (CASE status
     16       WHEN 'closed' THEN changetime
     17       ELSE (-1) * CAST(p.value AS int)
     18    END) AS _CloseTimeOrOpenNegPriority
    1419  FROM ticket t
    1520  LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
    1621  LEFT JOIN ticket_change tc ON id = tc.ticket
     
    1823   OR cc LIKE '%$USER%'
    1924   OR (tc.author='$USER' AND tc.field = 'comment')
    2025  ORDER BY
    21     (status = 'closed'),
    22     (CASE status
    23        WHEN 'closed' THEN changetime
    24        ELSE (-1) * CAST(p.value AS int)
    25     END) DESC
     26    _IsClosed,
     27    _CloseTimeOrOpenNegPriority DESC
Last edited 14 years ago by Remy Blank (previous) (diff)

in reply to:  18 comment:19 by mpotter@…, 14 years ago

Replying to mpotter@…:

Darn, hit Submit when I meant Preview. Looks like the only error I had made was those three closing curly braces shouldn't be a part of the diff; end of new line 27.

Could someone with postgress confirm?

in reply to:  18 comment:20 by Mark Potter <mpotter@…>, 12 years ago

Replying to mpotter@…:

Do not have postgress to validate these changes, but I would assume the following changes would resolve the issue. …

I now am using PostgreSQL and can confirm the above changes do work.

comment:21 by Christian Boos, 12 years ago

See also #9311 which proposes a similar enhancement.

in reply to:  description comment:22 by Christian Boos, 12 years ago

Keywords: USER added
Milestone: next-major-0.1X → 0.13

Replying to mpotter@…:

  • It would be nice to see such a report added here.

Added as {41}

  • Additionally, it would also be nice to see this added as one of the default reports thus would likely to be found on other Trac installations.

Will check with SQLite/MySQL. If we take it, then we'll rename {6} / #9311 to "My Tickets (developer centric)".

What about using groups like in #9311? e.g. Reported / Modified (comments added or on CC:)?

in reply to:  16 comment:23 by Christian Boos, 12 years ago

Replying to mpotter@…:

Reviewing some of the earlier points of this ticket.

Replying to mpotter@…:

However, I do not see how one could implement this as a Custom Query report. […] Also it is not possible for a user to implement this as a custom query that they could save as a book mark.

In general I always prefer to use a Custom Query Report instead of an SQL Report when building an available report, when I can; that way a user can make changes to the Query as needed (e.g. Adding or remove columns or conditions).

… and now, even batch modifications (though usually not permitted for simple users). So yes, a custom query has lots of advantages.

But instead of adding lots of special purpose filters like you suggested, I think there could be another way: add a "Send to Query" button that would simply pass the list of ids of all the tickets from the report to a custom query (like for example in #7966,​8559,​9311). That would at least give the possibility to refine further the results obtained from a report, sort and group those tickets differently, add extra filters and columns…

The only problem would be this fixed id list, which will make bookmarking such a query not very useful (but maybe we could imagine a report=n parameter which would automatize that pipelining?).

comment:24 by Remy Blank, 12 years ago

Milestone: 1.0 → 1.0-triage

Preparing for 1.0.

comment:25 by Christian Boos, 12 years ago

Move feature requests to next-dev.

comment:26 by Christian Boos, 12 years ago

Milestone: next-stable-1.0.x → next-dev-1.1.x

well, once again… next-dev

in reply to:  14 comment:27 by Jun Omae, 10 years ago

Cc: Jun Omae added

Replying to Ryan Ollos <ryano@…>:

Report from comment:2 with modifications described in comment:6. ...

It would be simple to use EXISTS (...) rather than DISTINCT ... LEFT JOIN, the same form in comment:32:ticket:9311.

SELECT 
   p.value AS __color__,
   (CASE status 
      WHEN 'closed' THEN 'color: #777; background: #ddd; border-color: #ccc;'
    END) AS __style__,
   id AS ticket, summary, component, milestone, status, resolution, 
   t.time AS created, changetime AS modified,
   priority AS _priority, reporter AS _reporter, cc AS _cc
  FROM ticket t
  LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
  WHERE reporter = $USER 
   OR cc LIKE '$USER,%'
   OR cc LIKE '% $USER'
   OR cc LIKE '% $USER,%'
   OR cc = $USER
   OR EXISTS (SELECT * FROM ticket_change tc
              WHERE t.id = tc.ticket AND tc.author = $USER AND tc.field = 'comment')
  ORDER BY
    (status = 'closed'), 
    (CASE status
       WHEN 'closed' THEN changetime
       ELSE (-1) * CAST(p.value AS int)
    END) DESC

comment:28 by Ryan J Ollos, 10 years ago

Cc: Ryan J Ollos added; ryano@… removed

comment:29 by Ryan J Ollos, 9 years ago

Milestone: next-dev-1.1.x → next-major-releases

Retargetting tickets to narrow focus for milestone:1.2. Please move the ticket back to milestone:next-dev-1.1.x if you intend to resolve it by milestone:1.2.

Modify Ticket

Change Properties
Set your email in Preferences
Action
as new The ticket will remain with no owner.
The ticket will be disowned.
as The resolution will be set. Next status will be 'closed'.
The owner will be changed from (none) to anonymous. Next status will be 'assigned'.

Add Comment


E-mail address and name can be saved in the Preferences .
 
Note: See TracTickets for help on using tickets.