CODEWITHSUNDEEP
ruby-on-railsactiverecordruby-on-rails-5active-record-query
Aarthi
Aarthi

Reputation: 1521

Creating filters with combination of AND and OR rails

I am in a situation to filter the records based on some conditions(conditions are in the form of scopes). in user model

scope :has_email, -> { where('email IS NOT NULL') }
scope :email_contains, (email) -> { where("email ILIKE :email'", email: email)}

If I want both conditions to be combined with 'AND' operator, We can do something like, 

User.has_email.email_contains

The query generated would be 

SELECT "user".* FROM "user" WHERE (email ILIKE '%gmail.com%') AND (email IS NOT NULL)

How can I proceed if I need scopes to be combined with OR operators? I found that rails 5 added support to or method(https://blog.bigbinary.com/2016/05/30/rails-5-adds-or-support-in-active-record.html), But this won't work if we use includes or joins

Eg: User.has_email.or(User.some_scope).or(User.joins(:event).temp)

How do I join scopes with OR?

Upvotes: 1

Views: 118

Answers (1)

rewritten
rewritten

Reputation: 16435

The bit you are missing is that a join is forcing the association to exist. To prevent that, you use left_joins:

User.left_joins(:event).where(event: {foo: bar})

Still it won't solve the issue because the .or method will work (by documentation) only on structurally equivalent relations.

You can actually overcome it by going one step lower, to Arel:

rels = [User.foo, User.bar(baz), User.joins(:event).temp]
cond = rels.map { |rel| rel.where_values.reduce(&:and) }.reduce(&:or)
User.left_joins(:event).where(cond)

The where_values property is an array of Arel::Nodes::Node instances, all of which are normally and-ed to get your query. You have to and them by hand, and then or the results.

If something does not work as expected, check the output of cond.to_sql in case you have missed something.

Upvotes: 1

Related Questions