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

Arkadiusz Jakubas ajakubas at arces.net
Thu Mar 6 04:56:04 EST 2008


I extracted query which RT sends to database server.
Query "  'CF.{Approval}' LIKE '1. Pending' " which is in RT current search
field.



2008/3/5, Kenneth Crocker <KFCrocker at lbl.gov>:
>
> 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
>
>


-- 
Arkadiusz Jakubas
Arces Network, LLC
http://www.arces.net
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20080306/4e63d838/attachment.htm>


More information about the rt-users mailing list