[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