** This issue came up in a Rails 3.0.11 app **
has_many :categorizations
has_many :categories, :through => :categorizations
scope :not_news, includes(:categories).where(Category.arel_table[:name].not_eq('news'))
scope :by_date, order('articles.post_date DESC, articles.id DESC')
Article.not_news.by_date.limit 5
Article Load (2.3ms) SELECT * FROM (SELECT DISTINCT ON ("articles".id) "articles".id, articles.post_date AS alias_0, articles.id AS alias_1 FROM "articles" LEFT OUTER JOIN "categorizations" ON "articles"."id" = "categorizations"."categorized_id" AND "categorizations"."categorized_type" = 'Article' LEFT OUTER JOIN "categories" ON "categories"."id" = "categorizations"."category_id" WHERE ("categories"."name" != 'news')) AS id_list ORDER BY id_list.alias_0 DESC LIMIT 5
Article Load (1.9ms) SELECT "articles"."id" AS t0_r0, "articles"."title" AS t0_r1, "articles"."summary" AS t0_r2, "articles"."body" AS t0_r3, "articles"."post_date" AS t0_r4, "articles"."created_at" AS t0_r5, "articles"."updated_at" AS t0_r6, "categories"."id" AS t1_r0, "categories"."name" AS t1_r1, "categories"."topic" AS t1_r2, "categories"."created_at" AS t1_r3, "categories"."updated_at" AS t1_r4, "categories"."parent_id" AS t1_r5, "categories"."lft" AS t1_r6, "categories"."rgt" AS t1_r7 FROM "articles" LEFT OUTER JOIN "categorizations" ON "articles"."id" = "categorizations"."categorized_id" AND "categorizations"."categorized_type" = 'Article' LEFT OUTER JOIN "categories" ON "categories"."id" = "categorizations"."category_id" WHERE "articles"."id" IN (512, 372, 371, 370, 369) AND ("categories"."name" != 'news') ORDER BY articles.post_date DESC, articles.id DESC
Here's what I want to see: The "top" articles by post_date DESC and then their id DESC. The problem is that the first query "the ID selection query" only orders by the post_date.
Say the database looks like and assume all of these are "not news":
[{id: 1, post_date: yesterday}, {id: 2, post_date: yesterday}, {id:3, post_date: today}]
Article.not_news.by_date.limit 2 - would return ids...
[3, 1]
But Article.not_news.by_date.limit 3 - would return ids...
[3, 2, 1]
It is expected / desirable for the first query to return...
[3, 2]
I've temporarily resolved this by the scope:
scope :not_news, joins(:categories).where(Category.arel_table[:name].not_eq('weekly')).select('DISTINCT articles.*')
But I don't really want to mix in SQL if I don't have to...