rails - Build queries, the combining them?

Question

Based on my app, I need to grab users based on a set of conditions.

@usersSet1 = XXX based on a bunch of conditions

@usersSet2 = XXX based on a bunch of different conditions

@usersSet3 = XXX based on a bunch of even more different conditions

I then want to combine the 3, and take the top 100 records. Any thoughts on getting this done with Rails? thanks

How do you want to combine them? Appending, ordering, what? – Jimmy Jan 20 '11 at 22:10 — Jimmy, Jan 20 '11 at 22:10

score 3 · Accepted Answer · answered Jan 20 '11 at 22:28

3

You should define scopes in your model:

# model
scope :set1, where(some_conditions), ...
scope :set2, where(some_conditions), ...
scope :set3, where(some_conditions), ...
scope :top_100, limit(100)

Give those scopes appropriate names. And of course you need to order it by something. Then you can call:

@usersSet1 = User.set1
@usersSet2 = User.set2
@usersSet3 = User.set3

@usersset123 = User.set1.set2.set3.top_100

It will AND all conditions in set1, set2, and set3.

answered Jan 20 '11 at 22:28

klew

14,837
7
47
59

Ok turns out this doesn't work because each scope is added to the SQL like so,, SET 1 = XXX, AND set 2 = x, AND set 3, where what I need is OR not AND. ideas? – AnApprentice Jan 21 '11 at 18:03
There is no simple way of chaining scopes and `OR` them. You have to write new scope that uses `OR` and call it. It is not DRY, but it works. You also can take a look here: http://stackoverflow.com/questions/3684311/rails-how-to-chain-scope-queries-with-or-instead-of-and and here: http://stackoverflow.com/questions/3016983/chaining-named-scopes-not-working-as-intended – klew Jan 21 '11 at 21:48

score -1 · Answer 2 · answered Jan 20 '11 at 22:06

The best way is to do one SQL query pulling back the fields that have those conditions. For example:

@users = User.all(select => "city,state,last_name,first_name,…", :limit => 100)

Now you have all of the columns. So you move on to:

@usersSet1 = @users.select{|a| a.state == 'Charlotte' && a.state == 'NC') #pulls back users in Charlotte
@usersSet2 = @users.select{|b| b.last_name == 'Smith' } #pulls back users that have the last name of Smith.
@usersSet3 = @users.select{|b| b.first_name == ('John' || 'Joe') } #pulls back users that have the first name of John or Joe.

And so on… This, in my opinion, is much more scalable.

Thanks but the issue here is that each query needs another table for the logic. like permissions, etc — AnApprentice, Jan 20 '11 at 23:40

rails - Build queries, the combining them?

2 Answers2