[rt-users] Sort Order of Custom Fields in rt 3.6.1

[Joop]

Hello Ole,


  > On Thu, 2006-09-28 at 14:46 -0400, Jesse Vincent wrote:
>>>>> So i came back to 3.6.0 and paradoxically the problem still exists!
>>>> I'm betting that this is due to an upgraded DBIx::SearchBuilder.  Just
>>>> out of curiosity, what database are you on?
>>> Our RT Installation uses PostgreSQL 8.0.3.
>>>
> [...]
>> Ok. Yes, this is a known bug (and one that it turns out is going to be a
>> real pain to fix)
> 
> Jesse -
>         Is that the case for all combinations? I'm running into a
> similar CF sort problem with MySQL 4.1 and 3.6.0.
> (DBIx-SearchBuilder-1.43).

I wrote the following to rt-devel last april:

===
Hello All,

I have been reading up on SELECTs and ORDER BY and GROUP BY and having a
chat with my DBA and we have come to the conclusion that the following
piece of code from DBIx/SearchBuilder/Handle/Oracle.pm is plain wrong.
sub DistinctQuery {
     my $self = shift;
     my $statementref = shift;
     my $sb = shift;
     my $table = $sb->Table;

     # Wrap select query in a sub select as Oracle doesn't allow
     # DISTINCT against CLOB/BLOB column types.
     if ($sb->_OrderClause =~ /(?<!main)\./) {
         # If we are ordering by something not in 'main', we need to GROUP
         # BY and adjust the ORDER_BY accordingly
         local $sb->{group_by} = [@{$sb->{group_by} || []}, {FIELD => 
'id'}];
         local $sb->{order_by} = [map {($_->{ALIAS} and $_->{ALIAS} ne 
"main") ? {%{$_}, FIELD => "min(".$_->{FIELD}.")"}: $_} @{$sb->{order_by}}];
         my $group = $sb->_GroupClause;
         my $order = $sb->_OrderClause;
         $$statementref = "SELECT main.* FROM ( SELECT main.id FROM 
$$statementref $group $order ) distinctquery, $table main WHERE (main.id 
= distinctquery.id)";
     } else {
         $$statementref = "SELECT main.* FROM ( SELECT DISTINCT main.id 
FROM $$statementref ) distinctquery, $table main WHERE (main.id = 
distinctquery.id) ";
         $$statementref .= $sb->_GroupClause;
         $$statementref .= $sb->_OrderClause;
     }
}

We have no clue whatsoever where that comment about orderby not being
the main table is going to need a groupby.
This is at least not true for Oracle 9i, never has been and can be
removed.
This solves the ordering problem with customfields with one side note
that to be 100% sure that you get the desired order you would need to
have the sub select in the order by clause, as explained in the previous
post.

By looking at the svn repository I can see that 4254 is the last change
and it was introduced then. Looks like it is verbatim copied from Pg.pm
where this might be true.

Joop

===

I have gone ahead and modified SearchBuilder todo what I described and 
the customer so far is very pleased ;-)

Jesse did indicate that Alex made the change in the Progress part of SB 
and it looked like it was copied verbatim to the other database drivers, 
at least it was copied to Oracle.pm, don't know about MySQL.
The relevant SVN change is: 4254

Hope this helps for you all,

Joop