| Subject: | ::Handle::Pg->DistinctQuery Doesn't work right |
Using Group By to do the distinct query is a good try but this fails if
any of the Order By Criteria are in the main table because Postgres
requires that if there is a group by criteria it must include all of the
Order By fields, unless they are an aggregate function.
The following example fails because main.starts is not in an aggregate
function or in the Group By clause. If main.starts is added to the
Group By clause or wrapped with an aggregate function it works fine:
SELECT main.* FROM ( SELECT main.id FROM (((Tickets main LEFT JOIN
CustomFields CustomFields_1 ON ( CustomFields_1.Name = 'Severity'))
LEFT JOIN ObjectCustomFieldValues ObjectCustomFieldValues_2 ON (
ObjectCustomFieldValues_2.CustomField = CustomFields_1.id) AND (
(ObjectCustomFieldValues_2.Disabled = '0')) AND (
(ObjectCustomFieldValues_2.ObjectId = main.id)) AND (
(ObjectCustomFieldValues_2.ObjectType = 'RT::Ticket'))) LEFT JOIN
CustomFieldValues CustomFieldValues_3 ON (
CustomFieldValues_3.CustomField = ObjectCustomFieldValues_2.CustomField)
AND ( (CustomFieldValues_3.Name = ObjectCustomFieldValues_2.Content)))
WHERE ((main.EffectiveId = main.id)) AND ((main.Status != 'deleted'))
AND ((main.Type = 'ticket')) AND ((main.Status = 'open')AND(main.Queue =
'10')) GROUP BY main.id ORDER BY min(CustomFieldValues_3.SortOrder)
ASC, min(ObjectCustomFieldValues_2.SortOrder) ASC,
min(ObjectCustomFieldValues_2.Content) ASC, main.Starts ASC )
distinctquery, Tickets main WHERE (main.id = distinctquery.id) LIMIT 50
This is with RT-3.4.5, the BP code for the University of Washington, and
a bit more extra code on our part.