35

I have two queries, I need an or between them, i.e. I want results that are returned by either the first or the second query.

First query is a simple where() which gets all available items.

@items = @items.where(available: true)

Second includes a join() and gives the current user's items.

@items =
  @items
  .joins(:orders)
  .where(orders: { user_id: current_user.id})

I tried to combine these with Rails' or() method in various forms, including:

@items =
  @items
  .joins(:orders)
  .where(orders: { user_id: current_user.id})
  .or(
    @items
    .joins(:orders)
    .where(available: true)
  )

But I keep running into this error and I'm not sure how to fix it.

Relation passed to #or must be structurally compatible. Incompatible values: [:references]
Andrey Deineko
  • 49,444
  • 10
  • 105
  • 134
frostbite
  • 558
  • 2
  • 5
  • 11

3 Answers3

31

There is a known issue about it on Github.

According to this comment you might want to override the structurally_incompatible_values_for_or to overcome the issue:

def structurally_incompatible_values_for_or(other)
  Relation::SINGLE_VALUE_METHODS.reject { |m| send("#{m}_value") == other.send("#{m}_value") } +
    (Relation::MULTI_VALUE_METHODS - [:eager_load, :references, :extending]).reject { |m| send("#{m}_values") == other.send("#{m}_values") } +
    (Relation::CLAUSE_METHODS - [:having, :where]).reject { |m| send("#{m}_clause") == other.send("#{m}_clause") }
end

Also there is always an option to use SQL:

@items
  .joins(:orders)
  .where("orders.user_id = ? OR items.available = true", current_user.id)
Andrey Deineko
  • 49,444
  • 10
  • 105
  • 134
  • 4
    Went with SQL queries instead. Thanks for the info. – frostbite Nov 22 '16 at 13:38
  • 1
    @Andrey Should have referred to my answer instead of editing yours and adding the same. Anyway, +1 for the first solution. – Rajdeep Singh Nov 22 '16 at 13:43
  • @RSB i was going to start with sql part, than googled error message and found a github issue. After all I still decided to add my initial thought, since it's the less code to make it work. – Andrey Deineko Nov 22 '16 at 13:47
22

You can write the query in this good old way to avoid error

@items = @items.joins(:orders).where("items.available = ? OR orders.user_id = ?", true, current_user.id)

Hope that helps!

Rajdeep Singh
  • 17,174
  • 6
  • 49
  • 74
12

Hacky workaround: do all your .joins after the .or. This hides the offending .joins from the checker. That is, convert the code in the original question to...

@items =
  @items
  .where(orders: { user_id: current_user.id})
  .or(
    @items
    .where(available: true)
  )
  .joins(:orders) # sneaky, but works!

More generally, the following two lines will both fail

A.joins(:b).where(bs: b_query).or(A.where(query))  # error!  

A.where(query).or(A.joins(:b).where(bs: b_query))  # error!

but rearrange as follows, and you can evade the checker:

A.where(query).or(A.where(bs: b_query)).joins(:b)  # works

This works because all the checking happens inside the .or() method. It's blissfully unaware of shennanigans on its downstream results.

One downside of course is it doesn't read as nicely.

nar8789
  • 617
  • 6
  • 9
  • 1
    This fixed the issue for me. This really _should_ be the answer. – hernan43 Mar 30 '21 at 17:34
  • 1
    Fixed for me to. Thanks ! joins and includes must be added after or. Can someone explain why, if this works, rails does not accept us to add it before 'or' ? – LiKaZ Apr 12 '21 at 13:48
  • 1
    @LiKaZ for `joins`, I don't think any good reason. In fact, after rails 6.1.3 I think you you can put the `.joins` before the `.or`, and I know for fact you can put `.includes` before the `.or`. As for why this exists in the first place... Rails has to restrict certain operations like `.limit` or `.distinct`, as allowing these before `.or` would be untranslatable to sql. (What would it mean to `.or` together two clauses with different `.limit`s?) Longer explanation of all cases here: https://github.com/rails/rails/issues/24055#issuecomment-793274937 – nar8789 Apr 12 '21 at 16:20
  • @nar8789 thanks for these explainations. I will upgrade my Rails 6.0 application to 6.1.3 soon ! – LiKaZ Apr 13 '21 at 09:59
  • 1
    Would have been interesting to know what the generated SQL looked like for your answer, because more than working it's important it does the right thing. – Nuno Costa May 27 '21 at 09:54
  • @NunoCosta Sure thing! here's a (partially redacted) test I just ran at the console: `puts A.where(id: 1).or(A.where(bs: { id: 1 })).joins(:b).to_sql` yields `SELECT "as".* FROM "as" INNER JOIN "bs" ON "bs"."id" = "as"."b_id" WHERE ("as"."id" = 1 OR "bs"."id" = 1)`. This looks like good SQL to me. This did reveal an error in pluralization in my original examples. I'll update that now. – nar8789 May 27 '21 at 19:11