[Rt-devel] Sortorder of customfields

[Joop van de Wege]

Hello,

I'm, actually a client of ours, having problems with the sortorder of
the customfields displayed on Ticket display and Asset Display.html
In Configuration the order is correctly displayed and in the database
the order is also correct.
At the moment our client is entering quite a bit of data, assets, and
last week they had the correct order but apparently they have added a
couple of types and maybe reordered a couple of customfields and now
they are seeing that the order is way off.
I can reproduce this weird behaviour by moving CF1 one entry down and
then back up, to the original position, in Configuration and when I go
to an asset with these customfields I can see that the order is
different then when I started this. In one particular case CF was
displayed LAST instead of FIRST.

After a bit of searching I found the query  which is responsible for
delivering the order to the element which displays it and there are
several problems with that query.
BTW this is RT-3.4.5, AT-1.2.3 using Oracle XE and DBIx::SearchBuilder v1.40,
complete config is available on request.
This is the query that is responsible for the display order:

SELECT main.*
  FROM (SELECT   main.ID
            FROM customfields main, objectcustomfields objectcustomfields_1
           WHERE (   (objectcustomfields_1.objectid = '3')
                  OR (objectcustomfields_1.objectid = '0')
                 )
             AND ((main.disabled = '0'))
             AND ((main.lookuptype =
                            'RTx::AssetTracker::Type-RTx::AssetTracker::Asset'
                  )
                 )
             AND ((main.ID = objectcustomfields_1.customfield))
        GROUP BY main.ID
        ORDER BY MIN (objectcustomfields_1.objectid) ASC,
                 MIN (objectcustomfields_1.sortorder) ASC) distinctquery,
       customfields main
 WHERE (main.ID = distinctquery.ID)

This will give you the customfields belonging to asset_type 3 in the
order as they are to be displayed, not.
Problem is the GROUP BY together with the MIN, removing those statements
get the correct sort order list BUT my dba tells me that there is no guarantee
that this will give you always the correct order. If the optimizer
decides to use the second table as leading then the order will change.
The solution apparently is the have an ORDER BY on the outer SELECT like
this:
SELECT main.*
  FROM (SELECT   main.ID
            FROM customfields main, objectcustomfields objectcustomfields_1
           WHERE (   (objectcustomfields_1.objectid = '3')
                  OR (objectcustomfields_1.objectid = '0')
                 )
             AND ((main.disabled = '0'))
             AND ((main.lookuptype =
                            'RTx::AssetTracker::Type-RTx::AssetTracker::Asset'
                  )
                 )
             AND ((main.ID = objectcustomfields_1.customfield))
        ORDER BY (objectcustomfields_1.objectid) ASC,
                    (objectcustomfields_1.sortorder) ASC) distinctquery,
       customfields main
 WHERE (main.ID = distinctquery.ID)
 order by (select distinct objectcustomfields_1.sortorder 
            FROM objectcustomfields objectcustomfields_1
           WHERE (   (objectcustomfields_1.objectid = '3')
                  OR (objectcustomfields_1.objectid = '0')
                 )
             AND ((main.disabled = '0'))
             AND ((main.lookuptype =
                            'RTx::AssetTracker::Type-RTx::AssetTracker::Asset'
                  )
                 )
             AND ((main.ID = objectcustomfields_1.customfield)))

This doesn't look right and can be simplified but I doubt that in itself
would be simple. This query is build in
DBIx/SearchBuilder/Handle/Oracle.pm, sub DistinctQuery()

Is there anyone who knows why that function is built that way because
I'm going to try to fix this but if there is some logic in there that is
not directly clear from it then it should be commented so that feature
problems can be solved more rapidly.

Thanks for any help anyone can offer on this,

Joop

-- 
Joop van de Wege <JoopvandeWege at mococo.nl>