Rails - Mar 2006 - 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:

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


-- 
Posted via http://www.ruby-forum.com/.

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


-- 
Posted via http://www.ruby-forum.com/.

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?

-- 
Posted via http://www.ruby-forum.com/.

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


-- 
Posted via http://www.ruby-forum.com/.

Chester Chee 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.

-- 
Posted via http://www.ruby-forum.com/.

Chester Chee wrote:
> Chester Chee 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...

-- 
Posted via http://www.ruby-forum.com/.

Chester Chee 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.

-- 
Posted via http://www.ruby-forum.com/.