[rt-users] Searching Custom Fields

[Chris Valdez]

When I run this query from the command line I get:

ERROR 1064 (00000): You have an error in your SQL syntax near 'ON (
CustomFields_2.id = ObjectCustomFieldValues_1.CustomField) AND( (CustomFiel'
at line 1

Thanks again,

-cv

-----Original Message-----
From: Chris Valdez [mailto:rpmonkey at gmail.com]
Sent: Monday, October 10, 2005 10:37 AM
To: rt-users at lists.bestpractical.com
Subject: RE: [rt-users] Searching Custom Fields


Here it is:

SELECT DISTINCT main.* FROM ((Tickets main  LEFT JOIN
ObjectCustomFieldValues ObjectCustomFieldValues_1  ON
((ObjectCustomFieldValues_1.ObjectType = 'RT::Ticket')) AND(
(ObjectCustomFieldValues_1.Disabled = '0')) AND(
ObjectCustomFieldValues_1.ObjectId = main.id))  JOIN CustomFields
CustomFields_2  ON ( CustomFields_2.id =
ObjectCustomFieldValues_1.CustomField) AND( (CustomFields_2.Name =
'Product')))   WHERE ((main.EffectiveId = main.id)) AND ((main.Status !=
'deleted')) AND ((main.Type = 'ticket')) AND
((ObjectCustomFieldValues_1.Content LIKE '%IPM%')AND
( (main.Status = 'new')OR(main.Status = 'open') ) )    ORDER BY main.id ASC
LIMIT 50

Thanks,

-cv


---------- Forwarded message ----------
From: Jesse Vincent <jesse at bestpractical.com>
Date: Oct 10, 2005 9:39 AM
Subject: Re: [rt-users] Searching Custom Fields
To: Chris Valdez <rpmonkey at gmail.com>
Cc: rt-users at lists.bestpractical.com




On Mon, Oct 10, 2005 at 08:54:08AM -0700, Chris Valdez wrote:
> All I did was turn on logging in mysql and then used the web interface to
> search for tickets where the Custom Field "Product" is like the string
> "IPM".
>
> The formatting of the main.id <http://main.id/> into a URL was done by
> gmail, it's not like that in the log. Sorry 'bout that, don't know what
> gmail was doing there.

Can you send the query exactly as it was in the logs, possibly from a
mailer that understands plain text? ;)

>
> Thanks,
>
> On 10/10/05, Jesse Vincent < jesse at bestpractical.com> wrote:
> >
> >
> > > ObjectCustomFieldValues_1.ObjectId = main.id < http://main.id> <
> > http://main.id>)
> >
> > That looks insanely suspect. How did you generate this query?
> >

> _______________________________________________
> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
>
> Be sure to check out the RT Wiki at http://wiki.bestpractical.com
>
> Buy your copy of our new book, RT Essentials, today!
>
> Download a free sample chapter from http://rtbook.bestpractical.com

--