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

Kenneth Crocker KFCrocker at lbl.gov
Wed Mar 5 13:23:26 EST 2008


Arkadiusz,


	Is this query you are writing in RT/Ticket SQL or native SQL? I use RT 
query with custom fields all the time for reporting and have no problem 
at all. I also use native SQL for other queries on our Oracle DataBase. 
Using native SQL against the DataBase, however, requires some finesse 
when trying to get certain data. For example, to get the value of a CF 
that is applied to tickets in a queue I could use the Ticket ID to go to 
the OBJECTCUSTOMFIELDVALUES, make sure it is a ticket CF and not 
disabled and use the CONTENT from OBJECTCUSTOMFIELDVALUES with any other 
Ticket data for my report. I don't see a need for all those joins. But 
hey, that's just me. Hope this helps.

Kenn
LBNL

On 3/5/2008 12:45 AM, Arkadiusz Jakubas wrote:
> It this some kind of bug ?
> 
> 
> 2008/2/14, Arkadiusz Jakubas <ajakubas at arces.net 
> <mailto:ajakubas at arces.net>>:
> 
>     I extracted sql query ( 'CF.{Approval}' LIKE '1. Pending' ) :
> 
>     SELECT COUNT(DISTINCT main.id <http://main.id>) FROM (((Tickets
>     main  LEFT JOIN ObjectCustomFields ObjectCustomFields_1  ON
>     ((ObjectCustomFields_1.ObjectId = '0')) AND( 
>     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 <http://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 <http://main.id>)) AND ((main.Status != 'deleted')) AND
>     ((main.Type = 'ticket')) AND ( ( (ObjectCustomFieldValues_3.Content
>     LIKE '%1. Pending%') ) )
> 
>     result  :
>     +-------------------------+
>     | COUNT(DISTINCT main.id <http://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 <http://main.id>)
>     to
>     SELECT *
> 
>     query:
>     SELECT * FROM (((Tickets main  LEFT JOIN ObjectCustomFields
>     ObjectCustomFields_1  ON ((ObjectCustomFields_1.ObjectId = '0'))
>     AND(  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 <http://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 <http://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-14 07: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: sales at bestpractical.com
> 
> 
> Discover RT's hidden secrets with RT Essentials from O'Reilly Media. 
> Buy a copy at http://rtbook.bestpractical.com




More information about the rt-users mailing list