Rails Fail - left outer joins and subqueries
This is an example of how Ruby on Rails' ActiveRecord fails to prevent the infamous N+1 query problem sometimes, even in very simple schemas that arose naturally from the requirements.
Let's say you have a discussion board. There's a Topics table and an Users table. (There's also a Comments table, naturally, but that's ignorable for now).
Let's say you want to let Users mark Topics as "favorite". So you make a Favorites table with two foreign keys: topic_id and users_id.
Now you want to display the last 10 Topics along with a flag telling whether the User's marked it "Favorite" or not. Preferably you'd use Rails' find, and find would only execute one query.
Simple, right? I can think of a few ways to do this in raw SQL in one query. With an outer join clause:
SELECT
isnull(f.user_id, 0) as favorited,
t.body_text,
u.name as topic_posters_name
FROM topic t
LEFT OUTER JOIN favorites f on f.topic_id = t.id AND f.user_id = ##USER'S ID##
LEFT OUTER JOIN users u ON t.user_id = u.id
ORDER BY t.id DESC
LIMIT 10
But - Rails' find can't do that, because Rails doesn't support dynamic conditions inside left outer joins on a has_one/belongs_to relationship.
Another way to do this in one query is with a select clause:
SELECT
( SELECT isnull(f.user_id, 0) FROM favorited f WHERE f.topic_id = t.id AND f.user_id = ##USER'S ID## ) as favorited,
t.body_text,
u.name as topic_posters_name
FROM topic t
LEFT OUTER JOIN users u ON t.user_id = u.id
ORDER BY t.id DESC
LIMIT 10
But this doesn't work in Rails, either, because Rails doesn't support the :select option when you use :include - even if the :select clause is something like "'example', *".
So in this case, as far as I'm aware, Rails is forcing you to do 11 queries, 1 for the topics list and 10 for either the Users or the Favorites. Please let me know if I'm not aware of something I should be.
EDIT: Turns out I was angry for no real reason. Rails' polymophic relationship do roughly what I want. See here.
Let's say you have a discussion board. There's a Topics table and an Users table. (There's also a Comments table, naturally, but that's ignorable for now).
Let's say you want to let Users mark Topics as "favorite". So you make a Favorites table with two foreign keys: topic_id and users_id.
Now you want to display the last 10 Topics along with a flag telling whether the User's marked it "Favorite" or not. Preferably you'd use Rails' find, and find would only execute one query.
Simple, right? I can think of a few ways to do this in raw SQL in one query. With an outer join clause:
SELECT
isnull(f.user_id, 0) as favorited,
t.body_text,
u.name as topic_posters_name
FROM topic t
LEFT OUTER JOIN favorites f on f.topic_id = t.id AND f.user_id = ##USER'S ID##
LEFT OUTER JOIN users u ON t.user_id = u.id
ORDER BY t.id DESC
LIMIT 10
But - Rails' find can't do that, because Rails doesn't support dynamic conditions inside left outer joins on a has_one/belongs_to relationship.
Another way to do this in one query is with a select clause:
SELECT
( SELECT isnull(f.user_id, 0) FROM favorited f WHERE f.topic_id = t.id AND f.user_id = ##USER'S ID## ) as favorited,
t.body_text,
u.name as topic_posters_name
FROM topic t
LEFT OUTER JOIN users u ON t.user_id = u.id
ORDER BY t.id DESC
LIMIT 10
But this doesn't work in Rails, either, because Rails doesn't support the :select option when you use :include - even if the :select clause is something like "'example', *".
So in this case, as far as I'm aware, Rails is forcing you to do 11 queries, 1 for the topics list and 10 for either the Users or the Favorites. Please let me know if I'm not aware of something I should be.
EDIT: Turns out I was angry for no real reason. Rails' polymophic relationship do roughly what I want. See here.