We are attempting to add a patch to RT which will show the custom
fields as columns in the search view. This was straightforward to add
but we are having trouble getting the sorting to work. By default RT
tries to sort on the custom field as if it were a column in the tickets
table which fails.
We attempted to add the ordering in a few locations in the code which
resulted in the code, mainly RT::Interface::Web and
RT::Tickets_Overlay, adding this using OrderBy caused it prepend the
alias ‘main’ creating fields to sort on like:
main.TicketCustomFieldValues.content since the appropriate table had
not yet been joined into the query.
We attempted to add a call to:
$self->OrderBy(
ALIAS => ‘TicketCustomFieldValues_1’,
FIELD => ‘content’,
ORDER => ‘DESC’,
);
In the sub _CustomFieldLimit in RT::Tickets_Overlay so that after
performing the join it would have the alias to perform the ordering.
The code above gets further but results in this error in the webserver
log:
[Fri Oct 17 19:32:56 2003] [warning]: DBIx::SearchBuilder error:ERROR:
For SELECT DISTINCT, ORDER BY expressions must appear in target list
Query String is SELECT DISTINCT main.* FROM (Tickets main LEFT
JOIN TicketCustomFieldValues as TicketCustomFieldValues_1 ON ( main.id
= TicketCustomFieldValues_1.Ticket) AND(
(TicketCustomFieldValues_1.CustomField = ‘6’))) WHERE
((main.EffectiveId = main.id)) AND ((main.Type = ‘ticket’)) AND ( ( (
(TicketCustomFieldValues_1.Content LIKE ‘%%Editor Server%%’) ) ) AND (
(main.Status = ‘new’)OR(main.Status = ‘open’) ) AND ( (main.Queue =
‘4’) ) ) ORDER BY TicketCustomFieldValues_1.content DESC LIMIT 50
This is obviously because the query selects main.* and attempts to
order by the ticketcustomfields table which is only present in the
subquery (LEFT JOIN …). The code above also only works if you have
built up a search query in the web interface with involves a custom
field. (Otherwise the _CustomFieldLimit isn’t called to construct the
search clause).
The short version of the issue is: we need to be able to join the
tickets and ticketcustomfieldvalues tables into every search query that
is executed and sort on columns in either table in that view. What is
the appropriate place to add this code?
Any assistance greatly appreciated. We would be happy to contribute the
patches back to the RT project once completed.
Thanks.