在rails中使用等效的数据库视图的最佳方法？

I have a working solution to this duct-taped together, but it relies on some pretty nasty queries that I'm afraid will make the application fairly brittle. Looking for advice on how to better implement the following.

我有一个工作的解决方案,这个管道绑在一起,但它依赖于一些非常讨厌的查询,我担心这将使应用程序相当脆弱。寻求有关如何更好地实施以下内容的建议。

I have models for Child, ChildStatus and StatusValue. The StatusValue has an 'active' flag. I want to then use this active status to filter most times that I pull children from the database (along the lines of a soft delete).

我有Child,ChildStatus和StatusValue的模型。 StatusValue具有“活动”标志。我想使用此活动状态来过滤我从数据库中拉出子项的大部分时间(沿着软删除的行)。

The Child model below works pretty swell for dealing with individual instances. However, it gets messy (at least the way I have done it) for pulling data in aggregate.

下面的Child模型在处理单个实例时效果非常好。然而,它总是变得混乱(至少我已经这样做了)。

class Child < ActiveRecord::Base
  ...
  has_many :child_statuses
  has_many :status_values, :through => :child_statuses

  ...

  def current_status
    child_statuses.last
  end

  def status_name
    if current_status
      current_status.status_value.name
    else
      "n/a"
    end
  end

  def status_active
    current_status && current_status.status_value.active
  end

end


class ChildStatus < ActiveRecord::Base
  belongs_to :child
  belongs_to :status_value, :foreign_key => :status_id
end


class StatusValue < ActiveRecord::Base
  has_many :child_statuses
end

With the above, when I want to pull a query of children in an active status, I end up with a big raw SQL query with bits like ...AND child_statuses.created_at = (SELECT MAX(created_at) FROM child_statuses WHERE child_id = children.id)

有了上述内容,当我想查询处于活动状态的子进程的查询时,我最终会得到一个大的原始SQL查询,其中包含... AND child_statuses.created_at =(SELECT MAX(created_at)FROM child_statuses WHERE child_id = children 。ID)

I feel like I'm falling back to my SQL comfort zone, but there is probably a better way to do this.

我觉得我正在回到我的SQL舒适区,但可能有更好的方法来做到这一点。

Shouldn't I be able to do something like Child.where(:status_value.active => "TRUE") ? (This throws an error - undefined method 'active' for :status_value:Symbol)

我不应该做像Child.where(:status_value.active =>“TRUE”)这样的事情吗? (这会引发错误 - 未定义的方法'active'表示:status_value:Symbol)

Any guidance is greatly appreciated. I have thought of exploring a database view to make it work also, but I'm not convinced that it will help.

非常感谢任何指导。我曾考虑过探索一个数据库视图来使其工作,但我不相信它会有所帮助。

2 个解决方案

#1

You can create the database view (use execute "CREATE VIEW my_view AS..." in your migration) and point an ActiveRecord model at it instead of at a table.

您可以创建数据库视图(在迁移中使用执行“CREATE VIEW my_view AS ...”)并将ActiveRecord模型指向它而不是表。

class MyView < ActiveRecord::Base
  def readonly?
    true
  end
end

#2

Did you try Child.includes(:status_values).where('status_values.active = true')?

您是否尝试过Child.includes(:status_values).where('status_values.active = true')?

Disclaimer: I'm not clear on the difference between Child.joins() and Child.includes().

免责声明:我不清楚Child.joins()和Child.includes()之间的区别。

#1