Ruby on Rails is missing a find clause for its left outer joins

[usernameguy]

Let's say you have a simple discussion board.  There's a posts table (simplified):
create table posts ( id int )
id
1
2
3
4

And a table where users can mark their favorite posts:
create table favorites ( post_id int, user_id int )
post_id, user_id
1 1
2 2
3 1
3 2

Problem: Now let's say we're trying to write a Ruby on Rails ActiveRecord find that gets all posts,

Comments

[steeltoe]

In solution 2, where is the case that the user_id would be null? All favorites were tagged by someone. Isn't user_id always there?

[usernameguy]

What happens with joins is, you compute all the joins, first, to compute the multiplied set. Then you apply the wheres second.

In fact, abstractly, what you're doing is doing the HUGE multiplied set of the join, without your join clause, first (you can see this huge result with a "SELECT id,user_id FROM posts LEFT OUTER JOIN favorites on 1=1" - it's a 2^N-sized set). This result is roughly the natural join. Then you apply your join clauses. Then you apply your where clauses. The database doesn't actually do that because it can take short cuts to avoid having the whole intermediate data set in memory all at once.

Left outer joins are different from normal joins in the way they apply the join clause. Basically what they do is, apply the join clause normally to whittle down the massive result, then sweep through to make sure that every row from the left table has a row in the result by filling in (A, null) rows.

BTW I messed up the select list on the Problem statement; should be id, user_id - not post_id,user_id. Corrected that.