I have an RT 3.6.3 installation and a user reported the following
problem. When he tried to display standard fields and custom fields in
the results page for a search, if he tried to sort by the custom field
value and priority, the results page did not show any results (although
it did give the correct number of tickets matching the search). When I
looked at the logs, I found the following SQL query had an error:
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 ((CustomFields_2.Name =
'Release Status')) AND ((main.EffectiveId = main.id)) AND ((main.Status
!= 'deleted')) AND ((main.Type = 'ticket')) AND ((main.Queue = '5')AND (
(main.Status = 'new')OR(main.Status = 'stalled')OR(main.Status = 'open')
) AND(main.Priority < '3')AND ( ( (ObjectCustomFieldValues_3.Content !=
'Verified')OR(ObjectCustomFieldValues_3.Content IS NULL) ) ) ) GROUP
BY main.id ORDER BY main.Priority ASC, main.id ASC,
min(CustomFieldValues_4.SortOrder) ASC,
min(ObjectCustomFieldValues_3.Content) ASC ) distinctquery, Tickets
main WHERE (main.id = distinctquery.id) LIMIT 50
When I tried this query directly in PostgreSQL (8.1.5), it gave the
following error:
ERROR: column "main.priority" must appear in the GROUP BY clause or be
used in an aggregate function
Sure enough, if I added main.priority to the GROUP BY clause, the query
worked. We have version 1.43 of DBIx::SearchBuilder. Is this something
that might be fixed in a newer version of DBIx::SearchBuilder (I didn't
see anything obvious from the changelog), or are there any other
solutions to this problem?
Thanks,
Brian
_______________________________________________
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
Community help: http://wiki.bestpractical.com
Commercial support: [EMAIL PROTECTED]
Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
Buy a copy at http://rtbook.bestpractical.com
