Artifact
2008-Sep-04 03:55 UTC
Find with includes, conditions on included tables, and limit
I have my main Night model, which has and belongs to many Users and Genres. I''m trying to put a limit on my find statement, but it causes SQL problems. Here''s what I''m trying to do: conditions = ["genres_nights.genre_id IN (:genres) AND venue_id IN (:venues) AND nights_users.user_id IN (:users) AND date >= :date", {:genres => genres, :venues => venues, :users => users, :date => date}] find(:all, :include => [:venue, :genres, :users, {:flyer => :thumbnails}], :conditions => conditions, :limit => 16) The problem is that the limit option is forcing rails to split the query into two, first without the joins, where it finds the IDs of the records. It should then do another query to retireve the data on the records with those IDs. This is because using limit on a query with joins doesn''t work due to the extra rows from the joins. Anyway, the long and short of it is that the limit means that the joins are not included in the initial "Load IDs for Limited Eager Loading" query, but that query still references the now non-joined tables in its conditions, producing the following error: Night Load IDs For Limited Eager Loading (0.000000) SQLite3::SQLException: no such column: nights_users.user_id: SELECT id FROM "nights" WHERE (genres_nights.genre_id IN (NULL) AND venue_id IN (NULL) AND nights_users.user_id IN (NULL) AND date >= ''2008-09-04'') LIMIT 16 So, what am I to do? Maybe I should use :join to manually specifiy the joins instead of using :include, but won''t I lose out on some rails magic that way? If that is a good option, any hints on how to do it? Thanks in advance. --~--~---------~--~----~------------~-------~--~----~ 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-Sep-04 07:31 UTC
Re: Find with includes, conditions on included tables, and limit
On 4 Sep 2008, at 05:55, Artifact wrote:> > I have my main Night model, which has and belongs to many Users and > Genres. I''m trying to put a limit on my find statement, but it causes > SQL problems. Here''s what I''m trying to do: > > conditions = ["genres_nights.genre_id IN (:genres) AND venue_id IN > (:venues) AND nights_users.user_id IN (:users) AND date >= :date", > {:genres => genres, :venues => venues, :users => users, :date => > date}] > find(:all, :include => [:venue, :genres, :users, {:flyer > => :thumbnails}], :conditions => conditions, :limit => 16) > > The problem is that the limit option is forcing rails to split the > query into two, first without the joins, where it finds the IDs of the > records. It should then do another query to retireve the data on the > records with those IDs. This is because using limit on a query with > joins doesn''t work due to the extra rows from the joins. > > Anyway, the long and short of it is that the limit means that the > joins are not included in the initial "Load IDs for Limited Eager > Loading" query, but that query still references the now non-joined > tables in its conditions, producing the following error: > > Night Load IDs For Limited Eager Loading (0.000000) > SQLite3::SQLException: no such column: nights_users.user_id: SELECT id > FROM "nights" WHERE (genres_nights.genre_id IN (NULL) AND venue_id IN > (NULL) AND nights_users.user_id IN (NULL) AND date >= ''2008-09-04'') > LIMIT 16That''s a bug in rails - it hasn''t worked out that you were referencing the join tables. Do you actually need :include? You can use the same syntax with :joins, it just won''t try and be clever and uess which tables it needs to join. Fred> > > So, what am I to do? Maybe I should use :join to manually specifiy the > joins instead of using :include, but won''t I lose out on some rails > magic that way? If that is a good option, any hints on how to do it? > > Thanks in advance. > > >--~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Artifact
2008-Sep-10 08:11 UTC
Re: Find with includes, conditions on included tables, and limit
Aha, thanks for that, changing :include to :join works fine. I didn''t see the line in the docs that tells me i can do that. That''ll teach me to read more carefully. If this is a bug, is there a ticket somewhere for it i could keep an eye on? A On Sep 4, 8:31 am, Frederick Cheung <frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> On 4 Sep 2008, at 05:55, Artifact wrote: > > > > > > > I have my main Night model, which has and belongs to many Users and > > Genres. I''m trying to put a limit on my find statement, but it causes > > SQL problems. Here''s what I''m trying to do: > > > conditions = ["genres_nights.genre_id IN (:genres) AND venue_id IN > > (:venues) AND nights_users.user_id IN (:users) AND date >= :date", > > {:genres => genres, :venues => venues, :users => users, :date => > > date}] > > find(:all, :include => [:venue, :genres, :users, {:flyer > > => :thumbnails}], :conditions => conditions, :limit => 16) > > > The problem is that the limit option is forcing rails to split the > > query into two, first without the joins, where it finds the IDs of the > > records. It should then do another query to retireve the data on the > > records with those IDs. This is because using limit on a query with > > joins doesn''t work due to the extra rows from the joins. > > > Anyway, the long and short of it is that the limit means that the > > joins are not included in the initial "Load IDs for Limited Eager > > Loading" query, but that query still references the now non-joined > > tables in its conditions, producing the following error: > > > Night Load IDs For Limited Eager Loading (0.000000) > > SQLite3::SQLException: no such column: nights_users.user_id: SELECT id > > FROM "nights" WHERE (genres_nights.genre_id IN (NULL) AND venue_id IN > > (NULL) AND nights_users.user_id IN (NULL) AND date >= ''2008-09-04'') > > LIMIT 16 > > That''s a bug in rails - it hasn''t worked out that you were referencing > the join tables. > Do you actually need :include? You can use the same syntax > with :joins, it just won''t try and be clever and uess which tables it > needs to join. > > Fred > > > > > So, what am I to do? Maybe I should use :join to manually specifiy the > > joins instead of using :include, but won''t I lose out on some rails > > magic that way? If that is a good option, any hints on how to do it? > > > Thanks in advance.--~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Frederick Cheung
2008-Sep-10 08:35 UTC
Re: Find with includes, conditions on included tables, and limit
On 10 Sep 2008, at 09:11, Artifact wrote:> > Aha, thanks for that, changing :include to :join works fine. I didn''t > see the line in the docs that tells me i can do that. That''ll teach me > to read more carefully. > > If this is a bug, is there a ticket somewhere for it i could keep an > eye on? >Don''t know, then again I didn''t look :-) Fred> A > > On Sep 4, 8:31 am, Frederick Cheung <frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> > wrote: >> On 4 Sep 2008, at 05:55, Artifact wrote: >> >> >> >> >> >>> I have my main Night model, which has and belongs to many Users and >>> Genres. I''m trying to put a limit on my find statement, but it >>> causes >>> SQL problems. Here''s what I''m trying to do: >> >>> conditions = ["genres_nights.genre_id IN (:genres) AND venue_id IN >>> (:venues) AND nights_users.user_id IN (:users) AND date >= :date", >>> {:genres => genres, :venues => venues, :users => users, :date => >>> date}] >>> find(:all, :include => [:venue, :genres, :users, {:flyer >>> => :thumbnails}], :conditions => conditions, :limit => 16) >> >>> The problem is that the limit option is forcing rails to split the >>> query into two, first without the joins, where it finds the IDs of >>> the >>> records. It should then do another query to retireve the data on the >>> records with those IDs. This is because using limit on a query with >>> joins doesn''t work due to the extra rows from the joins. >> >>> Anyway, the long and short of it is that the limit means that the >>> joins are not included in the initial "Load IDs for Limited Eager >>> Loading" query, but that query still references the now non-joined >>> tables in its conditions, producing the following error: >> >>> Night Load IDs For Limited Eager Loading (0.000000) >>> SQLite3::SQLException: no such column: nights_users.user_id: >>> SELECT id >>> FROM "nights" WHERE (genres_nights.genre_id IN (NULL) AND venue_id >>> IN >>> (NULL) AND nights_users.user_id IN (NULL) AND date >= ''2008-09-04'') >>> LIMIT 16 >> >> That''s a bug in rails - it hasn''t worked out that you were >> referencing >> the join tables. >> Do you actually need :include? You can use the same syntax >> with :joins, it just won''t try and be clever and uess which tables it >> needs to join. >> >> Fred >> >> >> >>> So, what am I to do? Maybe I should use :join to manually specifiy >>> the >>> joins instead of using :include, but won''t I lose out on some rails >>> magic that way? If that is a good option, any hints on how to do it? >> >>> Thanks in advance. > >--~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Artifact
2008-Sep-10 10:00 UTC
Re: Find with includes, conditions on included tables, and limit
I should metion this for the benefit of anyone else with the same problem: The problem disappears, ie. :include works, when I use mysql rather than sqlite3. On Sep 10, 9:35 am, Frederick Cheung <frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> On 10 Sep 2008, at 09:11, Artifact wrote: > > > > > Aha, thanks for that, changing :include to :join works fine. I didn''t > > see the line in the docs that tells me i can do that. That''ll teach me > > to read more carefully. > > > If this is a bug, is there a ticket somewhere for it i could keep an > > eye on? > > Don''t know, then again I didn''t look :-) > > Fred > > > A > > > On Sep 4, 8:31 am, Frederick Cheung <frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> > > wrote: > >> On 4 Sep 2008, at 05:55, Artifact wrote: > > >>> I have my main Night model, which has and belongs to many Users and > >>> Genres. I''m trying to put a limit on my find statement, but it > >>> causes > >>> SQL problems. Here''s what I''m trying to do: > > >>> conditions = ["genres_nights.genre_id IN (:genres) AND venue_id IN > >>> (:venues) AND nights_users.user_id IN (:users) AND date >= :date", > >>> {:genres => genres, :venues => venues, :users => users, :date => > >>> date}] > >>> find(:all, :include => [:venue, :genres, :users, {:flyer > >>> => :thumbnails}], :conditions => conditions, :limit => 16) > > >>> The problem is that the limit option is forcing rails to split the > >>> query into two, first without the joins, where it finds the IDs of > >>> the > >>> records. It should then do another query to retireve the data on the > >>> records with those IDs. This is because using limit on a query with > >>> joins doesn''t work due to the extra rows from the joins. > > >>> Anyway, the long and short of it is that the limit means that the > >>> joins are not included in the initial "Load IDs for Limited Eager > >>> Loading" query, but that query still references the now non-joined > >>> tables in its conditions, producing the following error: > > >>> Night Load IDs For Limited Eager Loading (0.000000) > >>> SQLite3::SQLException: no such column: nights_users.user_id: > >>> SELECT id > >>> FROM "nights" WHERE (genres_nights.genre_id IN (NULL) AND venue_id > >>> IN > >>> (NULL) AND nights_users.user_id IN (NULL) AND date >= ''2008-09-04'') > >>> LIMIT 16 > > >> That''s a bug in rails - it hasn''t worked out that you were > >> referencing > >> the join tables. > >> Do you actually need :include? You can use the same syntax > >> with :joins, it just won''t try and be clever and uess which tables it > >> needs to join. > > >> Fred > > >>> So, what am I to do? Maybe I should use :join to manually specifiy > >>> the > >>> joins instead of using :include, but won''t I lose out on some rails > >>> magic that way? If that is a good option, any hints on how to do it? > > >>> Thanks in advance.--~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---