[Rt-devel] Searching for Multiple values in a Custom Field

[Harry Bochner]

One of the groups using RT here wants to be able to tag tickets with
one or more keywords. This is a perfect application for a custom field
set up as "Select Multiple Values"; I've set it up and it looks good.

And searching for tagged tickets works quite nicely when you're
searching for a single tag value.

But my users want to be able to search for tickets that carry multiple
tags, e.g. both "ABC" and "DEF", and I haven't succeeded in getting
this to work.

The best I've been able to do with the UI is to generate this Ticket
SQL:

Queue = 'QueueName' AND 'CF.{Keywords}' = 'ABC' AND 'CF.{Keywords}' = 
'DEF'

this gets translated into the following real SQL:

SELECT DISTINCT main.*
FROM Tickets main
    JOIN ObjectCustomFields ObjectCustomFields_1
    ON ( ObjectCustomFields_1.ObjectId = '0' )
        OR ( ObjectCustomFields_1.ObjectId = main.Queue )
    JOIN CustomFields CustomFields_2
    ON ( CustomFields_2.id = ObjectCustomFields_1.CustomField )
    JOIN ObjectCustomFieldValues ObjectCustomFieldValues_3
    ON ( ObjectCustomFieldValues_3.ObjectId = main.id )
       AND ( ObjectCustomFieldValues_3.Disabled = '0' )
       AND ( ObjectCustomFieldValues_3.ObjectType = 'RT::Ticket' )
       AND ( ObjectCustomFieldValues_3.CustomField = CustomFields_2.id )
WHERE (CustomFields_2.Name = 'Keywords' AND CustomFields_2.Name = 
'Keywords')
AND (main.Status != 'deleted')
AND ( ( main.Queue = '7' )
    AND  (  ( ObjectCustomFieldValues_3.Content = 'ABC' )
        OR    ( ObjectCustomFieldValues_3.Content = 'DEF' )  ) )
AND (main.Type = 'ticket') AND (main.EffectiveId = main.id) 

And this SQL never returns any tickets, since the conditions on the
second and third lines from the bottom can never be satisfied at the
same time.

What I want is SQL something like this:
SELECT DISTINCT main.*
FROM Tickets main
    JOIN ObjectCustomFields ObjectCustomFields_1
    ON ( ObjectCustomFields_1.ObjectId = '0' )
        OR ( ObjectCustomFields_1.ObjectId = main.Queue )
    JOIN CustomFields CustomFields_2
    ON ( CustomFields_2.id = ObjectCustomFields_1.CustomField )
    JOIN ObjectCustomFieldValues ObjectCustomFieldValues_3
    ON ( ObjectCustomFieldValues_3.ObjectId = main.id )
       AND ( ObjectCustomFieldValues_3.Disabled = '0' )
       AND ( ObjectCustomFieldValues_3.ObjectType = 'RT::Ticket' )
       AND ( ObjectCustomFieldValues_3.CustomField = CustomFields_2.id )
    JOIN ObjectCustomFieldValues ObjectCustomFieldValues_4
    ON ( ObjectCustomFieldValues_4.ObjectId = main.id )
       AND ( ObjectCustomFieldValues_4.Disabled = '0' )
       AND ( ObjectCustomFieldValues_4.ObjectType = 'RT::Ticket' )
       AND ( ObjectCustomFieldValues_4.CustomField = CustomFields_2.id )
WHERE (CustomFields_2.Name = 'Keywords' AND CustomFields_2.Name = 
'Keywords')
AND (main.Status != 'deleted')
AND ( ( main.Queue = '7' )
    AND  (   ( ObjectCustomFieldValues_3.Content = 'ABC' )
        AND  ( ObjectCustomFieldValues_4.Content = 'DEF' )  ) )
AND (main.Type = 'ticket') AND (main.EffectiveId = main.id) 

I've run this SQL directly through the mysql client, and it does what
I want: it finds tickets where both the ABC and the DEF keywords have
been selected.

So my question is: is there a way to get this result via the web
interface?
It's not clear to me how to write Ticket SQL that will do the
right thing, much less how to construct that Ticket SQL through the
web UI. Is there a way to do this?

I've found one work-around, but it's not very satisfying: you can give
up on the "Select Multiple Values" type of custom field, and just use
a text area. Then wild card searches ("contains") against the text
area will do the right thing. But this gives up the controlled
vocabulary of the pull-down list, not to mention the nice UI.

I can imagine changing _CustomFieldLimit() in Tickets_Overlay.pm so that
the Ticket SQL that the UI generates gets translated into SQL that does
what I want. But that wouldn't be a simple thing, so I'm hoping some
one has a simpler (or better) solution.

Thanks in Advance,

-- 
Harry
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.bestpractical.com/pipermail/rt-devel/attachments/20070823/63dae2b9/attachment.htm