R: [rt-users] Chart grouped by customfield

[Emmanuel Lacour]

On Wed, Oct 10, 2007 at 05:46:31PM +0200, Gianluca Cecchi wrote:
> Sorry main was an alias for Tickets table....
> 
> If I use the same query on mysql 9without the comma...) I get:
> 
> ERROR 1054 (42S22): Unknown column 'ObjectCustomFieldValues_1.Content' in 'field list' 
> 
> And the oracle error 904 is "invalid column name" too
> 

Here is the SQL for the same Graph on a MySQL RT, gived by StatementLog (graph by CustomFields works with MySQL):

SELECT DISTINCT COUNT(main.id) AS id, ObjectCustomFieldValues_1.Content AS col1 FROM (Tickets main  LEFT JOIN ObjectCustomFieldValues ObjectCustomFieldValues_1  ON ((ObjectCustomFieldValues_1.CustomField = '1')) AND ( (ObjectCustomFieldValues_1.ObjectType = 'RT::Ticket')) AND ( (ObjectCustomFieldValues_1.Disabled = '0')) AND ( ObjectCustomFieldValues_1.ObjectId = main.id))   WHERE ((main.EffectiveId = main.id)) AND ((main.Status != 'deleted')) AND ((main.Type = 'ticket')) AND ((main.Created < '2007-10-31 23:00:00') AND (main.Created > '2007-09-29 22:00:00'))  GROUP BY ObjectCustomFieldValues_1.Content ;

and the one for the Oracle RT:

SELECT COUNT(main.id) AS id, ObjectCustomFieldValues_1.Content AS col1 FROM ( SELECT DISTINCT main.id FROM Tickets main LEFT JOIN ObjectCustomFieldValues ObjectCustomFieldValues_1  ON ( ObjectCustomFieldValues_1.CustomField = '1' ) AND ( ObjectCustomFieldValues_1.ObjectType = 'RT::Ticket' ) AND ( ObjectCustomFieldValues_1.Disabled = '0' ) AND ( ObjectCustomFieldValues_1.ObjectId = main.id ) WHERE (main.Status != 'deleted') AND (main.Status != 'resolved') AND (main.Type = 'ticket') AND (main.EffectiveId = main.id)  ) distinctquery, Tickets main WHERE (main.id = distinctquery.id)  GROUP BY ObjectCustomFieldValues_1.Content
;