Rails Ruby on Rails

The SQL in the Crazy Features > Complex Joins section is invalid. This is an interesting problem, because the invalid SQL builds towards an incorrect conclusion that a subselect is needed in the case described. Seems like more than a simple doc-patch.

First the SQL to do photo counts by user has a misplaced limit 3 so it doesn't run at all.

select users.*, count(photos.id) from users left outer join photos on users.id = photos.user_id limit 3 group by user_id;

should be

mysql> select users.*, count(photos.id) from users left outer join photos on users.id = photos.user_id group by user_id;

+----+------+------------------+
| id | name | count(photos.id) | 
+----+------+------------------+ 
| 2  | bai  | 0                |
| 1  | hai  | 3                | 
+----+------+------------------+
 2 rows in set (0.00 sec)

So that supports the example, but the group by can be better specified as group by users.id instead of user_id, which produces the correct results:

mysql> select users.*, count(photos.id) from users left outer join photos on users.id = photos.user_id group by users.id;

+----+--------+------------------+
| id | name   | count(photos.id) |
+----+--------+------------------+
| 1  | hai    | 3                |
| 2  | bai    | 0                |
| 3  | dumpty | 0                | 
+----+--------+------------------+
 3 rows in set (0.00 sec)

Interestingly, the first form of the group by clause is rejected by Postgres, which appears to restrict joins to columns in the result set.

Edited by Rohit Arondekar for formating.