[rt-users] Searching Custom Fields

[Ruslan Zakirov]

RT doesn't support MySQL 3.23.58.
This query works in MySQL 4.1 and I think in 4.0.

On 10/11/05, Chris Valdez <rpmonkey at gmail.com> wrote:
> 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
>
> --
>
> _______________________________________________
> 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
>


--
Best regards, Ruslan.