将 SQL 转换为 Rails 3 ActiveRecord

Question

我正在寻找最像 Rails 的方式来检索给定专辑中可用歌曲的不同类别列表。

这是在 SQL 中的 album_id = 1

-- Using subselects
select * from categories where id in (
  select distinct category_id
  from categorizations
  where song_id in (select song_id from album_songs
                    where album_id = 1 and available = 't')
)
order by name asc;

-- Using joins
select distinct c.* from categories c
  inner join categorizations cz on c.id = cz.category_id
  left join album_songs a on cz.song_id = a.song_id
where a.album_id = 1 and a.available = 't'
order by c.name asc;

我的工作（尽管很天真！）尝试将其移植到 ActiveRecord

## attempting to do it like subselects (although they're not really
## subselects, it executes them individually -- from what i've read
## ActiveRecord won't do subselects?)
Category.where('id IN (?)',
  Categorization.select('DISTINCT category_id').where('song_id IN (?)',
    Album.find(1).songs.available.map(&:song_id)
  ).map(&:category_id)
).order('name ASC')

## joins - although at this point it's pretty much all sql
## as i couldn't find a way to do the left join in pure AR
## i'm also duplicating my AlbumSongs.available scope -- is
## that scope reusable here? (outside the AlbumSongs model?)
Category.select('DISTINCT categories.*')
        .joins(:categorizations,
               'LEFT OUTER JOIN album_songs ON categorizations.song_id = album_songs.song_id')
        .where('album_songs.album_id = ? and available', 1)

我将使用最后一个，但似乎我也可以用 SQL 编写它？

有什么方法可以改进它，使其更加 Rails 风格吗？

Answer 1

好吧，如果您发布模型设置肯定会有帮助。但假设：
* 歌曲 has_many :categories, :through =>分类
* 专辑中的歌曲数量并不多

为什么不这样做：

Album.includes({:songs => {:categorizations => :categories}}).find(1).songs.collect {|s| s.category}.flatten.uniq