custom query needs to be able to access ticket change table

[edunne]

Hey guys we really need to be able to do something like the following…(ditch the two custom fields time_actual and time_estimate). Basically it allows us to see all the comments made to a ticket over time and then group the results by ticket id.

SELECT 
id AS __group__,
id AS ticket,  
summary,
version,
milestone,
(CASE WHEN c.value ISNULL THEN '' ELSE c.value END) as time_est,
(CASE WHEN n.value ISNULL THEN '' ELSE n.value END) as time_act,
(CASE WHEN tc.newvalue ISNULL THEN '' ELSE tc.newvalue END) as comments,
t.type AS type,
(CASE status WHEN 'assigned' THEN owner||' *' ELSE owner END) AS owner 

FROM ticket t, enum p 
LEFT OUTER JOIN ticket_custom c ON
       (t.id=c.ticket AND c.name='time_estimate')
LEFT OUTER JOIN ticket_custom n ON
       (t.id=n.ticket AND n.name='time_actual')
LEFT OUTER JOIN ticket_change tc ON
   (t.id=tc.ticket AND field = 'comment' )
WHERE status IN ('new', 'assigned', 'reopened') 
AND p.name = t.priority 
AND p.type = 'priority' 
ORDER BY id, milestone, t.type

[edunne]

this was supposed to be an enhancement.

[Christian Boos]

#5033 closed as duplicate.

[bobbysmith007@…]

As the author of the ​http://trac-hacks.org/wiki/TimingAndEstimationPlugin, It would be greatly beneficial for the query interface to expose querying of ticket_change rows as well. Currently the query interface is next to useless for time tracking purposes because there is no way to find all the time clocked between two given dates.

If this became part of trac, T&E plugin would no longer require the reports module (which it is my understanding will be phased out in future versions of Trac).

Thanks,

Russ

[Christian Boos]

See also #8729 (specifically requesting access to last comment).

[Christian Boos]

Milestone 1.0 deleted

[ramejan@…]

For me putting "Comments"( or "Changes", all I'm interested in are the comments actually since it tracks the discussion during the development and evolution of the ticket) check box next to the "Description" check box under the "Show under each result:" label. The list could get rather large but this is exactly what I would expect and it would be really nice.

[Peter Suter]

#11108 was closed as a duplicated. It requested a filter to search for tickets commented or changed by a given user.

[Jun Omae]

#12753 was closed as duplicate.

[Ryan J Ollos]

#12688 noted that modified in TracQuery#QueryLanguage is unclear, since it's actually last modified. To get all tickets modified in a date range we'd need to query the ticket_change table.