New Report: Tickets of Interest

[mpotter@…]

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.

[Remy Blank]

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.

[mpotter@…]

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

[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? Is there a better/more correct way to doing this test?

[mpotter@…]

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'".

[mpotter@…]

Replying to mpotter@…: Correction:

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

[mpotter@…]

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

[bobinabottle <bobinabottle1@…>]

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

[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.

[Christian Boos]

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.

[Ryan Ollos <ryano@…>]

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?

[Ryan Ollos <ryano@…>]

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

[mpotter@…]

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.

[mpotter@…]

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".

[mpotter@…]

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.

[mpotter@…]

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

@@ -10 +10 @@
     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
+   priority AS _priority, reporter AS _reporter, cc AS _cc,
+   (status = 'closed') AS _IsClosed,
+   (CASE status
+       WHEN 'closed' THEN changetime
+       ELSE (-1) * CAST(p.value AS int)
+    END) AS _CloseTimeOrOpenNegPriority
   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
@@ -18 +23 @@
    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
+    _IsClosed,
+    _CloseTimeOrOpenNegPriority DESC

[mpotter@…]

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?

[Mark Potter <mpotter@…>]

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.

[Christian Boos]

See also #9311 which proposes a similar enhancement.

[Christian Boos]

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:)?

[Christian Boos]

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?).

[Remy Blank]

Preparing for 1.0.

[Christian Boos]

Move feature requests to next-dev.

[Christian Boos]

well, once again… next-dev

[Jun Omae]

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

[Ryan J Ollos]

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.