has_many :through

Finding unassociated objects

# Manager
has_many :employees

# Employee
belongs_to :manager

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

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

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

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

15 comments — associations, rails

Comments

1. Gabe da Silveira – 2006-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 Schuerig – 2006-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://rubyforge.org/var/svn/assocext/association_extensions/trunk

3. namxam – 2006-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.us – 2006-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 employees.id=m.employe_id", :conditions => "m.id IS NULL"

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

5. James H – 2006-09-09 22:03:08

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

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

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

6. Josh Susser – 2006-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 Susser – 2006-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 Gabaldon – 2006-09-11 10:18:11

   Thanks Josh, very helpful tip.

9. John Ward – 2006-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 Susser – 2006-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 Ward – 2006-09-14 02:41:19

    Thanks for your reply.

    John

12. brooks hollar – 2006-09-28 08:57:41

    I was wondering if the following declaration:

    
    #Manager
    def find_without_employees
      find(:all, :readonly => false,
      ...
    end
    

    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 Susser – 2006-09-28 09:04:24

    Brooks, good catch. Fixed.

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

    Josh,

    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 Kuo – 2006-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.