SQL OR in Rails

rubyonrails-talk
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

javinto wrote:

4

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

5

Thanks Jan.

javinto wrote:

6

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

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 Biedenharn http://agileconsultingllc.com Rob@AgileConsultingLLC.com

7

I believe you can also do:

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

Worth a try...

Cheers,

-Roy