This is a replacement for http://dev.rubyonrails.org/ticket/9923 which I submitted about a week ago.
This accomplishes the same goals with a much cleaner syntax which should fit much more naturally with the current AR api. Thanks to Josh Susser for a great suggestion.
Here is an excerpt from the documentation I added to associations.rb
== Adding Joins For Associations to Queries Using the :joins option
ActiveRecord::Base#find provides a :joins option, which takes either a string or values accepted by the :include option.
if the value is a string, the it should contain a SQL fragment containing a join clause.
Non-string values of :joins will add an automatic join clause to the query in the same way that the :include option does but with two critical
differences:
- A normal (inner) join will be performed instead of the outer join generated by :include.
this means that only objects which have objects attached to the association will be included in the result.
For example, suppose we have the following tables (in yaml format):
Authors
fred:
id: 1
name: Fred
steve:
id: 2
name: Steve
Contributions
only:
id: 1
author_id: 1
description: Atta Boy Letter for Steve
date: 2007-10-27 14:09:54
and corresponding AR Classes
class Author: < ActiveRecord::Base
has_many :contributions
end
class Contribution < ActiveRecord::Base
belongs_to :author
end
The query Author.find(:all) will return both authors, but Author.find(:all, :joins => :contributions) will
only return authors who have at least one contribution, in this case only the first.
This is only a degenerate case of the more typical use of :joins with a non-string value.
For example to find authors who have at least one contribution before a certain date we can use:
Author.find(:all, :joins => :contributions, :conditions => ["contributions.date <= ?", 1.week.ago.to_s(:db)])
- Only instances of the class to which the find is sent will be instantiated. ActiveRecord objects will not
be instantiated for rows reached through the associations.
The difference between using :joins vs :include to name associated records is that :joins allows associated tables to
participate in selection criteria in the query without incurring the overhead of instantiating associated objects.
This can be important when the number of associated objects in the database is large, and they will not be used, or
only those associated with a paricular object or objects will be used after the query, making two queries more
efficient than one.
Note that while using a string value for :joins marks the result objects as read-only, the objects resulting
from a call to find with a non-string :joins option value will be writable.
End of documentation excerpt
The new form of :joins can be used in find and calculation methods, and in scoped finds.
The patch includes extensive tests. I tried to adapt every existing test I could find in the existing ar tests which used the :include option which would be sensible if :include were changed to :joins.
All tests pass using rake test_mysql.
