can complex active record queries be made using AND OR conditions in rails

Question

I have some filters that are put in sequence as per admin requirement to fetch target users

e.g (Filter_1 OR Filter_2) AND (Filter_3 OR Filter_4)

Application has many filters and these filters fetches users that are meeting some criteria. But these filters take use of 3-4 tables of inner join.

Filter_1 = Get users with avg perception score >= 0 generates query

1.  select user_avg_table.* from
              (select users.*, avg(perception_score) as avg from users
              inner join notes
              on notes.user_id  = users.id group by user_id) as user_avg_table where avg >= 0

Filter_2 = User.where("Date(created_at) = DATE(NOW())")

2.    SELECT * FROM `users`  WHERE (Date(auth_token_created_at) =  DATE(NOW()))

Filter_3 = User.joins(:notes).where(notes: {category: "Experiences"})

3.  SELECT * FROM users INNER JOIN notes ON notes.user_id = users.id WHERE notes.category = ‘Experiences'

Filter_4 = User.joins(:transactions).where(transactions: {product_id: 2})

4.  SELECT * FROM users INNER JOIN transactions ON transactions.user_id = users.id WHERE transactions.product_id = 3

Right now I am fetching users in 4 variables one for each filters and then performing ruby '|' and '&' methods over them.

eg.

    users_1 = Filter_1.get_users
    users_2 = Filter_2.get_users
    users_3 = Filter_3.get_users
    users_4 = Filter_4.get_users

target_users = (users_1 | users_2) & (users_3 | users_4)

it gives me an array of users.

Can I achieve this by using active record queries? which can give me array of active records rather than array of users. Can queries of all those be filters be combined? What is the best possible approach?

score 2 · Answer 1 · edited May 23 '17 at 12:17

Rails 4 support for or commands is pretty minimal (source), but it sounds like Rails 5 will have better support for it. You can use this gem for a backport.

But that will generate a pretty complex query with lots of joins.

It might be faster to do 4 smaller queries (what you've already done). If you need the results of this to be in an active record collection, you can do this

ids = Filter_1.get_users.select(:id)
ids += Filter_2.get_users.select(:id)
ids += Filter_3.get_users.select(:id)
ids += Filter_4.get_users.select(:id)

User.where(id: ids)

As a side note, its generally better to create scopes on your model that name each subset. That way, rather than Filter_1.get_users, you can write Users.with_perception_score.

can complex active record queries be made using AND OR conditions in rails

1 Answers1