Edgewall Software

Opened 7 years ago

Closed 7 years ago

#11479 closed defect (fixed)

TicketQuery with progress format should support grouping by custom fields

Reported by: josh@… Owned by: Peter Suter
Priority: normal Milestone: 1.0.2
Component: query system Version: 1.0.1
Severity: normal Keywords: custom field progress
Cc: fbrettschneider@… Branch:
Release Notes:

Fixed error in TicketQuery(format=progress) macro when grouping by a custom field.

API Changes:
Internal Changes:


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:


Attachments (1)

T11479-query-progress-group-custom.patch (6.2 KB ) - added by Peter Suter 7 years ago.

Download all attachments as: .zip

Change History (10)

comment:1 by fbrettschneider@…, 7 years ago

Cc: fbrettschneider@… added

comment:2 by anonymous, 7 years ago

  • trac/ticket/roadmap.py

    diff -r 3ad9422caa1d trac/ticket/roadmap.py
    a b  
    349349                group_names = field['options']
    350350                if field.get('optional'):
    351351                    group_names.insert(0, '')
     352            elif  field.get('custom'):
     353                group_names = [name for name, in env.db_query("""
     354                    SELECT DISTINCT c.value FROM ticket_custom c
     355                    WHERE c.name=COALESCE('%s', '')
     356                    ORDER BY c.value
     357                    """ % (by, ))]
    352358            else:
    353359                group_names = [name for name, in env.db_query("""
    354360                    SELECT DISTINCT COALESCE(%s, '') FROM ticket

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

comment:3 by Jun Omae, 7 years ago

Component: generalquery system
Keywords: custom field progress added
Milestone: next-stable-1.0.x
Summary: TicketQuery should support grouping by custom fieldsTicketQuery with progress format should support grouping by custom fields
Type: enhancementdefect

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
  File "/home/jun66j5/venv/trac/1.0.1/lib/python2.5/site-packages/trac/ticket/query.py", line 1375, in expand_macro
  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

comment:4 by Peter Suter, 7 years ago

Milestone: next-stable-1.0.x1.0.2

in reply to:  2 comment:5 by Peter Suter, 7 years ago

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.

comment:6 by Peter Suter, 7 years ago

Owner: set to Peter Suter
Status: newassigned

by Peter Suter, 7 years ago

comment:7 by Peter Suter, 7 years ago

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

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

comment:8 by Jun Omae, 7 years ago

  • 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

    diff --git a/trac/ticket/roadmap.py b/trac/ticket/roadmap.py
    index 8fa295f..9c4b425 100644
    a b def grouped_stats_data(env, stats_provider, tickets, by, per_group_stats_data):  
    350350                group_names = field['options']
    351351                if field.get('optional'):
    352352                    group_names.insert(0, '')
    353             elif  field.get('custom'):
     353            elif field.get('custom'):
    354354                group_names = [name for name, in env.db_query("""
    355                     SELECT DISTINCT c.value FROM ticket_custom c
    356                     WHERE c.name=COALESCE('%s', '')
    357                     ORDER BY c.value
    358                     """ % (by, ))]
     355                    SELECT DISTINCT COALESCE(c.value, '') FROM ticket_custom c
     356                    WHERE c.name=%s ORDER BY COALESCE(c.value, '')
     357                    """, (by, ))]
    359358                if '' not in group_names:
    360359                    group_names.insert(0, '')
    361360            else:

comment:9 by Peter Suter, 7 years ago

Release Notes: modified (diff)
Resolution: fixed
Status: assignedclosed

Thank you for the review and improvements.

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

Modify Ticket

Change Properties
Set your email in Preferences
as closed The owner will remain Peter Suter.
The resolution will be deleted. Next status will be 'reopened'.
to The owner will be changed from Peter Suter to the specified user.

Add Comment

E-mail address and name can be saved in the Preferences .
Note: See TracTickets for help on using tickets.