CODEWITHSUNDEEP
ruby-on-railsactiverecordleft-join
Chanpory
Chanpory

Reputation: 3095

Rails 3: Inner join with associations

I'm a bit stuck on an Active Record query problem. I'd like to do a left join instead of an inner join on a query, but the results of the joins method are returning only an inner join. 

Entry.
  joins(:measurements => :measurement_type).
  sum(:value, :conditions => ['name = ?','Calories'])

The tricky part is that :measurements has a belongs_to relationship to :measurement_type which is where the name column resides.

Right now this is returning sums only for users who have food_posts. The goal is to return sums for all users. Users who don't have a food post would have a sum of zero.

I've tried Googling and searching other posts on here, but haven't found an answer. Any suggestions?

Also I'm using SQLite in my development environment and Postgresql for production.

Upvotes: 1

Views: 3393

Answers (2)

DGM
DGM

Reputation: 26979

MetaWhere gem has support for outer joins, among much other goodness:

https://github.com/ernie/meta_where

Upvotes: 1

diedthreetimes
diedthreetimes

Reputation: 4115

If you want to do a left join instead of an inner join you need to use custom 'sql fragments'

In your example,

relation.joins("left join measurements on measurements.id = measurement_type.measurements_id")

Or something along those lines dependent on your setup.

This tutorial sums it up pretty clearly.

Upvotes: 4

Related Questions