Support for left outer join in Rails 5

Ratnadeep Deshmane

By Ratnadeep Deshmane

on March 24, 2016

This blog is part of our  Rails 5 series.

Suppose in a blog application there are authors and posts. A post belongs to an author, while author has many posts.

The app needs to show a list of all the authors along with a number of posts that they have written.

For this, we need to join author and posts table with "left outer join". More about "left outer join" here, here and here .

In Rails 4.x, we need to write the SQL for left outer join manually as Active Record does not have support for outer joins.

1authors = Author.join('LEFT OUTER JOIN "posts" ON "posts"."author_id" = "authors"."id"')
2                .uniq
3                .select("authors.*, COUNT(posts.*) as posts_count")
4                .group("authors.id")

Rails 5 has added left_outer_joins method.

1authors = Author.left_outer_joins(:posts)
2                .uniq
3                .select("authors.*, COUNT(posts.*) as posts_count")
4                .group("authors.id")

It also allows to perform the left join on multiple tables at the same time.

1>> Author.left_joins :posts, :comments
2  Author Load (0.1ms)  SELECT "authors".* FROM "authors" LEFT OUTER JOIN "posts" ON "posts"."author_id" = "authors"."id" LEFT OUTER JOIN "comments" ON "comments"."author_id" = "authors"."id"

If you feel left_outer_joins is too long to type, then Rails 5 also has an alias method left_joins.

If you liked this blog, you might also like the other blogs we have written. Check out the full archive.

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

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