PostgreSql SELECT DISTINCT and ORDER BY returns an error

Rails
1

Hello,

Given:

  • a Dealer model and controller
  • index page with a search form on top and results shown below

Problem:

I’d like to select dealers based on keyword, status and program then
order them by name/launched_at asc/desc. The query joins in other tables
that are needed to perform the ‘by keyword’ part of the search, so I get
this error:

PGError: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear
in select list
: SELECT DISTINCT dealers.* FROM “dealers” LEFT OUTER JOIN contacts ON
(contacts.contactable_id = dealers.id)
LEFT OUTER JOIN w9_tax_forms ON
(w9_tax_forms.dealer_id = dealers.id) WHERE (LOWER(dealers.name) LIKE
‘%dino%’ or LOWER(contacts.email) LIKE ‘%dino%’ or dealers.tax_id_code
LIKE ‘%dino%’ or w9_tax_forms.tax_id_code LIKE ‘%dino%’) ORDER BY
LOWER(dealers.name) asc LIMIT 10 OFFSET 0

dealers_controller.rb

@dealers = Dealer.search(search_filters) # params

dealer.rb

search = account.dealer_search_base # returns Dealer.scoped({}) in

this case

search = search.with_keyword(keyword)
search = search.with_status(options[:status_id])
search = search.by_program(options[:program_id])
search = search.order(options[:sort], options[:direction])

paginate ? search.paginate(:page => page, :per_page => 10) :

search.all

scopes.rb

named_scope :with_status, lambda { |status_id|

logger.debug("…with_status(#{status_id.to_i > 0})"); (status_id &&
status_id.to_i > 0) ? {:conditions => { :status_id => status_id } } : {}
}

named_scope :with_keyword, lambda { |k| k.present? ? { :conditions

=> [‘LOWER(dealers.name) LIKE ? or LOWER(contacts.email) LIKE ? or
dealers.tax_id_code LIKE ? or w9_tax_forms.tax_id_code LIKE ?’,
“%#{k.downcase}%”, “%#{k.downcase}%”, “%#{k}%”, “%#{k}%” ], :joins =>
“LEFT OUTER JOIN contacts ON (contacts.contactable_id = dealers.id)
LEFT OUTER JOIN w9_tax_forms ON
(w9_tax_forms.dealer_id = dealers.id)”, :select => “DISTINCT dealers.*”
} : {} }

 named_scope :by_program, lambda { |program_id| program_id.present?

? { :conditions =>
‘dealers.program_id = ?’, program_id] } : {} }

 named_scope :order, lambda {|sort, direction| {:order =>

“LOWER(dealers.#{sort}) #{direction}, dealers.status_id #{direction},
dealers.established #{direction}”} }

The problem is that PostgreSql requires the SELECT DISTINCT clause to be
followed by the ORDER BY criteria, so it needs to be inserted before the
joins take action. I don’t know how to do this, if even possible.
Currently using Rails -v 2.3.8 and postgres (0.7.9.2008.01.28) gem.

Suggestions or ideas anyone?

2

On Oct 27, 2010, at 2:48 PM, Gabi Ge wrote:

order them by name/launched_at asc/desc. The query joins in other tables
LIKE ‘%dino%’ or w9_tax_forms.tax_id_code LIKE ‘%dino%’) ORDER BY
search = account.dealer_search_base # returns Dealer.scoped({}) in
scopes.rb
“LEFT OUTER JOIN contacts ON (contacts.contactable_id = dealers.id)
dealers.established #{direction}”} }

The problem is that PostgreSql requires the SELECT DISTINCT clause to be
followed by the ORDER BY criteria, so it needs to be inserted before the
joins take action. I don’t know how to do this, if even possible.
Currently using Rails -v 2.3.8 and postgres (0.7.9.2008.01.28) gem.

Suggestions or ideas anyone?

I think you’re reading the error message wrong…

You’re query (trimmed down to relevant bits) is:

SELECT DISTINCT dealers.* FROM “dealers”…ORDER BY LOWER(dealers.name)
asc

PostgreSQL is complaining because you are ordering by a column that is
NOT in your SELECT… so… change it to:

SELECT DISTINCT(dealers.*, LOWER(dealers.name)) FROM “dealers”…ORDER
BY LOWER(dealers.name) asc

Or…

SELECT DISTINCT dealers.* FROM “dealers”…ORDER BY dealers.name asc

Also… with all those LOWER() queries going to lose a lot of
performance unless you’ve got functional indexes setup. And if you’re
going to do that (ie. get postgresql specific), why not replace “LIKE”
with “ILIKE” and drop all the LOWER() bits entirely? Just something to
consider…

-philip

3

Thanks Philip,

That’s exactly what I did (before checking the forum). Actually There
were 2 problems:
1- ordering by name failed because I didn’t have the LOWER(dealers.name)
field setup
2- ordering by launch date failed because the LOWER keyword doesn’t
apply to dates (timestamps) but to strings only

So, to solve this issue, I’ve changed the :keyword named scope

before —> :select => “DISTINCT dealers."
after ----> :select => "DISTINCT LOWER(dealers.name), dealers.”

and added 2 named scopes for ordering, depending on which attribute has
been previously selected

  named_scope :order_by_name, lambda {|direction| {:order =>

“LOWER(dealers.name) #{direction}, dealers.status_id #{direction},
dealers.established #{direction}”} }

  named_scope :order_by_date, lambda {|direction| {:order =>

“dealers.launched_at #{direction}, dealers.status_id #{direction},
dealers.established #{direction}”} }

Now the query knows what to select and put it in order (lowercase dealer
names) as well as to avoid type collision (string vs datetime).

I’m currently looking into functional indexes, thanks for the tip!

Gabriella