has anybody the trac report to get all tickets without milestone?

Question

i'm looking for the TracQuery for the trac report to get all tickets without assigned milestone listed. have you already composed this query, if yes, would you share it? thx in advance.

Answer 1

I could reproduce the 'No matches found' for the answer by Paul Dixon, so I tried it on my own:

SELECT 
   id AS ticket,
   time AS date,
   summary,
   status,
   priority,
   description AS _description
  FROM ticket t
  WHERE t.milestone not in (
    SELECT name
      FROM milestone
    )
    AND t.status<>'closed'
  ORDER BY time desc, status, severity, summary

Note: Trac has some "magic" result table column names. Assigning 'date' will do the appropriate conversation of time stamp integer to date string automagically - even better - respecting the current internal Trac time stamp format of POSIX micro-seconds (since Trac 0.12) too. In this respect the SQL statement above is even the most portable solution I'm aware of.

Answer 2

This is quite simple using Trac's query language instead of raw SQL. On a wiki page, you can use query:milestone= to create a link to a query of all tickets without a milestone assigned. Invoke the macro [[TicketQuery(milestone=)]] to insert the list of matching tickets into the wiki page.

To do the same thing on the "Custom Query" page, remove all search criteria and add a single criteria of "milestone" "is" and leave the third field blank.

Answer 3

Something like this should do it, adapt to suit your precise needs....

SELECT 
   id AS ticket,
   datetime(time,'unixepoch'),
   summary,
   status,
   priority,
   description AS _description
  FROM ticket t
  WHERE t.milestone is NULL AND t.status<>'closed'
  ORDER BY time desc, status, severity, summary

The key part for you is simply to filter on the milestone being NULL.