[rt-users] sorting by requestor or custom field

[Eliezer Weiss]

Using postgres 8.3.1 and RT 3.8.2

Hi I have a very odd problem - I am working on a develpment branch of our
software in which the sort by requestors or by custom fields in the search
results table stopped working.

I researched it for a while and i compared it with an older version of our
software, extracting the SQL query after OrderByCols call and  I saw this
query:

SELECT main.*
FROM ( SELECT main.id
             FROM Tickets main
               JOIN Groups Groups_1
                    ON ( Groups_1.Domain = 'RT::Ticket-Role' ) AND
                          ( Groups_1.Type = 'Requestor' )            AND
                          ( Groups_1.Instance = main.id )
               LEFT JOIN CachedGroupMembers CachedGroupMembers_2
                    ON ( CachedGroupMembers_2.GroupId !=
CachedGroupMembers_2.MemberId ) AND
                          ( CachedGroupMembers_2.GroupId = Groups_1.id )
               LEFT JOIN Users Users_3
                    ON ( Users_3.id = CachedGroupMembers_2.MemberId )
               WHERE (main.Status !=
'deleted')                                 AND
                            (main.Queue = '1'   AND ( main.Queue = '1' ) )
AND
                            (main.Type =
'ticket')                                       AND
                            (main.EffectiveId = main.id)
               GROUP BY main.id
               ORDER BY MIN(Users_3.EmailAddress) ASC
             ) distinctquery, Tickets main
WHERE (main.id = distinctquery.id)

trying this query on the older version worked fine (and when I make the
order DESC it change the results) while on the devel version it doesn't
matter it just won't order it for me.

I suspected that the problem is that the order is inside the subquery and
AFAIK (and asked in postgresql IRC) join doesn't keep the order.
I wonder if it worked only by accident or if I am missing something
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20100818/ae4babc8/attachment.htm>