MS SQL query strangeness for ActiveRecord in Rails

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.

Look like MS SQL 2000 doesn’t support OFFSET like MySQL and PostgreSQL
does. This is really a bummer… Anyone experience the same problem and
figure out a workaround?

Sorry, typo voice…

While this is perfectly ok with MySQL, MS SQL choked on this. And the
sqlserver_adapter seems to generic a rather strange looking query which

generic => generate

Hmm… I think the problem lies with the sqlserver driver which uses top
twice (each with different sorting order) to emulate the offset feature.
In the process of this “emulation”, it uses generic table name tmp1, and
tmp2. by the order by is still assuming what I have given in the
:order_by which also includes the original table name. And hence MS SQL
of course won’t like it because the “AS tmp1” and “AS tmp2” already
rename table. So the solution is use unique name for columns or at least
use alias during the SQL to ensure unique column name…

Chester C. wrote:

Hmm… I think the problem lies with the sqlserver driver which uses top
twice (each with different sorting order) to emulate the offset feature.
In the process of this “emulation”, it uses generic table name tmp1, and
tmp2. by the order by is still assuming what I have given in the
:order_by which also includes the original table name. And hence MS SQL
of course won’t like it because the “AS tmp1” and “AS tmp2” already
rename table. So the solution is use unique name for columns or at least
use alias during the SQL to ensure unique column name…

OK. I have a fixed for sqlserver_adapter.rb to address this issue. Can
someone please tell me what do I need to do to have the fix in the rails
distribution? TIA.

Chester C. wrote:

Hmm… I think the problem lies with the sqlserver driver which uses top
twice (each with different sorting order) to emulate the offset feature.
In the process of this “emulation”, it uses generic table name tmp1, and
tmp2. by the order by is still assuming what I have given in the
:order_by which also includes the original table name. And hence MS SQL
of course won’t like it because the “AS tmp1” and “AS tmp2” already
rename table. So the solution is use unique name for columns or at least
use alias during the SQL to ensure unique column name…

Alternative way without touch any db driver is not use the :limit and
:offset.
Instead, use slice() on the return array.

Chester C. wrote:

Chester C. wrote:

Hmm… I think the problem lies with the sqlserver driver which uses top
twice (each with different sorting order) to emulate the offset feature.
In the process of this “emulation”, it uses generic table name tmp1, and
tmp2. by the order by is still assuming what I have given in the
:order_by which also includes the original table name. And hence MS SQL
of course won’t like it because the “AS tmp1” and “AS tmp2” already
rename table. So the solution is use unique name for columns or at least
use alias during the SQL to ensure unique column name…

OK. I have a fixed for sqlserver_adapter.rb to address this issue. Can
someone please tell me what do I need to do to have the fix in the rails
distribution? TIA.

nevermind… found it…