[Rt-devel] RT + SB GROUP BY/ORDER BY mix-up

[Nick Humphries]

Hi there,

 

I recently hit a problem which others might have hit too.

 

I wanted to get a list of Ticket IDs for a given username where the user has
posted an update.

 

The code goes something like this:

 

  $TicketIDs = RT::Transactions->new($session{'CurrentUser'});

  $TicketIDs->Column(FIELD => 'Ticket');

  $TicketIDs->Limit(FIELD => 'Creator', VALUE => $UserObj->id);

  $TicketIDs->GroupByCols({FIELD => 'Ticket'});

  $TicketIDs->OrderBy(FIELD => 'Ticket', ORDER => 'desc');

 

So far so good, it should be obvious what the generated SQL query should be.
Run the code, look at rt.log, and this is what happens:

 

[Thu Jun  2 15:12:35 2005] [warning]: DBD::mysql::st execute failed: You
have an error in your SQL syntax.  Check the manual that corresponds to your
MySQL server version for the right syntax to use near 'DESCGROUP BY
main.Ticket' at line 1 at
/usr/lib/perl5/site_perl/5.8.4/DBIx/SearchBuilder/Handle.pm line 480.

 (/opt/rt3/lib/RT.pm:269)

[Thu Jun  2 15:12:35 2005] [warning]: RT::Handle=HASH(0x93094bc) couldn't
execute the query 'SELECT main.Ticket AS ticket FROM Transactions main
WHERE ((main.Creator = '160130')) ORDER BY main.Ticket DESCGROUP BY
main.Ticket ' at /usr/lib/perl5/site_perl/5.8.4/DBIx/SearchBuilder/Handle.pm
line 494.

 (/opt/rt3/lib/RT.pm:269)

 

 

Two things are apparent:

 

1) The GROUP BY clause is _after_ the ORDER BY clause. This is non-standard
SQL - grouping should come before sorting.

 

2) There's no space between "DESC" and "GROUP BY". The MySQL interpreter
subsequently falls over when it doesn't recognise the "DESCGROUP" keyword.

 

One workaround I've used until the new SB update is the following: swap the
two clauses in the SB object, and insert the extra space at the same time:

 

  my $t = $TicketIDs->{'group_clause'};

  $TicketIDs->{'group_clause'} = $TicketIDs->{'order_clause'};

  $TicketIDs->{'order_clause'} = $t;

  $TicketIDs->{'group_clause'} = " " . $TicketIDs->{'group_clause'};

 

This produces the correct SQL, but will of course break when the SB module
is patched!

 

Hope this helps someone - certainly took me a while to figure out!

 

Nick

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.bestpractical.com/pipermail/rt-devel/attachments/20050602/1ceb8898/attachment-0001.htm