Show number of replies/comments on Trac report

Question

I'd like to add a count of replies for each ticket shown in the Active Ticket report.

Here's my current query:

SELECT p.value AS __color__,
   id AS ticket, summary, component, version, milestone, t.type AS type, 
   reporter, owner, status,
   time AS created,
   changetime AS _changetime, description AS _description,
   date(changetime / 1000000, 'unixepoch') as LastUpdate,
   reporter AS _reporter
  FROM ticket t
  LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
  WHERE status <> 'closed'
  ORDER BY CAST(p.value AS integer), milestone, t.type, time

Is it possible to add the reply/comment count?

Answer 1

Using a subquery it could look like

SELECT p.value AS __color__,
    id AS ticket, summary, component, version, milestone, t.type AS type, 
    reporter, owner, status,
    time AS created,
    changetime AS _changetime, description AS _description,
    date(changetime / 1000000, 'unixepoch') as LastUpdate,
    reporter AS _reporter,
    CASE WHEN c.count ISNULL OR c.count = '' THEN 0 ELSE c.count
    END AS comments
FROM ticket t
LEFT JOIN (
    SELECT ticket, count(newvalue) AS count
    FROM ticket_change
    WHERE field = 'comment' AND NOT newvalue = ''
    GROUP by ticket) AS c
    ON t.id = c.ticket
LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
WHERE status <> 'closed'
ORDER BY CAST(p.value AS integer), milestone, t.type, time

Not all column names need to include table names, but it doesn't hurt to know their origin, especially if one does not know too much about the db schema.

The CASE expression is used to fill 'Comments' column for tickets without comments. Otherwise these cells would be just empty.

AND NOT newvalue = '' excludes changes without a real comment like changes owner reassignments.

I would prefer to order by ticket ID too, but it depends on the amount of open tickets.