2

I have a rails app running ruby 2.1.2 and rails 4.1.1 and in it I have a polymorphic association like so:

class Picture < ActiveRecord::Base
  belongs_to :imageable, polymorphic: true
end

class Employee < ActiveRecord::Base
 has_many :pictures, as: :imageable
end

class Product < ActiveRecord::Base
  has_many :pictures, as: :imageable
end

I want to be able to find all Pictures that either belong to a given Employee or have no associated "imageable" record.

# These both work fine
Picture.where(imageable: Employee.first) # finds all associated with Employee.first
Picture.where(imageable: nil) # finds all un-associated Pictures

#This does not work
Picture.where(imageable: [Employee.first, nil])

The reason it does not work is obvious once you see the SQL that is run

SELECT "pictures".* FROM "pictures"
WHERE "pictures"."imageable_type" = 'Employee' AND 
      (("pictures"."imageable_id" = 1 OR "pictures"."imageable_id" IS NULL))

When instead I need

SELECT "pictures".* FROM "pictures"
WHERE (("pictures"."imageable_type" = 'Employee' AND "pictures"."imageable_id" = 1)) OR 
      "pictures"."imageable_id" IS NULL

Any idea how to run the query in rails without writing out the SQL?

John Kacz
  • 404
  • 5
  • 15

3 Answers3

2

Just try this:

from_employee = Picture.where(imageable: Employee.first).where_values.reduce(:and)
from_nil = Picture.where(imageable: nil).where_values.reduce(:and)

@from_employee_or_nil = Picture.where(from_employee.or(from_nil))

Edit

On Rails 4.1 the where_values is defined dynamicaly at query_methods. It map the Arel::Nodes "Conditions" of all the conditions related to the current ActiveRecord::Relation. This nodes respond to and(other) and with reduce(:and) you can get all of them together.

On Rails 4.2 this doesn't work. You can make it work, but it turns odd (see: OR operator in WHERE clause with Arel in Rails 4.2 -the edit on the question-). What can we do?

Use a plain query(1):

@employee = Employee.first
Picture.where("(pictures.imageable_type = ? AND pictures.imageable_id = ?) OR pictures.imageable_id IS NULL", @employee.class, @employee.id)

Hack with Arel(2): (as @cristian says)

@employee = Employee.first
Picture.where( ( Picture.arel_table[:imageable_type].eq(@employee.class).and( Picture.arel_table[:imageable_id].eq(@employee.id) )).or(Picture.arel_table[:imageable_id].eq(nil) ))

Using a complex where_values(3):

employee_scope = Picture.where(imageable: Employee.first)
nil_scope = Picture.where(imageable: nil)

Picture.where( employee_scope.where_values.reduce(:and).or(nil_scope.where_values.reduce(:and)).to_sql, employee_scope.bind_values.map(&:last) + nil_scope.bind_values.map(&:last) )

My choice: for this case, the plain query (1). My first answer (that I've been using), stop of working at 4.2 and needs a refactor (3). Arel gives you independence from db manager (I recommend to learn Arel). And the answer you publish give me some weird feel, because it works fine but it use an invalid condition: imageable_id IS NULL AND imageable_type = 'Employee' are theoretically returned by the search.

Community
  • 1
  • 1
Alejandro Babio
  • 5,189
  • 17
  • 28
  • Thanks. I'll try this out. Do you mind explaining (or linking to more info about) how this works? – John Kacz Mar 23 '15 at 02:33
  • Thanks. It seems the plain query is my best option if I want to keep it simple. Even if that's what I said I didn't want to use in the question itself. Arel does seem like something I need to learn more. So I might go with it just to get practice... I'll wait a bit to see what other's say if any join in. – John Kacz Mar 24 '15 at 13:53
1

In order to obtain the exact query you are looking for use AREL:

employee_condition = Picture.arel_table[:imageable_id]
  .eq( Employee.first )
  .and( Picture.arel_table[:imageable_type].eq('Employee') )

nil_condition = Picture.arel_table[:imageable_id].eq(nil)

Picture.where(
  Picture.arel_table.grouping(employee_condition).or(nil_condition)
)

This will generate:

SELECT "pictures".* FROM "pictures" WHERE (
  ("pictures"."imageable_id" = 1 AND "pictures"."imageable_type" = 'Employee')
  OR "pictures"."imageable_id" IS NULL
)

To understand better what is happening in the code above read Using Arel to Compose SQL Queries and Rails/Arel - precedence when combining AREL predicates

Community
  • 1
  • 1
cristian
  • 8,676
  • 3
  • 38
  • 44
  • Thanks! I've seen people mention Arel but thought it was a third-party gem. I'll give this a shot. It doesn't look as clean as the answer I gave but it actually does the query I want. I wonder what the performance difference is. – John Kacz Mar 23 '15 at 02:35
  • It is verbose, but if you refactor a little bit it will look better. ActiveRecord uses AREL underneath, so the difference in performance should no exists. Behind the scenes, ActiveRecord uses Arel to build the queries and ultimately calls out to it to get the final SQL before shipping it to the database of your choice. More info here: http://jpospisil.com/2014/06/16/the-definitive-guide-to-arel-the-sql-manager-for-ruby.html and here: http://www.slideshare.net/camerondutro/advanced-arel-when-activerecord-just-isnt-enough – cristian Mar 23 '15 at 09:02
  • Thanks cristian. I saw Jiří's post but Cameron's slides were really helpful. For this relatively simple query, I'm starting to think writing out the plain query is actually the cleanest option, but I'm glad to know more about the power of Arel. – John Kacz Mar 24 '15 at 13:55
  • Also, by performance I meant the difference between Query 1 `(A is 'X' OR A is Null) AND (B is 'Y' OR B is NULL)` and Query 2 `(A is 'X' AND B is 'Y') AND (B is NULL)` You can see in the edit to my answer (Which was a way to get Query 1) that the performance question is moot as 1 and 2 are not equivalent for my application, because there are times when A is 'Z' and Bis NULL that Query 1 doesn't catch. – John Kacz Mar 24 '15 at 14:03
0

So the following works, but I'm not sure if it is the best or even the only way:

@empolyee = Employee.first
Picture.where(imageable_type: ["Employee", nil], imageable_id: [@empolyee.id, nil])

This runs the following which I'm not sure if it is slower or not.

SELECT "pictures".* FROM "pictures"
  WHERE (("pictures"."imageable_type" = 'Organization' OR "pictures"."imageable_type" IS NULL)) AND
        (("pictures"."imageable_id" = 1 OR "pictures"."imageable_id" IS NULL))

EDIT

While this works there is a possible edge case that fails.

I think that if a Picture belong to a Product and that Product is deleted (and my dependent strategy is set to nullify) then the Picture with have null as the 'imageable_id' but still have "Product" as the 'imageable_type'.

Such a Picture would not be found by my above answer as "imageable_type" is neither "Employee" nor NULL.

John Kacz
  • 404
  • 5
  • 15