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 [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.
