rails multiple outer joins syntax

Question

I have the following models

user

has_many :leave_balances

leave_balance

belongs_to :user
belongs_to :leave_type

leave_type

has_many :leave_balances

I want to output a table format showing user names and their balance by leave type. Not every user can have every balance i.e. outer joins required.

I'd like to see something like this:

Employee   Annual Leave   Sick Leave
Bob        10 
Fred                      9
Sara       12             15

I am unsure how to get this out as a single statement?

I am thinking something like User.joins(:leave_balances).joins(:leave_type)

http://stackoverflow.com/questions/3245201/left-outer-joins-in-rails-3 — BvuRVKyUVlViVIc7, Oct 29 '12 at 12:59

score 1 · Answer 1 · answered Oct 29 '12 at 13:08

I recently faced this exact same thing. I switched to Squeel and I am much happier now. Specifically, the syntax for specifying an outer join is very clean:

result = User.joins { leave_balances.outer.leave_type.outer }.where { some_constraints }

https://github.com/ernie/squeel

score 0 · Accepted Answer · answered Oct 29 '12 at 10:58

You can pass a hash to joins method for nested joins

so you'll do

User.joins(leave_balances: :leave_type)

But that will perform an INNER JOIN, and I think that what you need is an LEFT OUTER JOIN

so you might have to manually write the join as described here

rails multiple outer joins syntax

2 Answers2