查找记录，其中多态关联或为零 [英] Finding record where polymorphic association or nil

问题描述

我有一个运行ruby 2.1.2和rails 4.1.1的Rails应用程序，其中有一个多态关联，如下所示:

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

我希望能够找到属于给定员工或没有关联的可成像"记录的所有图片.

# 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])

看到运行的SQL后，它不起作用的原因很明显

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))

当我需要的时候

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

有没有办法在不编写SQL的情况下在rails中运行查询?

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

推荐答案

只需尝试以下操作:

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))

修改

在Rails 4.1上，动态定义where_values在and(other)，并通过reduce(:and)可以将它们全部聚集在一起.

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.

在Rails 4.2上不起作用.您可以使其正常运行，但变得奇怪(请参阅:

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?

使用普通查询(1):

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

与Arel(2)一起玩:(如@cristian所说)

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) ))

使用复杂的where_values(3):

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) )

我的选择:在这种情况下，为普通查询(1).我的第一个答案(我一直在使用)停止在4.2上工作，需要重构(3). Arel使您从数据库管理器中独立出来(我建议学习Arel).您发布的答案给了我一些奇怪的感觉，因为它可以正常工作，但使用的条件无效:理论上，搜索返回imageable_id IS NULL AND imageable_type = 'Employee'.

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.

这篇关于查找记录，其中多态关联或为零的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！