This project is archived and is in readonly mode.
Invalid SQL in AREL readme
Reported by James Kebinger | August 26th, 2010 @ 04:59 PM
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.
Comments and changes to this ticket
-
Rohit Arondekar August 27th, 2010 @ 02:26 PM
- Importance changed from to Low
James, you can edit the docs if you wish to. You'll need to ask lifo (on Github) write permission to http://github.com/lifo/docrails and then edit away. The changes will be merged with the main Rails repo later.
Do report back once you've made the changes so that I can close this ticket. :)
-
Rohit Arondekar September 14th, 2010 @ 05:12 AM
- State changed from new to invalid
You actually meant the AREL readme! It's here http://github.com/nkallen/arel and not part of Rails. You'll need to fork-edit that repo or make an issue there.
Create your profile
Help contribute to this project by taking a few moments to create your personal profile. Create your profile »
<h2 style="font-size: 14px">Tickets have moved to Github</h2>
The new ticket tracker is available at <a href="https://github.com/rails/rails/issues">https://github.com/rails/rails/issues</a>