CODEWITHSUNDEEP
ruby-on-railshas-and-belongs-to-many
bcb
bcb

Reputation: 939

Rails 3.2 - HABTM retrieve only parents with no children

I realize there are several article on SO about this (or at least similar topics), but none of those solutions are working for me.

First things first, don't suggest "you should have used has_many_through!" . . . you are probably correct, but we are past that option at this point :)

Basically I have a HABTM relationship between Buildings and Users. What I'm trying to do is to retrieve only Buildings that have NO users. Here is the code I'm using now:

Building.includes(:users).where(:users => { :id => nil})

This is returning buildings that have users as well (even though by all accounts it shouldn't be).

I'm not sure if this code isn't working because it is a HABTM relationship or because of the "double" HABTM relationship I have going on between buildings and users/owners, namely:

building.rb

has_and_belongs_to_many :users, :uniq => true
has_and_belongs_to_many :owners, :class_name => 'User', :foreign_key => 'building_id', :association_foreign_key => 'owner_id', :uniq => true

user.rb

has_and_belongs_to_many :buildings, :uniq => true
has_and_belongs_to_many :owned_buildings, :class_name => 'Building', :foreign_key => 'owner_id', :association_foreign_key => 'building_id', :uniq => true

Ideally I'd like to return all the buildings that don't have users and owners (i.e., for a building to be returned, it needs to not have users AND not have owners), but at this point I'd be fine just returning buildings that don't have users (irregardless of owners).

It should be noted that the above code does successfully return all the buildings with no owners (not what I'm looking for, but close), which makes absolutely no sense to me. I've checked the join table and the data looks good (nothing in there that leads me to believe the data is corrupted).

I would just use a counter cache and test that the counter is 0, but counter cache isn't automatically implemented for HABTM and it seems hacky to implement that just for this problem (and I've read that implementing your own counter cache for HABTM doesn't cover 100% of all creation/deletion cases).

Any ideas?

UPDATE

So far this is the only thing I could come up with that actually works (for finding buildings with no users or owners):

arr = BuildingsUser.all.map(&:building_id).uniq
buildings_without_users = Building.where("buildings.id NOT IN (?)",arr)

I had to create the following "join model" (the table was already there from when I created the HABTM) in order for this to work:

buildings_user.rb

class BuildingsUser < ActiveRecord::Base
  belongs_to :building
  belongs_to :user
end

This feels very hacky (and inefficient) but it works. Would obviously like a better solution.

Upvotes: 1

Views: 106

Answers (1)

Marc-Alexandre B&#233;rub&#233;
Marc-Alexandre B&#233;rub&#233;

Reputation: 2171

If you don't mind having RAW SQL I'd do it like this 

Building.where('NOT EXISTS(SELECT 1 FROM buildings_users WHERE building_id = buildings.id)')

This basically gets you all the buildings that doesn't have a record in the join table (assuming the join table name is buildings_users but I might be wrong on that.

Upvotes: 1

Related Questions