ruby-on-railsruby-on-rails-3squeel

Squeel to execute outer join on Rails


I'm trying to find a way to create a simple outer join without too much hassle. I know I can do this manually by specifying an outer join, but I'm looking for a simple way.

Therefore, I was taking a look at Squeel, which seems to be the new alternative for Metawhere. It seems to be able to handle outer joins, but I can't get what I want.

In particular, I have three models :

City
Building
CityBuilding

I would simply like a list of all the buildings whether they exist in a city or not. CityBuilding is, of course, the model that connects a city to a building. I would like to get something like :

city 1{
  TownCenter => city_building
  Sawmill => city_building
  Quarry => nil
} 

The query is null since there is no city_building entry for this one, you get the idea.

Is there a way that Squeel does that? Or maybe another gem, without having to manually do an outer join?


Solution

  • I think you can try something like the below using Squeel. I am not sure about the where part. You will have to give one of the two join conditions.

    Building.joins{city}.joins(city_buildings.outer).where{(buidlings.id == city_buildings.building_id) & (cities.id == city_buildings.city_id)}
    

    or

    Building.joins{city}.joins(city_buildings.outer).where{buidlings.id == city_buildings.building_id}
    

    or

    Building.joins{city}.joins(city_buildings.outer).where{cities.id == city_buildings.city_id}