This blog is part of our Rails 5 series.

users(:id, :name)
posts(:id, :title, :user_id)
comments(:id, :description, :user_id, :post_id)

>> Post.joins(:comments).group(:user_id).count
Mysql2::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

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.

users(:id, :name)
posts(:id, :title, :user_id)
comments(:id, :description, :user_id, :post_id)

>> Post.joins(:comments).group(:user_id).count
SELECT 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"

=> { 1 => 1 }

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