[Dbix-class] ORing searches

[Alan Humphrey]

Works like a champ.  Thanks!

-----Original Message-----
From: dbix-class-bounces at lists.rawmode.org
[mailto:dbix-class-bounces at lists.rawmode.org] On Behalf Of Matt S Trout
Sent: Friday, January 20, 2006 1:38 PM
To: dbix-class at lists.rawmode.org
Subject: Re: [Dbix-class] ORing searches

On Fri, Jan 20, 2006 at 01:19:32PM -0800, Alan Humphrey wrote:
> I'd like to search for the same value in multiple columns.  I tried this:
> 
> my @result = BirdWeb::Admin::Model::Staging->class('Birds')->search_like
({
> 		    bird_common_name => '%robin%',
>       	    genus_scientific_name => '%robin%'
> 	
> },
> 	
> {
>                 join => [qw/default_bird_common_name_id
> 			       genus_id/]
> 	
> });
> 
> which, as expected, produces an AND select.  Checking the SQL::Abstract
docs
> it looks like this should work:
> 
> my @result = BirdWeb::Admin::Model::Staging->class('Birds')->search_like
({
> 		    -nest => [bird_common_name => '%robin%',
> 				  genus_scientific_name => '%robin%']
> 	
> },
> 	
> {
>            	    join => [qw/default_bird_common_name_id
> 				 genus_id/]
> 	
> });
> 
> But the resulting SQL is whack:
> 
> SELECT me.washington_map_id, me.taxonomic_order, me.genus_id,
> me.default_bird_common_name_id, me.breeding, me.habitat, me.image_id,
> me.nesting, me.diet, me.species_name_id, me.id, me.north_america_map_id,
> me.migration, me.conservation_status, me.default_sound_id,
> me.generic_bird_common_name_id, me.identification, me.behavior, me.voice,
> me.notes, me.where_found FROM birds me  JOIN bird_common_names
> default_bird_common_name_id ON ( default_bird_common_name_id.id =
> me.default_bird_common_name_id )  JOIN genera genus_id ON ( genus_id.id =
> me.genus_id ) WHERE ( ( ( ( like = ? ) OR ( like = ? ) OR ( like = ? ) OR
(
> like = ? ) ) ) ): bird_common_name %robin% genus_scientific_name %robin%
> 
> Note the bind variables.
> 
> Is there another technique?

Don't use search_like for anything complicated, it's a convenience function
and hence fairly fragile.

->search([ { bird_common_name => { 'like' => '%robin%' } }, { ... } ]);

should do what you want.

-- 
     Matt S Trout       Offering custom development, consultancy and support
  Technical Director    contracts for Catalyst, DBIx::Class and BAST.
Contact
Shadowcat Systems Ltd.  mst (at) shadowcatsystems.co.uk for more information

 + Help us build a better perl ORM:
http://dbix-class.shadowcatsystems.co.uk/ +

_______________________________________________
List: http://lists.rawmode.org/cgi-bin/mailman/listinfo/dbix-class
Wiki: http://dbix-class.shadowcatsystems.co.uk/
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/trunk/DBIx-Class/