Rails 3 ActiveRecord query using both SQL IN and SQL OR operators
Solution 1
raw SQL
SELECT *
FROM table
WHERE user_id in (LIST OF friend.ids) OR target in (LIST OF friends.usernames)
with each list comma separate. I don't know the Rails ActiveRecord stuff that well. For AND you would just put a comma between those two conditions, but idk about OR
Solution 2
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 }) }
Solution 3
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/
Solution 4
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.
kateray
Updated on April 03, 2020Comments
-
kateray about 4 years
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?
-
kateray over 13 yearsThanks! The exact query I used was: Question.where('target IN (?) OR user_id IN (?)', self.friends.usernames, self.friends.ids)
-
jon_darkstar over 13 yearsok great, i forgot about that. i feel like they ought to be a way within the => hash format. you shouldn't need a string pattern just to add OR in between 2 valid conditions, but who cares you got it.
-
nunopolonia over 12 yearsthe idea of active record is to be database agnostic, so using raw sql in rails shouldn't be considered the best solution except where performance is degraded, which isn't the case in this query
-
crizCraig almost 11 yearsLooks good, but they clobbered Active Record's
where()
which seems unfortunate. -
jon_darkstar over 10 yearsOP - you really should accept Fabio's answer below instead. I'm not gonna delete mine while it's the accepted, but if you make that swap i probably will.
-
zeal over 9 yearsHey man kind of need help.. How to write query if I need to select all messages between two user(say id1 & id2) and message have sender_id and receiver_id. So i need to select "all messages whose ((sender_id = id1 and reciver_id = id2) or (sender_id = id2 and reciver_id = id1))". I need to sort them out and paginate which can not be done if I make two separate query and then add them(I will have the desired result but pagination becomes difficult). So please help me out in writing single line query for this. Any help will be appreciated.
-
Fábio Batista over 9 years@zeal, please open a new question, describing what you already tried and how we can help. The community is great, but you got to learn how to use it.