On Wed, May 23, 2012 at 9:01 AM, Michael <[email protected]> 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 [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. -- 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.
