ActiveRecord OR query with two scopes in SQL where one scope has a join

Question

I want to write a query which combines two scopes with an "OR" query (using Rails 4 though the issue is still there with rails 5).

Model1

scope :association_has_email, -> { joins(:model2).where.not(model2s:{email:nil}) }
scope :description_has_email, -> { where("description ~* ?", email_regex) }

(where email_regex is a regular expression picking out an email).

Which gives us SQL like:

SELECT \"model1s\".* FROM \"model1s\" WHERE (description ~* '[[:<:]][A-Z0-9._%+-]+@[A-Z0-9.-]+.[A-Z]{2,4}[[:>:]]')
SELECT \"model1s\".* FROM \"model1s\" INNER JOIN \"model2s\" ON \"model2s\".\"id\" = \"model1s\".\"model2_id\" WHERE (\"model2s\".\"email\" IS NOT NULL)

Create a scope to pick out those opportunities where either the email is in the association or an embedded email in the text.

How do you write an "OR" query where one side needs the join and the other doesn't?

See http://stackoverflow.com/questions/31096009/activerecord-or-query-hash-notation — Slava.K, Feb 09 '17 at 12:56
With where-or gem (which is brilliant...) you get a "ArgumentError: Relation passed to #or must be structurally compatible" However, if you pass a joins to the scope you get an issue where if the email_in_description has no model2 at all it is excluded. However, think I have a solution. Can pass includes to get an outer join... — Carpela, Feb 09 '17 at 13:09
I always suggest people to use raw SQL where ActiveRecord DSL fails to be a great tool. — MurifoX, Feb 09 '17 at 13:29
Unfortunately, see my answer below, to use raw SQL in this case would be utterly, utterly hideous due to the need to use the includes syntax. That said, you could probably write that LEFT OUTER JOIN manually without all the attribute gubbins. — Carpela, Feb 09 '17 at 13:33

score 0 · Answer 1 · answered Feb 09 '17 at 13:01

0

You can create one more scope like following:

scope :check_email, -> {
  if email is embedded
    association_has_email
  else
    description_has_email
  end
}

answered Feb 09 '17 at 13:01

Sapna Jindal

412
3
11

What is "embedded" here? – Carpela Feb 09 '17 at 13:30
This is just to check where the email you want to search – Sapna Jindal Feb 09 '17 at 13:32
Bit confused. What we're trying to do is find out if there is 'any' sort of email in one of our records. We're trying to work out whether we have a means of communicating back directly. i.e. we don't know in advance whether we have the email there, that's what the scope is trying to filter) – Carpela Feb 09 '17 at 13:35
Cant you do this then - `Model1. association_has_email.exists? || Model1. description_has_email .exists?` – Sapna Jindal Feb 09 '17 at 13:37

Carpela · Accepted Answer · 2017-02-09T13:28:01.193

Ok. For Rails 4, the where-or gem provides a fix for generating or queries as in rails 5, however if you pass the scopes above you end up with a

 ArgumentError: Relation passed to #or must be structurally compatible

You can use the following:

scope :email_somewhere, -> { association_has_email.or(Model1.joins(:model2).description_has_email }

SELECT \"model1s\".* FROM \"model1s\" INNER JOIN \"model2s\" ON \"model2s\".\"id\" = \"model1s\".\"model2_id\" WHERE ((\"model2s\".\"email\" IS NOT NULL) OR (description ~* '[[:<:]][A-Z0-9._%+-]+@[A-Z0-9.-]+.[A-Z]{2,4}[[:>:]]'))

Works, but it will exclude anything that has an email in the description but doesn't have a model2, because it uses an inner join.

But by using includes, you can get the desired result.

scope :association_has_email, -> { includes(:model2).where.not(model2s:{email:nil}) }
scope :description_has_email, -> { where("description ~* ?", email_regex) }

Means you can use

scope :email_somewhere, -> { association_has_email.or(Model1.includes(:model2).description_has_email }

The difference is the SQL pulls all of the attributes from model1, and then adds a left outer join for the model2 query.

SELECT [...all the model1 attributes...] LEFT OUTER JOIN \"model2s\" ON \"model2s\".\"id\" = \"model1s\".\"model2_id\" WHERE ((\"model2s\".\"email\" IS NOT NULL) OR (description ~* '[[:<:]][A-Z0-9._%+-]+@[A-Z0-9.-]+.[A-Z]{2,4}[[:>:]]'))

Irritating if you really needed to use an inner join for the first one, but works for me.

ActiveRecord OR query with two scopes in SQL where one scope has a join

2 Answers2