thr3ads.net - Rails - PostgreSql SELECT DISTINCT and ORDER BY returns an error [Oct 2010]

If this information is useful, please help other people find it:
Share via:

Gabi Ge

2010-Oct-27 21:48 UTC

PostgreSql SELECT DISTINCT and ORDER BY returns an error

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?

-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

Philip Hallstrom

2010-Oct-27 23:04 UTC

head link

Re: PostgreSql SELECT DISTINCT and ORDER BY returns an error

On Oct 27, 2010, at 2:48 PM, Gabi Ge wrote:
> 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?
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


-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

Gabi Ge

2010-Oct-28 16:54 UTC

head link

Re: PostgreSql SELECT DISTINCT and ORDER BY returns an error

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

-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

Rails - Oct 2010 - PostgreSql SELECT DISTINCT and ORDER BY returns an error

PostgreSql SELECT DISTINCT and ORDER BY returns an error

Re: PostgreSql SELECT DISTINCT and ORDER BY returns an error

Re: PostgreSql SELECT DISTINCT and ORDER BY returns an error