Bug #41415 for CatalystX-CRUD: Modify query wildcard behavior

Wed Dec 03 20:16:26 2008 gordon [...] visi.com - Ticket created

Subject:	Modify query wildcard behavior - best method?
Date:	Wed, 3 Dec 2008 17:16:06 -0800
To:	bug-catalystx-crud [...] rt.cpan.org
From:	"Gordon Pedersen" <gordon [...] visi.com>

Hi Peter, For the most part in the apps I support I want a different SQL query wildcard behavior than is stock with CatalystX::CRUD. Right now I use a 'cxc-fuzzy' param in my search form to turn out a WHERE clause like "my_column LIKE 'my_val%'". Works fine. But what I want is to have a WHERE clause like this: "my_column LIKE '%my_val%'" - ie, put the '%' at both ends. I don't want to change the behavior triggered by 'cxc-fuzzy'. I either want to introduce a new variable, say 'csx-dbl-fuzzy', to trigger the desired behavior. This would seem to require changes to Search::QueryParser::SQL::Query::_orm_subq (sp?) and I don't want to mess with that. Or it looks like I can surround all non-empty search form fields with asterisk at both ends before passing on to the do_search method and the above "_orm_subq" method will do the right thing. I'm not sure the best place/method to do the latter. Your examples or suggestions are solicited. Especially if it seems that I'm looking at this the wrong way. Thanks. -- Gordon Pedersen

Wed Dec 03 21:30:30 2008 peter [...] peknet.com - Correspondence added

Subject:	Re: [rt.cpan.org #41415] Modify query wildcard behavior - best method?
Date:	Wed, 03 Dec 2008 20:29:57 -0600
To:	bug-CatalystX-CRUD [...] rt.cpan.org
From:	Peter Karman <peter [...] peknet.com>

gp via RT wrote on 12/3/08 7:16 PM: Show quoted text

> I either want to introduce a new variable, say 'csx-dbl-fuzzy', to trigger > the desired behavior. This would seem to require changes to > Search::QueryParser::SQL::Query::_orm_subq (sp?) and I don't want to mess > with that. > > Or it looks like I can surround all non-empty search form fields with > asterisk at both ends before passing on to the do_search method and the > above "_orm_subq" method will do the right thing.

Hi Gordon, This very feature request came up today at $work, so you're not alone. I was thinking along the lines of 'cxc-fuzzy2' so I think that option is the better route. I'll work on getting that out in the next CXCY release. I expect it'll mean a change to SQS as well. -- Peter Karman . http://peknet.com/ . peter@peknet.com

Wed Dec 03 21:30:32 2008 The RT System itself - Status changed from 'new' to 'open'

Wed Dec 03 23:28:36 2008 gordon [...] visi.com - Correspondence added

Subject:	Re: [rt.cpan.org #41415] Modify query wildcard behavior - best method?
Date:	Wed, 3 Dec 2008 20:28:18 -0800
To:	bug-CatalystX-CRUD [...] rt.cpan.org
From:	"Gordon Pedersen" <gordon [...] visi.com>

On Wed, Dec 3, 2008 at 6:30 PM, peter@peknet.com via RT < bug-CatalystX-CRUD@rt.cpan.org> wrote: Show quoted text

> <URL: http://rt.cpan.org/Ticket/Display.html?id=41415 > > > gp via RT wrote on 12/3/08 7:16 PM: >

> > I either want to introduce a new variable, say 'csx-dbl-fuzzy', to

> trigger

> > the desired behavior. This would seem to require changes to > > Search::QueryParser::SQL::Query::_orm_subq (sp?) and I don't want to mess > > > > Or it looks like I can surround all non-empty search form fields with > > asterisk at both ends before passing on to the do_search method and the

> > ... > I was thinking along the lines of 'cxc-fuzzy2' so I think that option is > the > better route. I'll work on getting that out in the next CXCY release. I > expect > it'll mean a change to SQS as well.

Good. I look forward to it and will expect this ticket to update me when it happens. Show quoted text

> > --

Gordon Pedersen

Thu Dec 04 22:19:55 2008 peter [...] peknet.com - Correspondence added

Subject:	Re: [rt.cpan.org #41415] Modify query wildcard behavior - best method?
Date:	Thu, 04 Dec 2008 21:19:20 -0600
To:	bug-CatalystX-CRUD [...] rt.cpan.org
From:	Peter Karman <peter [...] peknet.com>

gp via RT wrote on 12/3/08 10:28 PM: Show quoted text

> Queue: CatalystX-CRUD > Ticket <URL: http://rt.cpan.org/Ticket/Display.html?id=41415 > > > On Wed, Dec 3, 2008 at 6:30 PM, peter@peknet.com via RT < > bug-CatalystX-CRUD@rt.cpan.org> wrote: >

>> <URL: http://rt.cpan.org/Ticket/Display.html?id=41415 > >> >> gp via RT wrote on 12/3/08 7:16 PM: >>

>>> I either want to introduce a new variable, say 'csx-dbl-fuzzy', to

>> trigger

>>> the desired behavior. This would seem to require changes to >>> Search::QueryParser::SQL::Query::_orm_subq (sp?) and I don't want to mess >>> >>> Or it looks like I can surround all non-empty search form fields with >>> asterisk at both ends before passing on to the do_search method and the

>> ... >> I was thinking along the lines of 'cxc-fuzzy2' so I think that option is >> the >> better route. I'll work on getting that out in the next CXCY release. I >> expect >> it'll mean a change to SQS as well.

> > > Good. I look forward to it and will expect this ticket to update me when it > happens.

CatalystX::CRUD 0.36 and Search::QueryParser::SQL 0.007 just uploaded to pause. You want cxc-fuzzy2 to get the behaviour you describe. give 'em a spin and let me know what you think. oh, and kudos for discovering cxc-fuzzy in the first place since it wasn't documented (it is now). -- Peter Karman . http://peknet.com/ . peter@peknet.com

Fri Dec 05 13:50:08 2008 gordon [...] visi.com - Correspondence added

Subject:	Re: [rt.cpan.org #41415] Modify query wildcard behavior - best method?
Date:	Fri, 5 Dec 2008 10:49:52 -0800
To:	bug-CatalystX-CRUD [...] rt.cpan.org
From:	"Gordon Pedersen" <gordon [...] visi.com>

What fun to get it changed so soon! It works just as expected vis-a-vis wildcards. Thank you. An interesting aspect that is probably unrelated to your change is that queries using your do_search() run against sqlite3 (only database used so far) are case-insensitive. Surprised me, but it fits with my intentions to make user searching painfree. Have my doubts whether Oracle will prove so dwim-y. Gordon On Thu, Dec 4, 2008 at 7:20 PM, peter@peknet.com via RT < bug-CatalystX-CRUD@rt.cpan.org> wrote: Show quoted text

> <URL: http://rt.cpan.org/Ticket/Display.html?id=41415 > > > gp via RT wrote on 12/3/08 10:28 PM:

> > Queue: CatalystX-CRUD > > Ticket <URL: http://rt.cpan.org/Ticket/Display.html?id=41415 > > > > > On Wed, Dec 3, 2008 at 6:30 PM, peter@peknet.com via RT < > > bug-CatalystX-CRUD@rt.cpan.org> wrote: > >

> >> <URL: http://rt.cpan.org/Ticket/Display.html?id=41415 > > >> > >> gp via RT wrote on 12/3/08 7:16 PM: > >>

> >>> I either want to introduce a new variable, say 'csx-dbl-fuzzy', to

> >> trigger

> >>> the desired behavior. This would seem to require changes to > >>> Search::QueryParser::SQL::Query::_orm_subq (sp?) and I don't want to

> mess

> >>> > >>> Or it looks like I can surround all non-empty search form fields with > >>> asterisk at both ends before passing on to the do_search method and the

> >> ... > >> I was thinking along the lines of 'cxc-fuzzy2' so I think that option is > >> the > >> better route. I'll work on getting that out in the next CXCY release. I > >> expect > >> it'll mean a change to SQS as well.

> > > > > > Good. I look forward to it and will expect this ticket to update me when

> it

> > happens.

> > CatalystX::CRUD 0.36 and Search::QueryParser::SQL 0.007 just uploaded to > pause. > > You want cxc-fuzzy2 to get the behaviour you describe. > > give 'em a spin and let me know what you think. > > oh, and kudos for discovering cxc-fuzzy in the first place since it wasn't > documented (it is now). > > -- > Peter Karman . http://peknet.com/ . peter@peknet.com > > >

-- Gordon Pedersen

Mon Dec 08 10:15:32 2008 peter [...] peknet.com - Correspondence added

Subject:	Re: [rt.cpan.org #41415] Modify query wildcard behavior - best method?
Date:	Mon, 08 Dec 2008 09:15:16 -0600
To:	bug-CatalystX-CRUD [...] rt.cpan.org
From:	Peter Karman <peter [...] peknet.com>

gp via RT wrote on 12/05/2008 12:50 PM: Show quoted text

> Queue: CatalystX-CRUD > Ticket <URL: http://rt.cpan.org/Ticket/Display.html?id=41415 > > > What fun to get it changed so soon! It works just as expected vis-a-vis > wildcards. Thank you. > > An interesting aspect that is probably unrelated to your change is that > queries using your do_search() run against sqlite3 (only database used so > far) are case-insensitive. Surprised me, but it fits with my intentions to > make user searching painfree. Have my doubts whether Oracle will prove so > dwim-y.

it's the 'ilike' vs 'like' setting. It defaults to 'ilike' iirc, so that searches are case-insensitive. I, too, like painfree search. :) -- Peter Karman . peter@peknet.com . http://peknet.com/

Mon Feb 02 22:05:04 2009 karman [...] cpan.org - Status changed from 'open' to 'resolved'

Bug #41415 for CatalystX-CRUD: Modify query wildcard behavior - best method?