Finding unassociated objects

— September 9, 2006 at 15:21 PDT

Say you have two models with a one-to-many relationship (using belongs_to and has_many). For example, a manager has many employees.

# Manager
has_many :employees

# Employee
belongs_to :manager

Rails associations make it easy to find all the employees belonging to a manager (manager.employees). And a rather simple find with conditions gets you the list of employees who belong to any manager, or even the orphaned employees with no manager.

# Employee
def self.find_managed
  find(:all, :conditions => "manager_id IS NOT NULL")

def self.find_orphans
  find(:all, :conditions => "manager_id IS NULL")

What if we need to find all the employees that don't work for a particular manager?

# Manager
has_many :employees do
  def not
    find(:conditions => [" != ?",])

That association extension lets us use manager.employees.not to find the list of employees that don't work for that manager.

It's a bit harder to find manager with no employees at all. Still, we can do it without too much ugly SQL in our pretty Rails code. We want to find all managers for which there are no employees that belong to them.

# Manager
def self.find_without_employees
  find(:all, :readonly => false,
       :select => "managers.*",
       :joins => "LEFT OUTER JOIN employees e ON = e.manager_id",
       :conditions => " IS NULL")

Then Manager.find_without_employees returns all the managers without any employees. Time for a re-org!

15 commentsassociations, rails

  1. Gabe da Silveira2006-09-09 15:42:07

    It's nice that AR gives such good SQL hooks. I never really believed in ORM because it tends to be incomplete and clunky, however Rails is pretty good about letting you get around the incompleteness without much pain.

    Sometimes I shudder to think of beginning programmers coming to Rails without understanding SQL or HTTP fundamentals and the horrific loads they must be generating on their SQL servers. But then I suppose that opinion is just like assembly programmers in the 70s who thought C was just too high level :)

  2. Michael Schuerig2006-09-09 16:24:28

    Let me take the opportunity to advertise my Association Extensions plugin :-) It gives you methods for

    • association_candidates
    • association.candidates
    • association_complement
    • association.complement
    • association_unrelated
    • association.unrelated
    • associationsingularids
    • associationsingularids= (now ignores blank ids)

    Available from Rubyforge svn://

  3. namxam2006-09-09 16:30:18

    Well, interesting article, although I would like to ask/ test if it really is as magic as you describe. Two things came straight to my mind… and I would like to share them before I will go to bed.

    1. When seeking emloyees without a manager, you are checking NULL, but how should this field be NULL? For sure you can create it this way, but I think in most cases employees will be created in association with managers… so this might be really seldom the case.

    2. suppose you are new to ror and you created a manager and a lot of employees which belong to him. Now you delete the manager, but not the employees… so you have those employee which still seem to belong to the manager, but he is gone. the employees still would not have a NULL in the referencing manager_id field. How would you find them?

  4. rubylicio.us2006-09-09 21:58:23

    naxnam: on #2 you would just do the opposite of the last codesnippet I think, something like this maybe:

    :select => "employees.*", :joins => "LEFT OUTER JOIN managers m ON", :conditions => " IS NULL"

    Gabe da Silveira: Fix a categorized atom/rss feed on your site? I want to add you to :)

  5. James H2006-09-09 22:03:08

    Very interseting. I didn't realize the below code was possible:

    has_many :employees do
      def not
        find(:conditions => [" != ?",])

    What other sorts of tricks can one do with blocks after such relational statements?

  6. Josh Susser2006-09-09 22:34:06

    @naxnam: You can use the :dependent option on the manager:

    has_many :employees, :dependent => :nullify

    When the manager is destroyed, all its employees' manager_id fields will be set to nil/NULL.

    The standard way of doing thing in Rails is to use the business logic in the models to manage foreign key relationships (the above being an example of how to do that). If that isn't sufficient you can always fall back to foreign key constraints in the database. But while that kind of constraint may seem safer, I think it actually leads to sloppy thinking about the business logic and potential messes because you don't really understand the relationships.

  7. Josh Susser2006-09-09 22:37:16

    @james h: Those blocks are called association extensions. They are documented in the ActiveRecord API docs, and I have several examples of using them in other articles on the blog. They let you create new methods in the association, and are a powerful and convenient way to give associations richer behavior.

  8. Carlos Gabaldon2006-09-11 10:18:11

    Thanks Josh, very helpful tip.

  9. John Ward2006-09-12 01:32:33

    Josh, Thanks for the post. I've already been using Association Extensions but was unware that proxy_owner mapped to the Association Proxy.

    I've been trying to figure out how to do this for a while and have been obviously using the wrong search terms. This will allow me to do same more elegantly.

    On a side issue is there any way you can retrieve the scope of the proxy_owner such as the joins, conditions etc?

    Thanks, John

  10. Josh Susser2006-09-12 08:01:09

    @John Ward: the proxy_reflection accessor of the association proxy returns the association's reflection object. You can use that to find all the options on the association just as joins and conditions. And for completeness, proxy_target returns the singular object (for has_one and belongs_to) or collection that is returned by the association.

  11. John Ward2006-09-14 02:41:19

    Thanks for your reply.


  12. brooks hollar2006-09-28 08:57:41

    I was wondering if the following declaration:

    def find_without_employees
      find(:all, :readonly => false,

    shouldn't be

    def Manager.find_without_employees

    since in your example you intended it to be a class method. If not, is there a fancy way to declare class methods in rails?

    Thanks! brooks

  13. Josh Susser2006-09-28 09:04:24

    Brooks, good catch. Fixed.

  14. eric 2006-09-28 10:22:30


    quick note, the two Employee examples need to have self dot in front of them also, since they should be class methods too.

    great blog btw.

  15. Tim Kuo2006-10-27 11:35:07

    Is there an elegant way to get (in one fell swoop?) all the "this employe works for, this employee doesn't work for" info for one specified manager (or all managers at once)? I tried LEFT JOIN-ing (and :include), but when I add in the :conditions to specify a manager, that blows away all the "nil" results from the JEFT JOIN.

    Or, for an analogous example, I've got :employees and :trainingsessions, and :trainingscores as a hasmany :though join table. I'd like a way (without repetitively hammering the MYSQL db) to display a grid of :employees and :trainingsessions, so that I can (with a glance at a rhtml "View") see if a specific employee has taken a specifc training_session.

    I've got a solution that brute force iterates through all :employees and all :training_sessions, but there must be a more elegant solution....

    Or is there?

    • Tim

Sorry, comments for this article are closed.