ernie/squeel — Issue #108: Outer join with conditions
Author: bluewomble
Created: 2012-02-29 11:32:23+00:00
State: open

I'm having difficulty getting outer joins with extra conditions working in squeel, but I'm sure there must be a way to do it...

If you imagine something along the lines of:

```ruby
create_table :people do |t|
t.string :name
end
create_table :phone_number_types do |t|
t.string :code
end
create_table :phone_numbers do |t|
t.references :person
t.references :phone_number_type
t.string :number
end

class Person < ActiveRecord::Base
has_many :phone_numbers
end
class PhoneNumber < ActiveRecord::Base
belongs_to :person
belongs_to :phone_number_type
end
class PhoneNumberType < ActiveRecord::Base
has_many :phone_numbers
end

fred= Person.create! :name => "Fred"
bob = Person.create! :name => "Bob"
home = PhoneNumberType.create! :code => "Home"
work = PhoneNumberType.create! :code => "Work"
PhoneNumber.create! :person => fred, :phone_number_type => home, :number = > "020 1234 5678"
PhoneNumber.create! :person => bob, :phone_number_type => work, :number = > "020 8765 4321"
etc.
```

The query I want to write is: Show me all people who do not have a work phone number. The SQL would be something like this:

```sql
SELECT people.* FROM people
INNER JOIN phone_numbers ON people.id = phone_numbers.person_id
LEFT OUTER JOIN phone_number_types ON phone_number_types.id = phone_numbers.phone_number_type_id
AND phone_number_types.code = "Work"
WHERE phone_number_types.id IS NULL
```

How can I do this with squeel?

```ruby
People.joins{phone_numbers.phone_number_type.outer}
```
gives me the outer join, but I can't work out how to add the extra join condition... am I missing something obvious?

Thanks,
Ash.