SQL OR in Rails


#1

Hi there everyone,

How does one set-up a SQL OR query in Rails.

I have the following SQL query:

select state, state_abbr, country from states where country = ‘Country’
or country = ‘International’ order by state asc;

Now in Rails:

@states = States.find(:all, :conditions => [:country => LOCALE, :country
=> ‘International’])

But the above will do a SQL AND query. What is the right keyword to use
between those two conditions to turn this into a OR query?

Thanks!
Schalk


#2

Hi Schalk

Just use:

@states = States.find(:all, :conditions => [“country=? OR
country=‘International’”, LOCALE])

Jan


#3

Cool, thanks. My IDE was moaning when I did something similar to that.
One should not always trust the IDE I guess, or does it have something
to do with the fact that you moved the constant to the end?

Kind Regards,
Schalk


#4

Thanks Jan.


#5

Hi,

I’m not sure how your code was, but you can do this as well:

@states = States.find(:all, :conditions => [“country=? OR country=?”,
LOCALE, ‘International’])

It’s just an array where every question mark will get replaced with
it’s successive array element after having being Escaped by Rails.
So the first <?> gets replaced with the 2nd element
The second <?> with the 3rd element
And so on

Jan


#6

On Oct 21, 2008, at 7:43 AM, javinto wrote:

The second <?> with the 3rd element
And so on

Jan

You have a special case there – one column with any of a set of
values. For that, you can use the SQL ‘IN’ operation like this:

States.find(:all, :conditions => [‘country IN (?)’, [LOCALE,
‘International’]] )

If you had more,
countries = [ …all the ones you needed… ]

States.find(:all, :conditions => [‘country IN (?)’, countries] )

It will work with an array having just one element also.

-Rob

Rob B. http://agileconsultingllc.com
removed_email_address@domain.invalid


#7

I believe you can also do:

@states = States.find_by_country([LOCALE, ‘International’])

Worth a try…

Cheers,

-Roy