[Rt-devel] SearchBuilder and sorting in Oracle

[Jesse Vincent]

Steen, I've bounced this into the RT bugs queue for evaluation. Thanks!


On Thu 12.Feb'09 at 11:37:02 +0100, Steen Olesen wrote:
>    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
>    J). 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 J
> 
> 
> 
>    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
>    [1]Mailto:so at schilling.dk
>    [2]http://www.schilling.dk
> 
> References
> 
>    Visible links
>    1. mailto:so at schilling.dk
>    2. http://www.schilling.dk/

> _______________________________________________
> List info: http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-devel


-- 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 194 bytes
Desc: not available
Url : http://lists.bestpractical.com/pipermail/rt-devel/attachments/20090212/90d30b40/attachment.pgp