- 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
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...
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