在Rails Group By查询中忽略Order By

Question

我所要做的就是根据过去7天内的印象数来排序帖子(当有人查看帖子时会产生印象)。这是我的命名作用域

scope :popular_last_week, unscoped
    .select("websites.*, COUNT(impressions.id) AS counted_impressions")
    .joins("INNER JOIN impressions ON websites.id = impressions.impressionable_id")
    .where("impressions.created_at >= ?", 7.days.ago)
    .where(:is_published => true)
    .group("websites.id")
    .order("counted_impressions DESC")

(counted_impressions用于避免post记录冲突)

然而，这会产生以下无效的SQL (counted_impressions不是有效列)：

SELECT COUNT(*) AS count_all, websites.id AS websites_id FROM "websites" INNER JOIN impressions ON websites.id = impressions.impressionable_id WHERE "websites"."is_published" = 't' AND (impressions.created_at >= '2013-01-11 17:48:03.954542') GROUP BY websites.id ORDER BY counted_impressions

似乎select语句被忽略了。我在哪里出错了，或者我应该怎么做，以便SQL语句是：

SELECT websites.*, COUNT(impressions.id) AS counted_impressions FROM "websites" INNER JOIN impressions ON websites.id = impressions.impressionable_id WHERE "websites"."is_published" = 't' AND (impressions.created_at >= '2013-01-11 17:42:57.771777') GROUP BY websites.id ORDER BY counted_impressions DESC

Answer 1

查询看起来应该是：

SELECT count(*) AS impression_count, w.id AS website_id 
FROM websites as w
INNER JOIN impressions as i ON w.id = i.impressionable_id 
WHERE w.is_published = 't' AND i.created_at >= '2013-01-11 17:48:03.954542'
GROUP BY w.id 
ORDER BY impression_count DESC;

似乎你可以跳过这个查询中的连接，然后在下一个查询上运行"is_published“检查，以获得实际的post数据……假设您只使用这个查询来获取获取数据的第二个查询的ID列表。