Pagination using first_column and not ActiveRecord primary_key

[celsodantas]

Making public a discussion I had with Metaskill

I'm using a legacy database which doesn't use Rails naming conventions, and if I try to use will_paginate gem i'm getting this:

SELECT TOP (30) [__rnt].* FROM ( SELECT ROW_NUMBER() OVER (ORDER BY [D04].[USR_NOTE] ASC) AS [__rn], [D04].* FROM [D04] ) AS [__rnt] WHERE [__rnt].[__rn] > (270)

...OVER (ORDER BY [D04].[USR_NOTE] ASC) AS...
Note that it's using USR_NOTE as a order by and not ID.
As USR_NOTE is not a varchar or number I get an error: The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

Is there anyway I can change the default ORDER BY column?
A workaround I found was to always use a ".order" method, like this:
Component.page(10).order("UKEY") #UKEY is the table ID

Obs. I've created an "alias_attribute :id, :UKEY", and would be cool to see the adapter use that.

MetaSkills response

I remember this issue when I started working with WillPaginate years ago. Unlike other databases, SQL Server does not allow you to have ambitious results. IE, you have to give it an order. I think that is even the case with tables with an [id] field. Note, you may not see this because our Arel visitor takes some liberties to find that primary key if you do not give it an order.

https://github.com/rails-sqlserver/activerecord-sqlserver-adapter/blob/master/lib/arel/visitors/sqlserver.rb#L349

My response

And is it possible for the adapter/visitor check if an "alias_attribute :id" exists and use it instead of using the first_column in the table? I know it's a silly thing, but would be cool to see that working.

MetaSkills response

Why alias_attribute? ActiveRecord has the self.primary_key= or set_primary_key class methods for doing that. It makes it so you can use legacy natural keys and/or legacy column names for the #id and it works. Google around and read the docs, you should like it.

My response

Or that! hehe
I'm using "set_primary_key :UKEY" in my model and in fact it makes more
sense to use it instead of alias_attribute.

So I update my comment saying: "would be great to see the adapter using the
table primary key (:id or set by the set_primary_key method) as the
default order column" =]

MetaSkills response

Cool! Re: your idea, I think it should not be the concern of the adapter to step too far into doing too much. In this case, it could be considered an ActiveRecord/Arel bug that set_primary_key on a table does not create the necessary reflection and or table object at the Arel level to follow that higher up setter and just ask that or pick the first attribute like I did here.

t = table_from_select_statement(o)
c = t.primary_key || t.columns.first

It could be a bug at our schema reflection level and when we propagate the column objects too. So in short, the fix could likely be elsewhere and an external hack in the visitor is not likely the right place for it. Something this small, I will have to wait till someone opens up an issue, does the legwork and research and/or suggests a patch with a test. BTW, this is why I dislike direct communications to the author. This whole thread is not in the open where others can learn from it :(

[metaskills]

TL:DR - We should test that the Arel visitor can pick up on AR's set_primary_key.

[metaskills]

Hmm, I was going to pick this up today but noticed this in the docs for ARel.

Arel::Table#primary_key is deprecated and will be removed in 3.0.0 with no replacement.

This does not make sense to me at all and I don't understand what the replacement would be. I think the could want you to reflect on the engines columns. Not sure. Either way, once we come up with a plan, we can use the SqlServerNaturalPkData class in our test suite to write a test.

[celsodantas]

kinda wierd. You think reflecting on the engines columns is a good way to go?

i'll try to find a new way to deal with that.

[metaskills]

FYI, some recent work went into the adapter the other day via issue #139. This added better column detection on identity or primary. So when we have the solution, we can test it falls back to identity, then first primary, then first.