[Rt-devel] SearchBuilder and sorting in Oracle

[Steen Olesen]

Hi,

I've been struggling with CustomFields in RT not being shown in the desired order, and I have now pinpointed where the problem apparently is.

As I see it, it is Oracle's query optimizer that prevents the DistinctQuery-SQL to return the rows in correct order. I have tweaked the generated SQL in DistinctQuery (Oracle.pm) and it now seems to give the correct result (at least my CustomFields are now shown in the right order :)). You may argue, that there is an overhead in my solution (two order bys), but I do not have enough in-depth knowledge of how the whole SearchBuilder works.

Anyway - you may adopt the solution or simply use it as inspiration :)

diff DBIx-SearchBuilder-1.54/SearchBuilder/Handle/Oracle.pm Oracle.pm
275c275,283
<         $$statementref = "SELECT main.* FROM ( SELECT main.id FROM $$statementref $group $order ) distinctquery, $table main WHERE (main.id = distinctquery.id)";
---
>
>         $$statementref = qq [
> SELECT main.* FROM
> ( SELECT rt_internal_table.id, rownum rt_internal_sort_order FROM
>       ( SELECT main.id FROM $$statementref $group $order ) rt_internal_table
> ) distinctquery, $table main
> WHERE (main.id = distinctquery.id) order by distinctquery.rt_internal_sort_order
> ];
>

Configuration used: Perl v5.8.8, Oracle 10.2.0.1.0, RT3.8.1 and SearchBuilder 1.54

Best regards

Steen Olesen
Schilling A/S
Baldersbækvej 24-26
DK-2635 Ishøj
Tel: +45 70 27 99 00
Fax: +45 70 27 99 10
Mailto:so at schilling.dk<mailto:so at schilling.dk>
http://www.schilling.dk<http://www.schilling.dk/>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.bestpractical.com/pipermail/rt-devel/attachments/20090212/e6dd05a2/attachment.htm