尝试使用PostgreSQL检索按日期分组的ActiveRecord对象数组。
更具体地说，我正在尝试翻译以下MySQL查询：
@posts = Post.all(:group => "date(date)",
:conditions => ["location_id = ? and published = ?", @location.id, true],
:order => "created_at DESC")
我知道PostgreSQL对SQL标准的解释比MySQL更严格，因此这种类型的查询将无法工作......并且已经阅读了StackOverflow和其他主题上的一些帖子 - 但它们似乎都不是关于这个问题的明确答案
我已经尝试过各种各样的查询组合，分组和不同的条款没有太大的乐趣 - 目前我有一个相当不优雅的黑客，虽然作品让我脸红时看着它。
使用Rails和PostgreSQL进行此类查询的正确方法是什么？ （忽略肯定这应该在ActiveRecord级别抽象出来的事实）
您想在这里使用的PostgreSQL功能是 DISTINCT ON。通过ActiveRecord进行此查询有两种基本方法。
第一种方法是只指定 :select 和 :order 选项。当你有一个相当简单的查询没有时，这很有用 :joins 要么 :include。
Post.all(
:select => 'DISTINCT ON (date::date) *',
:order => 'date::date DESC, created_at DESC'
)
如果您有一个更复杂的查询，其中ActiveRecord生成自己的查询 SELECT 子句，您可以使用子查询来选择目标记录。
Post.all(
:joins => 'INNER JOIN (SELECT DISTINCT ON (date::date) id FROM posts ORDER BY date::date DESC, created_at DESC) x ON x.id = posts.id'
)
请注意，根据您的数据，这可能比第一种方法慢一点。如果需要，我只会使用这种方法。务必使用类似生产的数据进行基准测试。
我的解决方案
def self.columns_list
column_names.collect { |c| "#{table_name}.#{c}" }.join(",")
end
scope :selling, joins(:products).group(columns_list)
简单且可重复。
虽然SQL在回答“每天最近的帖子是什么时候？”这样的问题时非常简单。当你问“哪一天是每天最近的帖子？”时，这不是很直接的。
如果不使用子SELECT（或多个SQL语句），则无法检索每天的最新Post。这可能适合你（使用Post.find_by_sql或类似的）：
SELECT P.*, M.just_day, M.max_created_at
FROM posts P
JOIN (
SELECT date(P2.date) AS just_day, MAX(P2.created_at) AS max_created_at
FROM posts P2
P.location_id='12345' AND P.published=true
GROUP BY date(P2.date)
) AS M
ON AND M.max_created_at = P.created_at
WHERE P.location_id='12345' AND P.published=true
上面的SQL语句应该足够了 如果 您可以确定两个帖子在created_at列中的值不同。如果你不能保证在创建的列中保持唯一性，那么你要么需要过滤掉Ruby中的重复项（这不应该太低效，因为可能你会在列表中循环）或者你需要做N +1 SQL语句。 （实际上你可以进行每行选择，但是AFAIK和N + 1 SQL语句一样低效。）
以下是循环时删除重复项的方法：
last_post = nil
posts.each do |post|
unless post.just_day == last_past.try(:just_day)
# Do stuff
last_post = post
end
end
也就是说，你可以用Ruby / ActiveRecord很好地编写它，如果你有足够的日子，那么每天的SELECT也不会太糟糕：
days = Post.group("date(date)")
posts = days.each { |day| Post.order('created DESC').where("date(day) = ?", day) }
如果您正在使用分页（每页10个项目），那么每个页面需要11个SQL语句。不是想法，但简单可能值得效率低下。
老实说，如果您希望此查询既经常运行又具有相当大的数据集，那么我建议您添加一个名为most_recent的布尔列。过去几天的最后一篇文章不会改变。你只需要担心今天的帖子。只需设置一个cron作业，在一天结束后运行几分钟，以更新最后一天的值。如果你想要更新的东西，你可以每5分钟运行一次cron作业。或者，如果您需要实时，则添加一个after_save回调，将当前发布的所有帖子的most_recent设置为false。
这个问题类似： MySQL：获得用户的最高分