Graham Slick Graham Slick - 4 months ago 13
SQL Question

Rails: ActiveRecord query is forcing ORDER BY after DISTINCT

I am using this query:

Post.where("created_at >= ? AND created_at <= ?", 1.month.ago.beginning_of_month , 1.month.ago.end_of_month)
.select('DISTINCT user_id')


Which returns 40 elements. However, I expect to get an array from this query, and to be able to iterate over this array with a
.each
. The issue is that when I try to use
.each
on the result of the query, I get this:


ActiveRecord::StatementInvalid: PG::InvalidColumnReference: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list
LINE 1: ...ted_at <= '2016-06-30 21:59:59.999999') ORDER BY created_at...
^
: SELECT DISTINCT user_id FROM "posts" WHERE (created_at >= '2016-05-31 22:00:00.000000' AND created_at <= '2016-06-30 21:59:59.999999') ORDER BY created_at DESC


It seems like this query forces a
ORDER BY
on the
created_at
, and makes it break since order_by isn't in the query.

To avoid this issue, I'm using:

Post.where("created_at >= ? AND created_at <= ?", 1.month.ago.beginning_of_month , 1.month.ago.end_of_month)
.map{|p| p.user_id}.uniq


Which is less efficient.

Any idea why I'm getting this error?

Answer

Try the following:

Post.where(created_at: (1.month.ago.beginning_of_month)..1.month.ago.end_of_month)
    .distinct.reorder(nil).pluck(:user_id)

As mentioned in the comments, this is due to a default_scope:

default_scope {order('created_at DESC')}
Comments