Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Rails 3 ActiveRecord query using both SQL IN and SQL OR operators

I'm writing a Rails 3 ActiveRecord query using the "where" syntax, that uses both the SQL IN and the SQL OR operator and can't figure out how to use both of them together.

This code works (in my User model):

Question.where(:user_id => self.friends.ids)
#note: self.friends.ids returns an array of integers

but this code

Question.where(:user_id => self.friends.ids OR :target => self.friends.usernames)

returns this error

syntax error, unexpected tCONSTANT, expecting ')'
...user_id => self.friends.ids OR :target => self.friends.usern...

Any idea how to write this in Rails, or just what the raw SQL query should be?

like image 846
kateray Avatar asked Nov 17 '10 18:11

kateray


People also ask

What is ActiveRecord in Ruby?

What is ActiveRecord The #activerecord is what binds the model in our ruby application with its respective database table. The Active record object comes with a set of methods to help us query database records so theres no need to use raw SQL. For comparation purposes will present the translation of our activerecord queries to sql queries.

What is the use of active record in SQL?

The Active record object comes with a set of methods to help us query database records so theres no need to use raw SQL. For comparation purposes will present the translation of our activerecord queries to sql queries.

Why does rails prefer to use where ID in query?

Well, as there are no additional constraints on the countries we are selecting, Rails prefers to resort to a WHERE id IN query, taking advantage of the primary key index. Though, if we add constraints to our query active record performs a LEFT OUTER JOIN instead:

How to create reusable queries in rails?

In older versions of rails, you should use the ActiveRecord::Base#scope class method to create such reusable queries. You are able to attain the same functionality by simply defining class methods: After defining the queries in such fashion, you are able to chain these high level abstractions together.


3 Answers

You don't need to use raw SQL, just provide the pattern as a string, and add named parameters:

Question.where('user_id in (:ids) or target in (:usernames)', 
               :ids => self.friends.ids, :usernames => self.friends.usernames)

Or positional parameters:

Question.where('user_id in (?) or target in (?)', 
               self.friends.ids, self.friends.usernames)

You can also use the excellent Squeel gem, as @erroric pointed out on his answer (the my { } block is only needed if you need access to self or instance variables):

Question.where { user_id.in(my { self.friends.ids }) |
                 target.in(my { self.friends.usernames }) }
like image 188
Fábio Batista Avatar answered Sep 19 '22 18:09

Fábio Batista


Though Rails 3 AR doesn't give you an or operator you can still achieve the same result without going all the way down to SQL and use Arel directly. By that I mean that you can do it like this:

t = Question.arel_table
Question.where(t[:user_id].in(self.friends.ids).or(t[:username].in(self.friends.usernames)))

Some might say it ain't so pretty, some might say it's pretty simply because it includes no SQL. Anyhow it most certainly could be prettier and there's a gem for it too: MetaWhere

For more info see this railscast: http://railscasts.com/episodes/215-advanced-queries-in-rails-3 and MetaWhere site: http://metautonomo.us/projects/metawhere/

UPDATE: Later Ryan Bates has made another railscast about metawhere and metasearch: http://railscasts.com/episodes/251-metawhere-metasearch Later though Metawhere (and search) have become more or less legacy gems. I.e. they don't even work with Rails 3.1. The author felt they (Metawhere and search) needed drastic rewrite. So much that he actually went for a new gem all together. The successor of Metawhere is Squeel. Read more about the authors announcement here: http://erniemiller.org/2011/08/31/rails-3-1-and-the-future-of-metawhere-and-metasearch/ and check out the project home page: http://erniemiller.org/projects/squeel/ "Metasearch 2.0" is called Ransack and you can read something about it from here: http://erniemiller.org/2011/04/01/ransack-the-library-formerly-known-as-metasearch-2-0/

like image 42
Timo Avatar answered Sep 21 '22 18:09

Timo


Alternatively, you could use Squeel. To my eyes, it is simpler. You can accomplish both the IN (>>) and OR (|) operations using the following syntax:

Question.where{(:user_id >> my{friends.id}) | (:target >> my{friends.usernames})}

I generally wrap my conditions in (...) to ensure the appropriate order of operation - both the INs happen before the OR.

The my{...} block executes methods from the self context as defined before the Squeel call - in this case Question. Inside of the Squeel block, self refers to a Squeel object and not the Question object (see the Squeel Readme for more). You get around this by using the my{...} wrapper to restore the original context.

like image 31
erroric Avatar answered Sep 21 '22 18:09

erroric