0

I feel like I'm missing something obvious here, so hopefully someone can help me out.

Lets say I have 3 models: Buildings, Shelves, and Books.

The problem I am trying to solve is to remove all the shelves which have no books out of a particular group of buildings.

The basic structure would look like this:

class Building
  has_many :shelves
end

class Shelf
  has_many :books
  belongs_to :building
end

class Book
  belongs_to :shelf
end

I don't need this to be extremely efficient, but I do want to keep it from being horribly inefficient. More specifically, If I have a building whose empty shelves need to be deleted, Here is what I've been trying to do to find the shelves which are empty:

Shelf.joins(:books).where("books.id is NULL").where(:building_id => building_id)<other_conditions...>

However, this query isn't giving me the results I expect (its not returning any shelves when I know some are empty). I was hoping that the join would fill in NULL for the books.id column for a shelf with no books, but it appears I am mistaken.

Is there another way to accomplish this, or is there something simple that I'm missing? Thanks for the help!

Pete
  • 17,885
  • 4
  • 32
  • 30
  • Did you try it with `includes` instead of `joins`? – Benoit Garret Sep 01 '11 at 15:05
  • Wow, interesting. That seems to be working correctly. There is obviously a part of how includes works that I didn't realize. I thought includes was more solely about eagerly loading associations while joins was for querying against the attributes of the associations. either way, put it in an answer and get the rep you deserve :) – Pete Sep 01 '11 at 15:09
  • I'm also a bit fuzzy on the difference between `includes` and `joins`, but I know that it works in the case that you want both associated and not associated record. – Benoit Garret Sep 01 '11 at 15:13

1 Answers1

1

Try the same query with includes instead of joins.

books.id can never be NULL, because you asked for the books which belong to shelves. joins will only give you the records for which the association is set, so you're asking for the books whose ids are both NULL and NOT NULL.

This answer might shed some light on the differences between the two methods.

Community
  • 1
  • 1
Benoit Garret
  • 14,027
  • 4
  • 59
  • 64
  • Good stuff. It didn't occur to me that joins only returns records with the association filled, but it certainly makes sense. I also don't think I've ever tried to include my associations attributes in a query using "includes", but thinking about it now, It certainly makes sense that it is allowed. Thanks for the clarification. – Pete Sep 01 '11 at 15:18