CODEWITHSUNDEEP
ruby-on-railsrubyactiverecordmodel
Remy Wang
Remy Wang

Reputation: 793

Ruby on Rails - order not working with distinct.pluck

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?

Upvotes: 3

Views: 2506

Answers (2)

m.seliverstov
m.seliverstov

Reputation: 305

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.

Upvotes: 0

Nuclearman
Nuclearman

Reputation: 5304

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

Upvotes: 2

Related Questions