-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On 10.09.2012 13:16, init wrote:
> Hello! I have trac with mysql connected database. I want to know how to
> complete request to view all closed tickets, where user is currently
> logon user ($USER export) (it will be "My closed tickets") with sorting
> by resolving date.
> Please help me to complete this MySQL report.
If you want me to complete, where is the current state?
I've merely stated requirements so far. This doesn't show much effort
from your side, right?
While TracReports could contain any SQL, I'm not familiar with MySQL
merits. And as a general attitude Trac folks tend to refrain from any
but the most basic SQL statements to make things really portable at
least for the supported db backends, that include SQLite and PostgreSQL too.
So let's approach this as you should do in the future:
all tickets - by id and summary? I assume so.
closed - that's the status
user - considering the context you rather want ticket owner here
$USER - right, this is the current logged-in user
sorting by resolving date - this is a bit hard, because the only
special Trac ticket time stamp is time, the time of ticket creation.
Changetime, as it is named, changes with every comment, and you can
comment even after closing a ticket. So we need to find the time stamp
of the (last) change, where status is set to closed. Sure, you could
reopen and close again, so really the last one. The report label is just
wiki text - you shouldn't need help with that, right?
Putting it together now:
SELECT
id, summary
FROM ticket
WHERE status='closed'
AND owner=$USER
yields just the tickets - but not the correct ordering yet
SELECT
id, summary, changetime as date
FROM ticket t
LEFT JOIN ticket_change tc ON t.id=tc.ticket
WHERE t.status='closed'
AND owner=$USER
AND field='status'
AND newvalue='closed'
has closing dates in too, but maybe more than one per ticket,
so we have to approach it differently again:
SELECT ticket, MAX(time) as date
FROM ticket_change
WHERE field='status'
AND newvalue='closed'
GROUP by ticket
eliminates duplicate results per ticket
leaves just changetime column for reference
SELECT owner AS __group__,
ticket, summary, MAX(tc.time) as date
FROM ticket_change tc
LEFT JOIN ticket ON tc.ticket=id
WHERE field='status'
AND newvalue='closed'
AND owner=$USER
GROUP by ticket
ORDER BY tc.time DESC
adds username as top label (AS __group__)
finally sorts (in descending!) order by latest closing time
Thats's it. Feel free to do differently, i.e. by following
http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/
for MySQL specific SQL with probably better performance
But that would be all proprietary syntax, so you're on your own again.
Sincerely,
Steffen Hoffmann
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
iEYEARECAAYFAlBOStcACgkQ31DJeiZFuHffYgCdHD6okXX7T+uFC103a5oVt+S4
Rt4AoNpof6FP5E4qoC2NHMTpRymxz436
=/7Xf
-----END PGP SIGNATURE-----
--
You received this message because you are subscribed to the Google Groups "Trac
Users" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/trac-users?hl=en.
