PostgreSql SELECT DISTINCT and ORDER BY returns an error

rubyonrails-talk
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

2

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