Custom ticket query for new and reassigned tickets

Michael

unread,

May 23, 2012, 12:01:09 PM5/23/12

to trac-...@googlegroups.com

Hello everybody,

I hope to find some help here with custom ticket queries in sql for trac that will make my life easier ;)

First of all, we are using a hosted trac on hosted-projects.com. So my possibilities may be limited or some functionality may be missing but custom ticket queries in sql seem to work, although not fully as I would expect e.g. CURDATE() function is not working.

I would like to have two queries:

1) All my new tickets

When I try a custom query without sql with owner=michael and status=new, it just gives all the tickets that are assigned to me.

But what I consider new tickets are
- tickets that have me as the owner and there was no change in the ownership, so basically all tickets that are created with me as the owner and are not touched again
or
- tickets that I own that have been assigned to me for the first time (which means that I have not been owner of the ticket before)

2) All tickets reassigned to me

This query should show me tickets that I own and have been assigned back to me, which means that I have been owner of the ticket before.

I think that for these queries I have to join the ticket and ticket_change table somehow, but that is exactly where I am stuck...

Can someone help me with these sql queries?

Regards,
Michael

Benjamin Lau

unread,

May 23, 2012, 12:19:52 PM5/23/12

to trac-...@googlegroups.com

On Wed, May 23, 2012 at 9:01 AM, Michael <michael...@googlemail.com> wrote:
> Hello everybody,
>
> I hope to find some help here with custom ticket queries in sql for trac
> that will make my life easier ;)
>
> First of all, we are using a hosted trac on hosted-projects.com. So my
> possibilities may be limited or some functionality may be missing but custom
> ticket queries in sql seem to work, although not fully as I would expect
> e.g. CURDATE() function is not working.

This triggered something in the back of my mind...
http://stackoverflow.com/questions/1991697/mysql-date-and-time-functions-dont-exist

I'm guessing they're running sqlite not mysql as the DB backend for
Trac. But that should help you with composing queries better.

I don't use the custom sql reports in trac usually... I've generally
tried to make do with the capabilities of the TicketQuery macro
embedded in a wiki page for my needs regarding reporting. What I have
setup looks like this:
== My Tickets ==

{{{#!td style="vertical-align: top; width: 33%;" align=center
[[TicketQuery(owner=$USER,status!~=closed,list,group=priority)]]
}}}
{{{#!td style="vertical-align: top; width: 33%;" align=center
[[TicketQuery(owner=$USER,status=in_QA,list)]]
}}}
{{{#!td style="vertical-align: top; width: 33%;" align=center
[[TicketQuery(owner=$USER,status=closed,list)]]
}}}

And let's me at a glance see my open tickets, ones that have been
assigned to QA for assessment and my closed tickets. I usually key
this additional by whatever milestone I'm currently working towards...
but this example is from a test Trac instance I had set up where I'm
not tracking a real project... so it doesn't have any milestones.
Maybe you could get the ordering you want by sorting with output using
the order field set to time (create) or changetime (modified).

Ben

> I would like to have two queries:
>
> 1) All my new tickets
>
> When I try a custom query without sql with owner=michael and status=new, it
> just gives all the tickets that are assigned to me.
>
> But what I consider new tickets are
> - tickets that have me as the owner and there was no change in the
> ownership, so basically all tickets that are created with me as the owner
> and are not touched again
> or
> - tickets that I own that have been assigned to me for the first time (which
> means that I have not been owner of the ticket before)
>
> 2) All tickets reassigned to me
>
> This query should show me tickets that I own and have been assigned back to
> me, which means that I have been owner of the ticket before.
>
> I think that for these queries I have to join the ticket and ticket_change
> table somehow, but that is exactly where I am stuck...
>
> Can someone help me with these sql queries?
>
> Regards,
> Michael
>
>
>

> --
> You received this message because you are subscribed to the Google Groups
> "Trac Users" group.
> To view this discussion on the web visit
> https://groups.google.com/d/msg/trac-users/-/6_ykxdkm8jQJ.
> To post to this group, send email to trac-...@googlegroups.com.
> To unsubscribe from this group, send email to
> trac-users+...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/trac-users?hl=en.

Michael

unread,

Jul 3, 2012, 10:49:08 AM7/3/12

to trac-...@googlegroups.com

Thanks Ben, and sorry for the late answer. Unfortunately what I wanted only works with a custom SQL query. But I had some time lately to work on this and I finally came up with 2 queries that do the work for me. They are tailored to our use of trac - e.g. grouping by milestone or taking into account that people usually don't use the "accept" action - but still might be useful for others, so I am sharing them here:

1) My Tickets: returned

This query shows tickets that have been returned to you. Either you have created these tickets and assigned them to someone else and they have been assigned back to you or you have just owned them before and they have been assigned back to you.

SELECT p.value AS __color__,
t.milestone AS __group__,
'../milestone/' || t.milestone AS __grouplink__,
t.id AS ticket,
t.time AS created,
t.changetime AS modified,
t.summary,
t.type,
t.priority,
t.component,
t.reporter
FROM ticket t, enum p
WHERE t.status IN ('new', 'assigned', 'reopened')
AND t.owner = $USER
AND p.name = t.priority
AND p.type = 'priority'
AND (
    -- tickets that have been owned by the user, assigned to someone and assigned back
    t.id IN (
      SELECT DISTINCT tc.ticket
      FROM ticket_change tc
      WHERE tc.field = 'owner' AND tc.oldvalue = $USER
    )
    OR (
    -- tickets that have been assigned directly to someone else and returned now
      t.reporter = $USER
      AND t.id IN (
        SELECT DISTINCT tc.ticket
        FROM ticket_change tc
        WHERE tc.field = 'owner' AND tc.newvalue = $USER
      )
    )
)
ORDER BY t.milestone DESC, p.value, modified DESC

2) My Tickets: new untouched

This query shows tickets, that are assigned to you, but you didn't touch them yet (no change or comment by you).

SELECT p.value AS __color__,
t.milestone AS __group__,
'../milestone/' || t.milestone AS __grouplink__,
t.id AS ticket,
t.time AS created,
t.changetime AS modified,
t.summary,
t.type,
t.priority,
t.component,
t.reporter
FROM ticket t, enum p
WHERE t.status IN ('new', 'assigned', 'reopened')
AND t.owner = $USER
AND t.reporter <> $USER
AND p.name = t.priority
AND p.type = 'priority'
AND
    -- there is no ticket_change by you, means no comment or any change, basically you didn't touch them
    t.id NOT IN (
      SELECT DISTINCT tc.ticket
      FROM ticket_change tc
      WHERE tc.author = $USER
    )
ORDER BY t.milestone DESC, p.value, modified DESC

Maybe they are useful for someone.

Cheers!
Michael

On Wednesday, May 23, 2012 6:19:52 PM UTC+2, netjunki wrote:

> trac-users+unsubscribe@googlegroups.com.

> For more options, visit this group at
> http://groups.google.com/group/trac-users?hl=en.