[rt-users] 3.6.3 - sorting on Custom Fields still not working?

[Joop]

Joop wrote:
> OK. RT-3.6.3 has the possibility to log sql statements. Please turn this 
> on and try to find the query which is not producing the correct results. 
> We might be talking about different things.
> I'll do the same in the clients RT with patch and without, hoping they 
> don't mind the slight interruption.
> The problem that we were facing is that you start with entering CF's and 
> ordering is fine until either you delete some CF's or start moving them 
> around and then suddenly the ordering isn't correct any more, the reason 
> is that Oracle gets its rows, probably, in entered order until you 
> disturb it by deleted records. This makes it hard to reproduce in a test 
> environment by entering CF's and looking whether they come out in order 
> or not.

The patch that I send to Ruslan is meant to fix the ordering on, for 
example, the customfields which are displayed when looking at a ticket. 
In my case we discovered this problem because we use AssetTracker alot 
with lots of customfields and suddenly they started being displayed in a 
different order. I'm talking about this page:
http://localhost/rt3/AssetTracker/Asset/ModifyFields.html?id=163 and the 
query from this is:
  SELECT main.*
   FROM (SELECT   main.ID
             FROM customfields main, objectcustomfields objectcustomfields_1
            WHERE (   (objectcustomfields_1.objectid = '2')
                   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)

And when using the original Oracle.pm its:
SELECT main.*
   FROM (SELECT   main.ID
             FROM customfields main, objectcustomfields objectcustomfields_1
            WHERE (   (objectcustomfields_1.objectid = '2')
                   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)

Notice the extra GROUP BY main.ID and ORDER BY MIN(..) statements.

When I perform a search like this, ticket SQL:
Queue = 'Purmerend' AND 'CF.{Soort Hulp}' LIKE 'Eerste lijn'
I get an Oracle SQL statement about the same as you but not quite so I'm 
not sure this is the correct search that I'm doing.
This is what I get:
SELECT *
   FROM (SELECT limitquery.*, ROWNUM limitrownum
           FROM (SELECT   main.*
                     FROM (SELECT DISTINCT main.ID
                                      FROM (((tickets main LEFT JOIN 
objectcustomfields objectcustomfields_1
                                           ON 
((objectcustomfields_1.objectid =
 
     '0'
                                               )
                                              )
                                          OR 
(objectcustomfields_1.objectid =
 
main.queue
                                             ))
                                           LEFT JOIN
                                           customfields customfields_2
                                           ON (customfields_2.ID =
 
objectcustomfields_1.customfield
                                              ))
                                           LEFT JOIN
                                           objectcustomfieldvalues 
objectcustomfieldvalues_3
                                           ON 
((objectcustomfieldvalues_3.objectid =
 
main.ID
                                               )
                                              )
                                         AND 
(objectcustomfieldvalues_3.customfield =
 
customfields_2.ID
                                             )
                                         AND 
((objectcustomfieldvalues_3.disabled =
 
     '0'
                                              )
                                             )
                                         AND 
((objectcustomfieldvalues_3.objecttype =
 
'RT::Ticket'
                                              )
                                             ))
                                     WHERE ((customfields_2.NAME = 
'Soort Hulp'
                                            )
                                           )
                                       AND ((main.effectiveid = main.ID))
                                       AND ((main.status != 'deleted'))
                                       AND ((main.TYPE = 'ticket'))
                                       AND (    (main.queue = '22')
                                            AND 
((objectcustomfieldvalues_3.content LIKE
 
'Eerste lijn'
                                                 )
                                                )
                                           )) distinctquery,
                          tickets main
                    WHERE (main.ID = distinctquery.ID)
                 ORDER BY main.ID DESC) limitquery
          WHERE ROWNUM <= 50)
  WHERE limitrownum >= 1

And this is your query:
SELECT *
   FROM (SELECT limitquery.*, ROWNUM limitrownum
           FROM (SELECT main.*
                   FROM (SELECT   main.ID
                             FROM ((((tickets main LEFT JOIN 
objectcustomfields objectcustomfields_1
                                  ON ((objectcustomfields_1.objectid = '0'))
                                 OR (objectcustomfields_1.objectid = 
main.queue
                                    ))
                                  LEFT JOIN
                                  customfields customfields_2
                                  ON (customfields_2.ID =
 
objectcustomfields_1.customfield
                                     ))
                                  LEFT JOIN
                                  objectcustomfieldvalues 
objectcustomfieldvalues_3
                                  ON ((objectcustomfieldvalues_3.objectid =
 
main.ID
                                      )
                                     )
                                AND (objectcustomfieldvalues_3.customfield =
 
customfields_2.ID
                                    )
                                AND ((objectcustomfieldvalues_3.disabled 
= '0'
                                     )
                                    )
                                AND ((objectcustomfieldvalues_3.objecttype =
 
'RT::Ticket'
                                     )
                                    ))
                                  LEFT JOIN
                                  customfieldvalues customfieldvalues_4
                                  ON ((customfieldvalues_4.NAME =
 
objectcustomfieldvalues_3.content
                                      )
                                     )
                                AND (customfieldvalues_4.customfield =
 
objectcustomfieldvalues_3.customfield
                                    ))
                            WHERE ((main.effectiveid = main.ID))
                              AND ((main.status != 'deleted'))
                              AND ((main.TYPE = 'ticket'))
                              AND (((main.owner = '86')))
                         GROUP BY main.ID
                         ORDER BY MIN (customfieldvalues_4.sortorder) ASC,
                                  MIN 
(objectcustomfieldvalues_3.content) ASC) distinctquery,
                        tickets main
                  WHERE (main.ID = distinctquery.ID)) limitquery
          WHERE ROWNUM <= 50)
  WHERE limitrownum >= 1;

Could you post the TicketSQL for your search?
You can get this from the QueryBuilder page and than 'Advanced'

Thanks in advance,

Joop