Eager Loading + Confusion

So I'm trying to do a find with an order by specific column values thing, but some of the columns are attributes through associations. So I did some eager loading with the :include option to try and access the attributes of the object I'm doing the find on. Stuff has been blowing up on me so far.

Here's the code in the controller right now:

sort_by = params[:sort_by] || 'id' direction = params[:direction] || 'desc'

@state_agency_grants = Grant.paginate(:all, :include => [:funding_entity, :creator_entity, applying_entity], :order=> "#{sort_by} #{direction}" ,:page => params[:page], :per_page => 50)

So yea, passing what to sort by as a parameter, etc etc. :funding_entity is of type FundingEntity while :creator_entity and :appying_entity are of AccountEntity.

Some of the params[:sort_by] I've passed are:

funding_entity.name => The multi-part identifier "funding_entity.name" could not be bound.

funding_entity => Invalid column name 'funding_entity'.

Okay, its checking the database and not the model.

Well, funding_entities is a table... maybe that will work?

funding_entities.name => Incorrect syntax near '\.'.: SELECT * FROM (SELECT TOP 5 * FROM (SELECT TOP 5 [grants].[id] AS t0_r0, [grants]. [created_by] AS t0_r1, ... (40 more of these) ... ORDER BY funding_entities.name asc) AS tmp1 ORDER BY funding_entities\.\[name\] DESC) AS tmp2 ORDER BY funding_entities\.\[name\] asc

Not sure why this happens. Its trying to do funding_entities.[name] or something with escape characters?

I could sort by funding_entity_id, but that's not very useful to the end user. I feel like I'm doing it as described here:

Any help would be great.

Thanks, Brian Butz

Sorry for bumping this, but I still haven't been able to figure it out.

The weird bit is here: \.\[name\] it's like it's been double escaped. What database are you using? passing something like funding_entities.name should work.

Fred

Using MSSQL Server. Its weird because it doesn't try to escape the brackets or . (should that even be there?) anywhere else in the statement. I'll inspect the statement again (so long...) and see if there's any reason it does that at the end.

Using MSSQL Server. Its weird because it doesn't try to escape the brackets or . (should that even be there?) anywhere else in the statement. I'll inspect the statement again (so long...) and see if there's any reason it does that at the end.

Well I'd guess this funnyness is happening inside the add_limit_offset! function in the connection adapter. Unfortunately I don't know anything about MSSQL so i've no idea if what it's actually trying to accomplish.

Fred

Thanks for the good direction. It is indeed in add_limit_offset!, I've done a little bit of modifying but am a bit worried about breaking the rest of the application.

There's something else I don't completely get either. The model also has two associations that use the same table (the Account that created it, and the Account the object is for), so the table is account_entities, but the names of the associations are creator_entity and applying_entity. Right now I'm doing include => [..., :creator_entity, :applying_entity]. Do I want to order by account_entities.name, and if so how do I know it's applying it to the correct column? Is this just the wrong kind of join?

Thanks in advance, Brian Butz

MS Sql 2000 does not have limit and offset command

it only appears in MS Sql 2005, but the adapter does not use them

so they need to have a work around

to have the records from 80 to 100 for example

it first does a select limit 100 then a select in reverserve order limit 80 in the result and finally a third select on the result to put in back in initial order

this creates some complication with the rest of rails that assumes here and there that the code can use limit and offset

i'm not sure about the code but if something is not working as expected you have to think of that