Left outer join using ruby on rails (>=6) ORM?

Question

Is there a way to simply do a left outer join using rails ActiveRecord?

I see here there are some ways using raw SQL, but I'd like to use the 'rails way' (presuming there is a rails way)

i.e. a query to retrieve the records represented by the maroon area:

Why not use raw SQL (like here) - that's a fair question. My app has no raw sql, only syntax of the form customer.purchases so I'd like to keep the code consistence across the app.

Answer 1

Rails >= 5 appears to have this capability built in.

Here's a simple example:

User.left_outer_joins(:posts)
=> SELECT "users".* FROM "users" LEFT OUTER JOIN "posts" ON "posts"."user_id" = "users"."id"

Here's another example:

Author.left_joins :posts, :comments

# Produces:
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"

This example from RailsGuides is also handy to know (although it contains some raw SQL):

Customer.left_outer_joins(:reviews).distinct.select('customers.*, COUNT(reviews.*) AS reviews_count').group('customers.id')

#Produces:
SELECT DISTINCT customers.*, COUNT(reviews.*) AS reviews_count FROM customers
LEFT OUTER JOIN reviews ON reviews.customer_id = customers.id GROUP BY customers.id