Rails 5 solves ambiguous column issue

Abhishek Jain

By Abhishek Jain

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.