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