Ruby on Rails - order not working with distinct.pluck

user_12438293 0
Viewed 1230

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?

model activerecord ruby-on-rails ruby
2 Answers

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) }

user_16082260

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.

user_37676580
Related