Can Simple.html search Email OR Content (not AND)?

Hi,

I would like to make it so that the Simple Search (3.8.2) will always
search Content, Requestor and Subject fields using LIKE comparisons so
that users won’t need to prepend “fulltext:” and “email:” (the latter
won’t find partial email addresses) on search terms.

I have created a local overlay for html/Search/Simple.html which loads
a local lib/RT/Search/Googleish.pm and that has an altered QueryToSQL
function which bakes the search term into the pseudo-SQL:

( Content LIKE 'munchy' ) OR ( Requestor LIKE 'munchy' ) OR (

Subject LIKE ‘munchy’ )

The above gets fed deeper into RT SearchBuilder guts, until it comes
out in the mysql query log as this:

1 WHERE (Transactions_1.ObjectType = ‘RT::Ticket’)
2 AND (main.Status != ‘deleted’)
3 AND (Users_5.EmailAddress LIKE ‘%munchy%’)
4 AND (
5 ( ( Attachments_2.Content LIKE ‘%munchy%’ ) )
6 OR
7 ( ( CachedGroupMembers_4.id IS NOT NULL ) )
8 OR
9 ( main.Subject LIKE ‘%munchy%’ )
10 )
11 AND (main.Type = ‘ticket’)
12 AND (main.EffectiveId = main.id)

So the final actual query is not what I asked for because there will
be zero results if the EmailAddress does not match. I want all results
where the Email OR Content OR Subject is LIKE the search term. If Line
3 above could be moved down after Line 4 and connected by “OR” then it
would work and find tickets.

I dug around and found something about EnteryAggregator in
lib/RT/Tickets_Overlay.pm but I don’t know what to do next or how to
modify Googleish.pm to get the results I am looking for.

A

This known issue that if email is not in the db then result would be
empty even if condition is ORed with another, but believe me correct
query will kick breath out of your DB server.On Thu, May 21, 2009 at 9:27 PM, allen+rtlist@crystalfontz.com wrote:

Hi,

I would like to make it so that the Simple Search (3.8.2) will always
search Content, Requestor and Subject fields using LIKE comparisons so
that users won’t need to prepend “fulltext:” and “email:” (the latter
won’t find partial email addresses) on search terms.

I have created a local overlay for html/Search/Simple.html which loads
a local lib/RT/Search/Googleish.pm and that has an altered QueryToSQL
function which bakes the search term into the pseudo-SQL:

( Content LIKE ‘munchy’ ) OR ( Requestor LIKE ‘munchy’ ) OR (
Subject LIKE ‘munchy’ )

The above gets fed deeper into RT SearchBuilder guts, until it comes
out in the mysql query log as this:

1 WHERE (Transactions_1.ObjectType = ‘RT::Ticket’)
2 AND (main.Status != ‘deleted’)
3 AND (Users_5.EmailAddress LIKE ‘%munchy%’)
4 AND (
5 ( ( Attachments_2.Content LIKE ‘%munchy%’ ) )
6 OR
7 ( ( CachedGroupMembers_4.id IS NOT NULL ) )
8 OR
9 ( main.Subject LIKE ‘%munchy%’ )
10 )
11 AND (main.Type = ‘ticket’)
12 AND (main.EffectiveId = main.id)

So the final actual query is not what I asked for because there will
be zero results if the EmailAddress does not match. I want all results
where the Email OR Content OR Subject is LIKE the search term. If Line
3 above could be moved down after Line 4 and connected by “OR” then it
would work and find tickets.

I dug around and found something about EnteryAggregator in
lib/RT/Tickets_Overlay.pm but I don’t know what to do next or how to
modify Googleish.pm to get the results I am looking for.

A


The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Best regards, Ruslan.

I would like to make it so that the Simple Search (3.8.2) will always
search Content, Requestor and Subject fields using LIKE comparisons so
and “email:” (won’t find partial email addresses)

Is email: some newfangled 3.8.2 thing?

Oops. Mixed up one of my attempted mods with how the stock install
works. No, you are right. There is no “email:” modifier for simple
search. I was trying to get it to search email addresses where the
user doesn’t remember the full address – like just searching for
‘email:foo’ when they can’t remember ‘foo@foo.com’

To solve your latter problem of partial matches, you might try RTx::From
RTx::From - Make it easier to find users and their tickets - metacpan.org

Looking at the synopsis of that, “cannot be meaningfully combined with
other search terms” seems to mean that a person could not search for
“from:foo bar” and expect to find tickets that were from User “foo”
and/or had the word “bar” in them. Is that right? If so, I don’t think
that is going to help me since my users are going to try to use other
terms as well.

A.

Looking at the synopsis of that, “cannot be meaningfully combined with
other search terms” seems to mean that a person could not search for
“from:foo bar” and expect to find tickets that were from User “foo”
and/or had the word “bar” in them. Is that right? If so, I don’t think
that is going to help me since my users are going to try to use other
terms as well.
Right, and there’s no easy way around that as far as I know.
You can do a two step process though:

  • Search “from:bob”
  • Click “Requested” for the intended user
  • Edit Search, and add other criteria as you see fit.

Cambridge Energy Alliance: Save money. Save the planet.

This known issue that if email is not in the db then result would be
empty even if condition is ORed with another, but believe me correct
query will kick breath out of your DB server.

Are you saying that this “known issue” is a bug or is it intentional?

I know that with a ton of tickets, this kind of “search everything” is
not practical. But this will be a new RT deployment and I need to ease
the support staff (and the boss) into getting used to this new system.
Eventually I know they will find the QueryBuilder screen to be very
precise and great, but right now everything is new and different and
that screen scares them. They think it’s too complicated.

That’s why I’d like to get the "WHERE EmailAddress like ‘%foo%’ OR
'Content like ‘%foo%’ OR’d together instead of having the EmailAddress
separated from the rest of the query by an “AND.”

Thanks,

A.

This known issue that if email is not in the db then result would be
empty even if condition is ORed with another, but believe me correct
query will kick breath out of your DB server.

Are you saying that this “known issue” is a bug or is it intentional?

I know that with a ton of tickets, this kind of “search everything” is
not practical. But this will be a new RT deployment and I need to ease
the support staff (and the boss) into getting used to this new system.
Eventually I know they will find the QueryBuilder screen to be very
precise and great, but right now everything is new and different and
that screen scares them. They think it’s too complicated.

That’s why I’d like to get the "WHERE EmailAddress like ‘%foo%’ OR
'Content like ‘%foo%’ OR’d together instead of having the EmailAddress
separated from the rest of the query by an “AND.”

Thanks,

A.

Allen,

This will result in every search being a full scan of the database
looking for a match in the content. This will cripple your system
for all but the smallest of databases. If you really intend to search
in the content, please choose and use a database backend that supports
full-text indexing. I know that the wiki has recipes for Oracle and
PostgreSQL.

Cheers,
Ken