[rt-users] Re: [3.6][FIX] Searches by custom fields

[Ruslan Zakirov]

DBIx::SearchBuilder 1.39 and greater should fix this issue.
Don't forget to run `make testdeps` on updates from SVN too :)

On 5/12/06, Joshua Colson <jcolson at voidgate.org> wrote:
> On Thu, 2006-05-11 at 21:55 +0400, Ruslan Zakirov wrote:
> > I reproduced problem when people search by one field with several
> > restrictions, for example "CF.{cf1} = 'foo' OR CF.{cf1} = 'bar'".
> > Also, change should fix issue when RT generate wrong SQL query with
> > reference to 'main.name' column that doesn't exist.
> >
> > If you see other issues feel free to send links to the archives.
>
> Okay, after some digging, I found this SQL query (line numbers added):
>
> ----------------------------------------------------------------
> 1  SELECT DISTINCT main.*
> 2  FROM Tickets main
> 3  LEFT JOIN ObjectCustomFields ObjectCustomFields_1
> 4    ON ((ObjectCustomFields_1.ObjectId = '0'))
> 5    AND(  ObjectCustomFields_1.ObjectId = main.Queue)
> 6  LEFT JOIN CustomFields CustomFields_2
> 7    ON ( CustomFields_2.id = ObjectCustomFields_1.CustomField)
> 8  LEFT JOIN ObjectCustomFieldValues ObjectCustomFieldValues_3
> 9    ON ((ObjectCustomFieldValues_3.ObjectId = main.id))
> 10   AND(ObjectCustomFieldValues_3.CustomField = CustomFields_2.id)
> 11   AND( (ObjectCustomFieldValues_3.Disabled = '0'))
> 12   AND( (ObjectCustomFieldValues_3.ObjectType = 'RT::Ticket'))
> 13 WHERE ((CustomFields_2.Name = 'Registrar-Lock'))
> 14   AND ((main.EffectiveId = main.id))
> 15   AND ((main.Status != 'deleted'))
> 16   AND ((main.Type = 'ticket'))
> 17   AND ((main.Queue = '3')
> 18   AND ( (ObjectCustomFieldValues_3.Content = 'Yes') ) )
> 19 ORDER BY main.id ASC
> 20 LIMIT 50;
> ----------------------------------------------------------------
>
> I ran this against the DB and got zero results. So after closer
> inspection, it seems that this could only ever be true if the custom
> field is Global. The reason for this lies in line 5 of the statement.
> The aggregator should be 'OR', not 'AND'. If I change that, I get an
> accurate listing of tickets in the result set. However, I tried digging
> at the code to find where it is being set and haven't found it, yet. I
> figured I would send this to the list in case somebody knows off the the
> top of their head.
>
> Thanks.
>
>
> --
> Joshua Colson <jcolson at voidgate.org>
>
>


-- 
Best regards, Ruslan.