[rt-devel] Help adding custom fields to search view

[tariq chaudhry]

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.