[Dbix-class] ORing searches

[Matt S Trout]

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/ +