Rails的ActiveRecord的查询:计数,组和额外列 [英] Rails ActiveRecord Query: Count, group and extra column
问题描述
我有一个名为模型服务
每个服务都有一个或多个用法
的特定歌曲
(加入了 usage.song_id
)。我想通过用途搜索和找到最常见的歌曲:
Usage.joins(:曲)。集团(:song_id,:song_name).Count之间:song_id
我希望这些命令进行的计数值,所以我加入(之前 .Count之间
)
.order(count_song_id DESC)
但现在我也希望最新的使用这首歌是存储在服务
模式的一个细节的日期。所以我知道我需要添加到我的加入:
Usage.joins(:歌曲,:服务)
所以基本上现在我有:
Usage.joins(:歌曲,:服务)。集团(:song_id,:song_name)。.order(count_song_id DESC)数:song_id
但我怎么得到的最新的的日期为传唱输出每首歌曲?
更新:理想的情况我在寻找的输出是这样的:
[
:song_id,
:song_name,
count_of_this_songs_usages,
most_recent_date_this_song_was_used
]
嗯,我想这一点。无论是在SELECT语句中两个函数实际上是正常工作还有待观察。
Usage.select(MAX(services.date):song_name,COUNT(song_id)AS count_song_id)。加入(:服务:曲)。集团(: song_name).order(count_song_id DESC)
I have a model called service
each service has one or more usage
of a particular song
(joined on usage.song_id
). I want to search through the usages and find the most common songs:
Usage.joins(:song).group(:song_id, :song_name).count :song_id
I want those ordered by the count value so I add (just before .count
)
.order("count_song_id DESC")
But now I also want the date of the most recent use of that song which is one of the details stored in the service
model. So I know I need to add to my join:
Usage.joins(:song, :service)
So basically now I have:
Usage.joins(:song, :service).group(:song_id, :song_name).order("count_song_id DESC").count :song_id
But how do I get the most recent date for each song sung in the output?
Update: Ideally I'm looking for output something like this:
[
:song_id,
:song_name,
count_of_this_songs_usages,
most_recent_date_this_song_was_used
]
Well, I'm trying this. Whether two functions in the select statement are actually working correctly remains to be seen.
Usage.select("MAX(services.date)", :song_name, "COUNT(song_id) AS count_song_id").joins(:service, :song).group(:song_name).order("count_song_id DESC")
这篇关于Rails的ActiveRecord的查询:计数,组和额外列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!