Rails 5 solves ambiguous column issue

Abhishek Jain

By Abhishek Jain

on July 21, 2016

This blog is part of our  Rails 5 series.

1users(:id, :name)
2posts(:id, :title, :user_id)
3comments(:id, :description, :user_id, :post_id)
4
5>> Post.joins(:comments).group(:user_id).count
6Mysql2::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.

1users(:id, :name)
2posts(:id, :title, :user_id)
3comments(:id, :description, :user_id, :post_id)
4
5>> Post.joins(:comments).group(:user_id).count
6SELECT 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"
7
8=> { 1 => 1 }

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

Stay up to date with our blogs. Sign up for our newsletter.

We write about Ruby on Rails, ReactJS, React Native, remote work,open source, engineering & design.