Displaying child tickets using TracQuery

[chrissy@…]

We have some wiki pages that use the ​TicketQuery Macro. When displaying a table with children set as one of the columns, the column displays, but it does not list any of the child tickets, it's just empty for each row.

Example Query:

[[TicketQuery(group=milestone,status=!closed,priority=major,format=table,col=id|summary|status|owner|component|children)]]

[Mark Ryan]

Hi Chrissy

I am afraid this is not possible with the childticketsplugin and ticketquery macro as the field 'children' is not a property of any ticket. The child tickets plugin only requires the extra field 'parent' and for the purposes of the plugin the child list is dynamically generated.

As you can use the 'parent' field in any query or in any query result you can clever with some SQL queries .... my SQL is not very good (I'm sure there's a better way to achieve this!), but you could do something like the following in a report:

SELECT p.value AS __color__,
   'Milestone '||milestone AS __group__,
   id AS ticket, summary, component, version, t.type AS type, 
   owner, status,
   time AS created,
   (SELECT group_concat(tc.ticket) FROM ticket_custom tc WHERE tc.name='parent' AND tc.value='#'||t.id) as children,
   changetime AS _changetime, 
   description AS _description,
   reporter AS _reporter
  FROM ticket t
  LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
  WHERE status <> 'closed' 
  ORDER BY (milestone IS NULL),milestone, CAST(p.value AS integer), t.type, time

I also tried to get some sort of wiki formatting to get links to the child tickets in the view, here I 'stole' the description column to get the effect (I'm sure if you look into the trac documentation you'll find loads of ways of achieving the same thing more efficiently ... )

SELECT p.value AS __color__,
   'Milestone '||milestone AS __group__,
   id AS ticket, summary, component, version, t.type AS type, 
   owner, status,
   time AS created,
   (SELECT group_concat('#'||tc.ticket) FROM ticket_custom tc WHERE tc.name='parent' AND tc.value='#'||t.id) as description,
   changetime AS _changetime, 
   reporter AS _reporter
  FROM ticket t
  LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
  WHERE status <> 'closed' 
  ORDER BY (milestone IS NULL),milestone, CAST(p.value AS integer), t.type, time

Another option would be to produce a report that used the 'group' function to produce lists of child tickets below each parent value:

SELECT 'Parent : '||tc.value AS __group__,
 tc.value AS parent,
 t.id,
 t.summary
FROM ticket_custom tc, ticket t
WHERE tc.ticket=t.id
ORDER BY (tc.value IS NULL),tc.value

I know none of these address the issue of the 'TicketQueryMacro' not being able to use the 'children' field (or lack of it!) but perhaps they help you achieve something like what you're after if you use them in combination with the 'SqlQueryMacro' ???

Good luck!

Regards Mark