Edgewall Software
Modify

Opened 13 years ago

Last modified 3 years ago

#5219 new enhancement

custom query needs to be able to access ticket change table

Reported by: edunne Owned by:
Priority: normal Milestone: unscheduled
Component: query system Version:
Severity: normal Keywords: comments
Cc: mpotter@…, ramejan@… Branch:
Release Notes:
API Changes:

Description (last modified by Emmanuel Blot)

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

Attachments (0)

Change History (13)

comment:1 by edunne, 13 years ago

Type: defectenhancement

this was supposed to be an enhancement.

comment:2 by Emmanuel Blot, 13 years ago

Description: modified (diff)

comment:3 by Christian Boos, 13 years ago

Component: generalreport system
Keywords: query comments added
Milestone: 0.12
Owner: changed from Jonas Borgström to Matthew Good

#5033 closed as duplicate.

comment:4 by bobbysmith007@…, 12 years ago

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

Version 0, edited 12 years ago by bobbysmith007@… (next)

comment:5 by Christian Boos, 10 years ago

Milestone: next-major-0.1X1.0

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

comment:6 by mpotter@…, 10 years ago

Cc: mpotter@… added

comment:7 by Christian Boos, 10 years ago

Milestone: 1.0unscheduled

Milestone 1.0 deleted

comment:8 by ramejan@…, 7 years ago

Cc: ramejan@… added

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.

comment:9 by Peter Suter, 6 years ago

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

comment:10 by Ryan J Ollos, 5 years ago

Owner: Matthew Good removed

comment:11 by Jun Omae, 3 years ago

#12753 was closed as duplicate.

comment:12 by Ryan J Ollos, 3 years ago

Component: report systemquery system
Keywords: query removed

comment:13 by Ryan J Ollos, 3 years ago

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

Modify Ticket

Change Properties
Set your email in Preferences
Action
as new The ticket will remain with no owner.
The ticket will be disowned. Next status will be 'new'.
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.