Skip Menu |

This queue is for tickets about the DBIx-SearchBuilder CPAN distribution.

Report information
The Basics
Id: 13060
Status: resolved
Priority: 0/
Queue: DBIx-SearchBuilder

Requestors: nick.humphries [...]

Bug Information
Severity: (no value)
Broken in: (no value)
Fixed in: (no value)

From: "Nick Humphries" <nick.humphries [...]>
Subject: [Rt-devel] RT + SB GROUP BY/ORDER BY mix-up
Date: Thu, 2 Jun 2005 16:32:08 +0100
To: <Rt-devel [...]>
Download (untitled) / with headers
text/plain 2.3k
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/ line 480. (/opt/rt3/lib/ [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/ line 494. (/opt/rt3/lib/ 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 Show quoted text
_______________________________________________ Rt-devel mailing list
Download (untitled) / with headers
text/plain 293b
Hi Nick. Thanks for reporting this. It should be fixed in the current repository as of revision 3236, and it will be rolled into a future release of DBIx::SearchBuilder. -- --dave Code Monkey, Best Practical Solutions -- David Glasser |

This service is sponsored and maintained by Best Practical Solutions and runs on infrastructure.

Please report any issues with to