| From: | "Nick Humphries" <nick.humphries [...] pipex.net> |
| Subject: | [Rt-devel] RT + SB GROUP BY/ORDER BY mix-up |
| Date: | Thu, 2 Jun 2005 16:32:08 +0100 |
| To: | <Rt-devel [...] lists.bestpractical.com> |
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) Theres no space between DESC and GROUP BY. The MySQL
interpreter subsequently falls over when it doesnt recognise the
DESCGROUP keyword.
One workaround Ive 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
Show quoted text
_______________________________________________
Rt-devel mailing list
Rt-devel@lists.bestpractical.com
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-devel