[rt-users] 0 tickets found when using custom fields

Arkadiusz Jakubas Thu, 27 Mar 2008 03:48:39 -0700

I didn't get any useful help from mailing list .

RT 3.6.3


I extracted sql query ( 'CF.{Approval}' LIKE '1. Pending' ) :

SELECT COUNT(DISTINCT main.id) FROM (((Tickets main LEFT JOINObjectCustomFields ObjectCustomFields_1 ON((ObjectCustomFields_1.ObjectId = '0'))AND( ObjectCustomFields_1.ObjectId = main.Queue)) LEFT JOINCustomFields CustomFields_2 ON ( CustomFields_2.id =ObjectCustomFields_1.CustomField)) LEFT JOIN ObjectCustomFieldValuesObjectCustomFieldValues_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'))) WHERE((CustomFields_2.Name = 'Approval')) AND ((main.EffectiveId =main.id)) AND ((main.Status != 'deleted')) AND ((main.Type ='ticket')) AND ( ( (ObjectCustomFieldValues_3.Content LIKE '%1. Pending%') ) )


result  :
+-------------------------+
| COUNT(DISTINCT main.id) |
+-------------------------+
|                       0 |
+-------------------------+

Then i modified query a little removed:
(ObjectCustomFieldValues_3.Content LIKE '%1. Pending%')
and
(CustomFields_2.Name = 'Approval'))

changed from:
SELECT COUNT(DISTINCT main.id)
to
SELECT *

query:

SELECT * FROM (((Tickets main LEFT JOIN ObjectCustomFieldsObjectCustomFields_1 ON ((ObjectCustomFields_1.ObjectId = '0'))AND( ObjectCustomFields_1.ObjectId = main.Queue)) LEFT JOINCustomFields CustomFields_2 ON ( CustomFields_2.id =ObjectCustomFields_1.CustomField)) LEFT JOIN ObjectCustomFieldValuesObjectCustomFieldValues_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'))) WHERE((main.EffectiveId = main.id)) AND ((main.Status != 'deleted')) AND((main.Type = 'ticket')) order by main.LastUpdated desc limit 100 ;



some result:

| id | EffectiveId | Queue | Type | IssueStatement | Resolution |Owner | Subject | InitialPriority | FinalPriority |Priority | TimeEstimated | TimeWorked | Status | TimeLeft |Told | Starts | Started |Due | Resolved | LastUpdatedBy |LastUpdated | Creator | Created | Disabled | id| CustomField | ObjectId | SortOrder | Creator | Created |LastUpdatedBy | LastUpdated | id | Name | Type | Description |SortOrder | Creator | Created | LastUpdatedBy | LastUpdated | Disabled| LookupType | Repeated | Pattern | MaxValues | id | ObjectId |CustomField | Content | Creator | Created | LastUpdatedBy |LastUpdated | ObjectType | LargeContent | ContentType |ContentEncoding | SortOrder | Disabled |+-------+-------------+-------+--------+----------------+------------+-------+---------------------------+-----------------+---------------+----------+---------------+------------+--------+----------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------+---------------------+---------+---------------------+----------+------+-------------+----------+-----------+---------+---------+---------------+-------------+------+------+------+-------------+-----------+---------+---------+---------------+-------------+----------+------------+----------+---------+-----------+------+----------+-------------+---------+---------+---------+---------------+-------------+------------+--------------+-------------+-----------------+-----------+----------+| 22285 | 22285 | 6 | ticket | 0 | 0 |91191 | Juniper | 20 | 39 | 22| 0 | 0 | open | 0 | 2008-02-14 08:24:01| 1970-01-01 00:00:00 | 1970-01-01 00:00:00 | 2008-02-24 01:13:50 |1970-01-01 00:00:00 | 620 | 2008-02-14 08:24:01 | 50067 |2008-02-13 20:18:20 | 0 | NULL | NULL | NULL |NULL | NULL | NULL | NULL | NULL | NULL | NULL |NULL | NULL | NULL | NULL | NULL | NULL |NULL | NULL | NULL | NULL | NULL | NULL |NULL | NULL | NULL | NULL | NULL | NULL |NULL | NULL | NULL | NULL | NULL |NULL | NULL | NULL || 22269 | 22269 | 53 | ticket | 0 | 0 |93603 | Account | 10 | 29 | 19| 0 | 30 | open | 0 | 2008-02-13 10:04:11| 1970-01-01 00:00:00 | 1970-01-01 00:00:00 | 2008-02-17 16:01:13 |1970-01-01 00:00:00 | 5786 | 2008-02-14 07:00:43 | 93260 |2008-02-12 16:01:13 | 0 | NULL | NULL | NULL |NULL | NULL | NULL | NULL | NULL | NULL | NULL |NULL | NULL | NULL | NULL | NULL | NULL |NULL | NULL | NULL | NULL | NULL | NULL |NULL | NULL | NULL | NULL | NULL | NULL |NULL | NULL | NULL | NULL | NULL |NULL | NULL | NULL || 22286 | 22286 | 47 | ticket | 0 | 0 |50067 | Server reboot | 10 | 29| 14 | 0 | 60 | open | 0 |2008-02-14 04:13:11 | 1970-01-01 00:00:00 | 1970-01-01 00:00:00 |2008-02-19 02:50:52 | 1970-01-01 00:00:00 | 5786 | 2008-02-1407:00:30 | 96040 | 2008-02-14 02:50:52 | 0 | NULL |NULL | NULL | NULL | NULL | NULL | NULL |NULL | NULL | NULL | NULL | NULL | NULL | NULL |NULL | NULL | NULL | NULL | NULL |NULL | NULL | NULL | NULL | NULL | NULL | NULL| NULL | NULL | NULL | NULL | NULL |NULL | NULL | NULL | NULL | NULL |



Is this some kind of bug ?  There shouldn't be so many NULLs
--
Arkadiusz Jakubas
Arces Network, LLC
http://www.arces.net

_______________________________________________
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

[rt-users] 0 tickets found when using custom fields

Reply via email to