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