Eager Loading + Confusion

Rails
1

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

2

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

3

On 31 Mar 2008, at 21:44, Brian B. wrote:

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

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

4

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.

On Mar 31, 3:57 pm, Frederick C. [email protected]

5

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

On Apr 1, 2:44 am, Frederick C. [email protected]

6

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

7

On Mar 31, 10:55 pm, Brian B. [email protected] wrote:

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