Forum: Ruby on Rails MS SQL query strangeness for ActiveRecord in Rails

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Chester C. (Guest)
on 2006-03-30 19:26
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:

<pre>
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)
    )
</pre>

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:-

<pre>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</pre>

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.
Chester C. (Guest)
on 2006-03-30 19:28
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
Chester C. (Guest)
on 2006-03-30 23:18
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?
Chester C. (Guest)
on 2006-03-31 04:00
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. (Guest)
on 2006-03-31 23:41
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. (Guest)
on 2006-03-31 23:55
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...
Chester C. (Guest)
on 2006-04-01 01:43
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.
This topic is locked and can not be replied to.