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

[Joe Casadonte]

On 2/13/2007 3:14 AM, Joop wrote:

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

With this data:

CF1 = Select one text field (One, Two, Three, Four, Five)
CF2 = numeric

  #   CF1     CF2
===  ======  =====
  1   Four    0.1
  2   Two     0.1
  3   Five    0.1
21   Two     23
22   One     23
41   Three   3
42   Five    0
55   Two     0.5
56   Two     0

Running this query:

SELECT id
  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'))
			  AND  ((main.Status = 'new')
					OR (main.Status = 'open')))
		 GROUP BY main.id
		 ORDER BY min(CustomFieldValues_4.SortOrder) DESC,
		          min(ObjectCustomFieldValues_3.Content) DESC) distinctquery,
	  Tickets main
	  WHERE (main.id = distinctquery.id)) limitquery
    WHERE rownum <= 10)
  WHERE limitrownum >= 1;

Results in:

  #   CF1     CF2
===  ======  =====
  3   Five    0.1
42   Five    0
  1   Four    0.1
41   Three   3
21   Two     23
55   Two     0.5
  2   Two     0.1
56   Two     0
22   One     23

I confirmed that the same query run in SQL*Plus returns the same row 
order.  Is this the "Ticket SQL" you were looking for:

  Owner = '86' AND ( Status = 'new' OR Status = 'open')

Thanks for the help!

-- 
Regards,


joe
Joe Casadonte
joe.casadonte at oracle.com