Hi,
I am currently trying to move my rubyonrails app from Linux to Windows
server utilizing MS SQL instead of MySQL due to “business” reason. All
my listing screen utilize a drop down list in each column to allow user
to “filter” the listing based upon the value chosen from the drop down
list. In the controller, I used find() method but with customized
parameters such as the following:
Member.find( :all, :select => "members.id as id, members.last_name, members.first_name, members.phone, members.company_id, companies.name", :order => @sort, :joins => ", companies, engagements_members, engagements", :conditions => [@cond_str] + @cond_params, :limit => @user.rows_per_page, :group => 'members.id, members.last_name, members.first_name, members.phone, members.company_id, companies.name', :offset => @page * Integer(@user.rows_per_page) )
While this perfectly ok with MySQL, MS SQL choked on this. And the
sqlserver_adapter seems to generic a rather strange looking query which
causes the error as the following:-
Exception occurred.: SELECT * FROM (SELECT TOP 7 * FROM (SELECT TOP 7 members.id, members.last_name, members.first_name, members.phone, members.company_id, companies.name FROM members , companies, engagements_members, engagements WHERE (members.company_id = companies.id AND engagements_members.member_id = members.id AND engagements_members.engagement_id = engagements.id) GROUP BY members.id, members.last_name, members.first_name, members.phone, members.company_id, companies.name ORDER BY members.last_name, members.first_name, members.phone, companies.name ) AS tmp1 ORDER BY members.last_name DESC, members.first_name DESC, members.phone DESC, companies.name DESC) AS tmp2 ORDER BY members.last_name, members.first_name, members.phone, companies.name
Does anyone has any suggestion/pointer or other online resource that can
help me out here? I am trying to avoid direct SQL (so no find_by_sql())
hoping to keep the code portable for different database.
Thanks in advance.