Back to Blog

Rails 5 solves ambiguous column issue

on July 21, 2016
This blog is part of our Rails 5 series.
1
2users(:id, :name)
3posts(:id, :title, :user_id)
4comments(:id, :description, :user_id, :post_id)
5
6>> Post.joins(:comments).group(:user_id).count
7Mysql2::Error: Column 'user_id' in field list is ambiguous: SELECT COUNT(*) AS count_all, user_id AS user_id FROM `posts` INNER JOIN `comments` ON `comments`.`post_id` = `posts`.`id` GROUP BY user_id
8

As we can see user_id has conflict in both projection and GROUP BY as they are not prepended with the table name posts in the generated SQL and thus, raising SQL error Column 'user_id' in field list is ambiguous.

Fix in Rails 5

This issue has been addressed in Rails 5 with this pull request.

With this fix, we can now group by columns having same name in both the tables.

1
2users(:id, :name)
3posts(:id, :title, :user_id)
4comments(:id, :description, :user_id, :post_id)
5
6>> Post.joins(:comments).group(:user_id).count
7SELECT COUNT(*) AS count_all, "posts"."user_id" AS posts_user_id FROM "posts" INNER JOIN "comments" ON "comments"."post_id" = "posts"."id" GROUP BY "posts"."user_id"
8
9=> { 1 => 1 }
10

This shows that now both projection and Group By are prepended with the posts table name and hence fixing the conflict.


You might also like

If you liked this blog post, check out similar ones from BigBinary