带连接的 Rails name_scopes

发布于 2024-07-06 19:22:06 字数 764 浏览 16 评论 0原文

我正在尝试创建一个使用联接的named_scope，但尽管生成的SQL 看起来正确，但结果却是垃圾。例如：（

class Clip < ActiveRecord::Base      
  named_scope :visible, {
    :joins => "INNER JOIN series ON series.id = clips.owner_id INNER JOIN shows on shows.id = series.show_id", 
    :conditions=>"shows.visible = 1 AND clips.owner_type = 'Series' "
  }

剪辑属于系列，系列属于节目，节目可以是可见的或不可见的）。

Clip.all 的作用：

SELECT * FROM `clips`

Clip.visible.all 的作用：

SELECT * FROM `clips` INNER JOIN series ON series.id = clips.owner_id INNER JOIN shows on shows.id = series.show_id WHERE (shows.visible = 1 AND clips.owner_type = 'Series' )

这看起来不错。但生成的剪辑模型数组包含一个 ID 不在数据库中的剪辑 - 它会选择一个节目 ID。我哪里出错了？

原文

I'm trying to create a named_scope that uses a join, but although the generated SQL looks right, the result are garbage. For example:

class Clip < ActiveRecord::Base      
  named_scope :visible, {
    :joins => "INNER JOIN series ON series.id = clips.owner_id INNER JOIN shows on shows.id = series.show_id", 
    :conditions=>"shows.visible = 1 AND clips.owner_type = 'Series' "
  }

(A Clip is owned by a Series, a Series belongs to a Show, a Show can be visible or invisible).

Clip.all does:

SELECT * FROM `clips`

Clip.visible.all does:

SELECT * FROM `clips` INNER JOIN series ON series.id = clips.owner_id INNER JOIN shows on shows.id = series.show_id WHERE (shows.visible = 1 AND clips.owner_type = 'Series' )

This looks okay. But the resulting array of Clip models includes a Clip with an ID that's not in the database - it's picked up a show ID instead. Where am I going wrong?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

心病无药医 2024-07-13 19:22:06

问题在于“SELECT *” - 查询按顺序从剪辑、系列和节目中选取所有列。每个表都有一个 id 列，这会导致结果中的命名列之间发生冲突。最后一个 id 列（从显示中）拉回会覆盖您想要的列。您应该将 :select 选项与 :joins 一起使用，例如：

named_scope :visible, {
  :select => "episodes.*",
  :joins => "INNER JOIN series ON series.id = clips.owner_id INNER JOIN shows on shows.id = series.show_id", 
  :conditions=>"shows.visible = 1 AND clips.owner_type = 'Series' "
}

The problem is that "SELECT *" - the query picks up all the columns from clips, series, and shows, in that order. Each table has an id column, and result in conflicts between the named columns in the results. The last id column pulled back (from shows) overrides the one you want. You should be using a :select option with the :joins, like:

named_scope :visible, {
  :select => "episodes.*",
  :joins => "INNER JOIN series ON series.id = clips.owner_id INNER JOIN shows on shows.id = series.show_id", 
  :conditions=>"shows.visible = 1 AND clips.owner_type = 'Series' "
}

回复收藏 0 原文