[rt-users] 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: 1WHERE (Transactions_1.ObjectType = 'RT::Ticket') 2AND (main.Status != 'deleted') 3AND (Users_5.EmailAddress LIKE '%munchy%') 4AND ( 5( ( Attachments_2.Content LIKE '%munchy%' ) ) 6OR 7( ( CachedGroupMembers_4.id IS NOT NULL ) ) 8OR 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 ___ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: sa...@bestpractical.com Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
Re: [rt-users] Can Simple.html search Email OR Content (not AND) ?
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, 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 > ___ > http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users > > Community help: http://wiki.bestpractical.com > Commercial support: sa...@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. ___ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: sa...@bestpractical.com Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
Re: [rt-users] Can Simple.html search Email OR Content (not AND) ?
>> 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 'f...@foo.com' > To solve your latter problem of partial matches, you might try RTx::From > http://search.cpan.org/~jpierce/RTx-From-0.02/lib/RTx/From.pm 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. ___ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: sa...@bestpractical.com Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
Re: [rt-users] Can Simple.html search Email OR Content (not AND) ?
> 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. ___ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: sa...@bestpractical.com Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
Re: [rt-users] Can Simple.html search Email OR Content (not AND) ?
> 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. ___ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: sa...@bestpractical.com Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
Re: [rt-users] Can Simple.html search Email OR Content (not AND) ?
On Thu, May 21, 2009 at 11:41:50AM -0700, allen+rtl...@crystalfontz.com wrote: > > 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 ___ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: sa...@bestpractical.com Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com