Hi

I am using: rails 3.012, tiny_tds 0.5.1 and activerecord-sqlserver-
adapter 3.0.19.

I want to be able to sort the address table using the following sql
query:

select * from addresses order by case when ended_on is null then 0
else 1 end, ended_on desc

This will give me a sorted list where the addresses that have ended_on
NULL comes first and the rest comes in descending order by ended_on.

However, when I try creating this scope

scope :my_order, :order => "case when ended_on is null then 0 else 1
end, ended_on desc"

and typing Address.limit(10).my_order into a console I get this error:

ActiveRecord::StatementInvalid: TinyTds::Error: Incorrect syntax near
the keyword 'ASC'.: SELECT TOP (10) [addresses].* FROM [addresses]
ORDER BY case ASC, ended_on DESC

I am guessing that the error might be in whatever code that takes
input from rails and makes that into sql. It seems to cut off most of
the first statement and inserting an ASC instead (btw there is no case
column in the addresses table). Does that occur in the sqlserver-
adapter or do I need to ask about this problem somewhere else?

- Niels

Have you searched on the Github issues for things like "order"? This ticket comes to the top and seems related.

https://github.com/rails-sqlserver/activerecord-sqlserver-adapter/pul...

It explains about how the adapter/visitor has to work with orders and the need for using an Arel sql literal. Because of that and the various tickets, we have an extensive order test too.

https://github.com/rails-sqlserver/activerecord-sqlserver-adapter/blo...

*duh* I should have searched github, not just google. However, I
wasn't sure what kind of problem I was having. Now at least, I know it
is in rails-sqlserver-adapter.

I see that is has been addressed, in 3.1.5. However, I am on 3.0.19
(since I am using rails 3.0, I am upgrading from 2.3 and didn't want
to upgrade directly to 3.2). Can the fix be applied to 3.0.19?

Also, if I define the order like this:

Arel::Nodes::Ordering.new(Arel.sql('CASE WHEN ended_on IS NULL THEN 1
ELSE 0 END'))

it works... however, this is also valid sql and doesn't work:

Arel::Nodes::Ordering.new(Arel.sql('CASE WHEN ended_on IS NULL THEN 1
ELSE 0 END, ended_on DESC'))

This last ordering spits out the following error:

SELECT [addresses].* FROM [addresses] ORDER BY CASE WHEN ended_on IS
NULL THEN 1 ELSE 0 END, ended_on DESC ASC

Seems that it throws and extra ASC in the for good measure, which
bungles the whole thing... how to I prevent it from doing that? I want
to do this:

has_many :addresses, :order =>
Arel::Nodes::Ordering.new(Arel.sql('CASE WHEN ended_on IS NULL THEN 1
ELSE 0 END, ended_on DESC'))

Is there a workaround?

I appreciate your help!

- Niels

On Mar 7, 4:19 pm, Ken Collins <k...@metaskills.net> wrote:

Review the ticket and the commits. You would have to monkey patch the Arel visitor as I did in the fix commits and then use an Arel sql literal wrapper as the comment/tests show.

 - Ken

On Mar 8, 2012, at 6:50 AM, Niels Jansen wrote: