Ruby on Rails - order not working with distinct.pluck

Question

app/model/line_item.rb

class LineItem < ApplicationRecord
  default_scope { order(:order_date, :line_item_index) }
  scope :sorted, -> { order(:order_date, :line_item_index) }
  scope :open_order_names, -> { distinct.pluck(:order_name) }
end

What I have tried:

LineItem.open_order_names        # Way 1
LineItem.sorted.open_order_names # Way 2
LineItem.open_order_names.sorted # Way 3

But I am always getting this error.

ActiveRecord::StatementInvalid (PG::InvalidColumnReference: ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select list
LINE 1: ...ne_items"."order_name" FROM "line_items" ORDER BY "line_item...
                                                                 ^
):

Anyone can help me?

Does this answer your question? [PG::Error: SELECT DISTINCT, ORDER BY expressions must appear in select list](https://stackoverflow.com/questions/12693089/pgerror-select-distinct-order-by-expressions-must-appear-in-select-list) — Jake Worth, Sep 17 '20 at 18:46
Sorry to hear! It's definitely a database, not Ruby, issue. To summarize what that post and others seem to be saying: if you collapse your results down to only distinct `order_name`s, when there is a duplicate `order_name`, from which record should the `order_date` or `line_item_index` be chosen for the ordering? Postgres can't decide. — Jake Worth, Sep 18 '20 at 13:13

score 2 · Answer 1 · answered Sep 17 '20 at 19:06

2

The issue is that you need to specify how they should be distinct, the following should work for you, the select may not be needed.

scope :open_order_names, -> { select(:order_name).distinct(:order_name).pluck(:order_name) }

answered Sep 17 '20 at 19:06

Nuclearman

5,029
1
19
35

Thank you for your reply, but *LineItem.open_order_names.sorted* doesn't work after I have updated that scope. – Remy Wang Sep 17 '20 at 22:07
Yea it's the `select`, see if you can remove it, if you can't, may may need to be more specific with what columns you pull from the database. Though slight chance `select(:*)` works, but that should be what the default (without select) is so kind of doubt it. – Nuclearman Sep 19 '20 at 03:49
Another option is to just add a unique field that you can use instead to avoid the need for a distinct using a where or join where the combination is unique works well, that's usually how I do it. I try to avoid using distinct as much as possible precisely because of this, it get's messy. – Nuclearman Sep 19 '20 at 03:52

score 0 · Answer 2 · answered Sep 17 '20 at 22:00

So it's database restriction. For example we have users table with (id, email). You can do:

SELECT DISTINCT "users"."email" FROM "users"

or

SELECT "users"."email" FROM "users" ORDER BY "users"."id" ASC

but can not:

SELECT DISTINCT "users"."email" FROM "users" ORDER BY "users"."id" ASC

i.e. you can not order by column which abcent in the SELECT part of query if you use the DISTINCT.

As mentioned above the

scope :open_order_names, -> { select(:order_name).distinct(:order_name).pluck(:order_name) }

could be nice solution.

Ruby on Rails - order not working with distinct.pluck

2 Answers2