thr3ads.net - Rails - Eager Loading + Confusion [Mar 2008]

If this information is useful, please help other people find it:
Share via:

Brian Butz

2008-Mar-28 21:51 UTC

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:
http://api.rubyonrails.org/classes/ActiveRecord/Associations/ClassMethods.html

Any help would be great.

Thanks,
Brian Butz
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Brian Butz

2008-Mar-31 20:44 UTC

head link

Re: Eager Loading + Confusion

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

On Mar 28, 4:51 pm, Brian Butz
<butzopo...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:> 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:http://api.rubyonrails.org/classes/ActiveRecord/Associations/ClassMet...
>
> Any help would be great.
>
> Thanks,
> Brian Butz--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Frederick Cheung

2008-Mar-31 20:57 UTC

head link

Re: Eager Loading + Confusion

On 31 Mar 2008, at 21:44, Brian Butz wrote:
>>
>> 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
>>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


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Brian Butz

2008-Mar-31 21:55 UTC

head link

Re: Eager Loading + Confusion

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 Cheung
<frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:> On 31 Mar 2008, at 21:44, Brian Butz wrote:
>
>
>
>
>
> >> 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
>
> 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--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Frederick Cheung

2008-Apr-01 07:44 UTC

head link

Re: Eager Loading + Confusion

On Mar 31, 10:55 pm, Brian Butz
<butzopo...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
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> On Mar 31, 3:57 pm, Frederick Cheung
<frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
> wrote:
>
> > On 31 Mar 2008, at 21:44, Brian Butz wrote:
>
> > >> 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
>
> > 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--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Brian Butz

2008-Apr-01 18:27 UTC

head link

Re: Eager Loading + Confusion

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

On Apr 1, 2:44 am, Frederick Cheung
<frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:> On Mar 31, 10:55 pm, Brian Butz
<butzopo...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> 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
>
> > On Mar 31, 3:57 pm, Frederick Cheung
<frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
> > wrote:
>
> > > On 31 Mar 2008, at 21:44, Brian Butz wrote:
>
> > > >> 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
>
> > > 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--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

StephanParis-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org

2008-Apr-01 22:42 UTC

head link

Re: Eager Loading + Confusion

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



On Apr 1, 8:27 pm, Brian Butz
<butzopo...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:> 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
>
> On Apr 1, 2:44 am, Frederick Cheung
<frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
> wrote:
>
>
>
> > On Mar 31, 10:55 pm, Brian Butz
<butzopo...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> 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
>
> > > On Mar 31, 3:57 pm, Frederick Cheung
<frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
> > > wrote:
>
> > > > On 31 Mar 2008, at 21:44, Brian Butz wrote:
>
> > > > >> 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
>
> > > > 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- Hide quoted text -
>
> - Show quoted text ---~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Possibly Parallel Threads

Search for more apparently analagous threads

Rails - Mar 2008 - Eager Loading + Confusion

Eager Loading + Confusion

Re: Eager Loading + Confusion

Re: Eager Loading + Confusion

Re: Eager Loading + Confusion

Re: Eager Loading + Confusion

Re: Eager Loading + Confusion

Re: Eager Loading + Confusion

Possibly Parallel Threads