[rt-users] Re: [3.6][FIX] Searches by custom fields
Joshua Colson
jcolson at voidgate.org
Thu May 11 18:41:35 EDT 2006
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>
More information about the rt-users
mailing list