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?
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.
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
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.
