TicketQuery with progress format should support grouping by custom fields

[josh@…]

I'm using ​SimpleMultiProjectPlugin, which creates a new custom field called "project". I would like to use TicketQuery to display tickets grouped by that field, but that doesn't seem to be supported. This generates an error:

[[TicketQuery(group=project,format=progress)]]

[anonymous]

trac/ticket/roadmap.py

@@ -349 +349 @@
                 group_names = field['options']
                 if field.get('optional'):
                     group_names.insert(0, '')
+            elif  field.get('custom'):
+                group_names = [name for name, in env.db_query("""
+                    SELECT DISTINCT c.value FROM ticket_custom c
+                    WHERE c.name=COALESCE('%s', '')
+                    ORDER BY c.value
+                    """ % (by, ))]
             else:
                 group_names = [name for name, in env.db_query("""
                     SELECT DISTINCT COALESCE(%s, '') FROM ticket

I'm not sure how to detect if a (none) group is needed.

[Jun Omae]

Reproduced. TicketQuery(format=progress) macro with grouping custom field leads the issue.

2014-02-08 10:28:22,513 Trac[util] DEBUG: SQL:
                    SELECT DISTINCT COALESCE(project, '') FROM ticket
                    ORDER BY COALESCE(project, '')

2014-02-08 10:28:22,515 Trac[util] DEBUG: execute exception: OperationalError('no such column: project',)
2014-02-08 10:28:22,517 Trac[formatter] ERROR: Macro TicketQuery(group=project,format=progress) failed:
Traceback (most recent call last):
  File "/home/jun66j5/venv/trac/1.0.1/lib/python2.5/site-packages/trac/wiki/formatter.py", line 765, in _macro_formatter
    return macro.ensure_inline(macro.process(args))
  File "/home/jun66j5/venv/trac/1.0.1/lib/python2.5/site-packages/trac/wiki/formatter.py", line 356, in process
    text = self.processor(text)
  File "/home/jun66j5/venv/trac/1.0.1/lib/python2.5/site-packages/trac/wiki/formatter.py", line 343, in _macro_processor
    text)
  File "/home/jun66j5/venv/trac/1.0.1/lib/python2.5/site-packages/trac/ticket/query.py", line 1375, in expand_macro
    per_group_stats_data)
  File "/home/jun66j5/venv/trac/1.0.1/lib/python2.5/site-packages/trac/ticket/roadmap.py", line 356, in grouped_stats_data
    """ % (by, by))]
  File "/home/jun66j5/venv/trac/1.0.1/lib/python2.5/site-packages/trac/db/api.py", line 122, in execute
    return db.execute(query, params)
  File "/home/jun66j5/venv/trac/1.0.1/lib/python2.5/site-packages/trac/db/util.py", line 121, in execute
    cursor.execute(query, params)
  File "/home/jun66j5/venv/trac/1.0.1/lib/python2.5/site-packages/trac/db/util.py", line 56, in execute
    r = self.cursor.execute(sql)
  File "/home/jun66j5/venv/trac/1.0.1/lib/python2.5/site-packages/trac/db/sqlite_backend.py", line 78, in execute
    result = PyFormatCursor.execute(self, *args)
  File "/home/jun66j5/venv/trac/1.0.1/lib/python2.5/site-packages/trac/db/sqlite_backend.py", line 56, in execute
    args or [])
  File "/home/jun66j5/venv/trac/1.0.1/lib/python2.5/site-packages/trac/db/sqlite_backend.py", line 48, in _rollback_on_error
    return function(self, *args, **kwargs)
OperationalError: no such column: project

[Peter Suter]

I'm not sure how to detect if a (none) group is needed.

I'm not that familiar with TracTicketsCustomFields. But Tickets created before a custom field has been defined will not have a value for that field. So it might be best to always add a (none) group. If it's not needed it will be skipped​ anyway.

[Peter Suter]

attachment:T11479-query-progress-group-custom.patch​ adds this and some testcases.

(Needs the patch from #10838​ for shared test infrastructure.)

[Jun Omae]

• We should use COALESCE() for ticket_custom.value.
• We could use DB API rather than query % args for custom fields.
• Using COALESCE('%s', '') is no sense….

trac/ticket/roadmap.py

@@ -350 +350 @@
                 group_names = field['options']
                 if field.get('optional'):
                     group_names.insert(0, '')
-            elif  field.get('custom'):
+            elif field.get('custom'):
                 group_names = [name for name, in env.db_query("""
-                    SELECT DISTINCT c.value FROM ticket_custom c
-                    WHERE c.name=COALESCE('%s', '')
-                    ORDER BY c.value
-                    """ % (by, ))]
+                    SELECT DISTINCT COALESCE(c.value, '') FROM ticket_custom c
+                    WHERE c.name=%s ORDER BY COALESCE(c.value, '')
+                    """, (by, ))]
                 if '' not in group_names:
                     group_names.insert(0, '')
             else:

[Peter Suter]

Thank you for the review and improvements.

Applied in [12617] and merged in [12618].