From bef071dd0b6b634c5e8e49d8c1b9daa0baa4136c Mon Sep 17 00:00:00 2001 From: Jeremy Kemper Date: Mon, 29 Oct 2007 21:39:52 +0000 Subject: Introduce finder :joins with associations. Same :include syntax but with inner rather than outer joins. Closes #10012. git-svn-id: http://svn-commit.rubyonrails.org/rails/trunk@8054 5ecf4fe2-1ee6-0310-87b1-e25e094e27de --- activerecord/lib/active_record/associations.rb | 103 +++++++++++++++++++++---- 1 file changed, 89 insertions(+), 14 deletions(-) (limited to 'activerecord/lib/active_record/associations.rb') diff --git a/activerecord/lib/active_record/associations.rb b/activerecord/lib/active_record/associations.rb index f4369060f7..b250af47e8 100755 --- a/activerecord/lib/active_record/associations.rb +++ b/activerecord/lib/active_record/associations.rb @@ -486,7 +486,63 @@ module ActiveRecord # # When eager loaded, conditions are interpolated in the context of the model class, not the model instance. Conditions are lazily interpolated # before the actual model exists. - # + # + # == 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: + # + # 1. 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)]) + # + # 2. 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. + # # == Table Aliasing # # ActiveRecord uses table aliasing in the case that a table is referenced multiple times in a join. If a table is referenced only once, @@ -1121,7 +1177,13 @@ module ActiveRecord def find_with_associations(options = {}) catch :invalid_query do - join_dependency = JoinDependency.new(self, merge_includes(scope(:find, :include), options[:include]), options[:joins]) + if ar_joins = scope(:find, :ar_joins) + options = options.dup + options[:ar_joins] = ar_joins + end + includes = merge_includes(scope(:find, :include), options[:include]) + includes = merge_includes(includes, options[:ar_joins]) + join_dependency = JoinDependency.new(self, includes, options[:joins], options[:ar_joins]) rows = select_all_rows(options, join_dependency) return join_dependency.instantiate(rows) end @@ -1375,8 +1437,9 @@ module ActiveRecord class JoinDependency # :nodoc: attr_reader :joins, :reflections, :table_aliases - def initialize(base, associations, joins) + def initialize(base, associations, joins, ar_joins = nil) @joins = [JoinBase.new(base, joins)] + @ar_joins = ar_joins @associations = associations @reflections = [] @base_records_hash = {} @@ -1400,9 +1463,9 @@ module ActiveRecord unless @base_records_hash[primary_id] @base_records_in_order << (@base_records_hash[primary_id] = join_base.instantiate(row)) end - construct(@base_records_hash[primary_id], @associations, join_associations.dup, row) + construct(@base_records_hash[primary_id], @associations, join_associations.dup, row) unless @ar_joins end - remove_duplicate_results!(join_base.active_record, @base_records_in_order, @associations) + remove_duplicate_results!(join_base.active_record, @base_records_in_order, @associations) unless @ar_joins return @base_records_in_order end @@ -1444,7 +1507,7 @@ module ActiveRecord reflection = parent.reflections[associations.to_s.intern] or raise ConfigurationError, "Association named '#{ associations }' was not found; perhaps you misspelled it?" @reflections << reflection - @joins << JoinAssociation.new(reflection, self, parent) + @joins << (@ar_joins ? ARJoinAssociation : JoinAssociation).new(reflection, self, parent) when Array associations.each do |association| build(association, parent) @@ -1595,12 +1658,12 @@ module ActiveRecord def association_join join = case reflection.macro when :has_and_belongs_to_many - " LEFT OUTER JOIN %s ON %s.%s = %s.%s " % [ + " #{join_type} %s ON %s.%s = %s.%s " % [ table_alias_for(options[:join_table], aliased_join_table_name), aliased_join_table_name, options[:foreign_key] || reflection.active_record.to_s.foreign_key, parent.aliased_table_name, reflection.active_record.primary_key] + - " LEFT OUTER JOIN %s ON %s.%s = %s.%s " % [ + " #{join_type} %s ON %s.%s = %s.%s " % [ table_name_and_alias, aliased_table_name, klass.primary_key, aliased_join_table_name, options[:association_foreign_key] || klass.to_s.foreign_key ] @@ -1658,13 +1721,13 @@ module ActiveRecord end end - " LEFT OUTER JOIN %s ON (%s.%s = %s.%s%s%s%s) " % [ + " #{join_type} %s ON (%s.%s = %s.%s%s%s%s) " % [ table_alias_for(through_reflection.klass.table_name, aliased_join_table_name), parent.aliased_table_name, reflection.active_record.connection.quote_column_name(parent.primary_key), aliased_join_table_name, reflection.active_record.connection.quote_column_name(jt_foreign_key), jt_as_extra, jt_source_extra, jt_sti_extra ] + - " LEFT OUTER JOIN %s ON (%s.%s = %s.%s%s) " % [ + " #{join_type} %s ON (%s.%s = %s.%s%s) " % [ table_name_and_alias, aliased_table_name, reflection.active_record.connection.quote_column_name(first_key), aliased_join_table_name, reflection.active_record.connection.quote_column_name(second_key), @@ -1672,7 +1735,7 @@ module ActiveRecord ] when reflection.options[:as] && [:has_many, :has_one].include?(reflection.macro) - " LEFT OUTER JOIN %s ON %s.%s = %s.%s AND %s.%s = %s" % [ + " #{join_type} %s ON %s.%s = %s.%s AND %s.%s = %s" % [ table_name_and_alias, aliased_table_name, "#{reflection.options[:as]}_id", parent.aliased_table_name, parent.primary_key, @@ -1681,14 +1744,14 @@ module ActiveRecord ] else foreign_key = options[:foreign_key] || reflection.active_record.name.foreign_key - " LEFT OUTER JOIN %s ON %s.%s = %s.%s " % [ + " #{join_type} %s ON %s.%s = %s.%s " % [ table_name_and_alias, aliased_table_name, foreign_key, parent.aliased_table_name, parent.primary_key ] end when :belongs_to - " LEFT OUTER JOIN %s ON %s.%s = %s.%s " % [ + " #{join_type} %s ON %s.%s = %s.%s " % [ table_name_and_alias, aliased_table_name, reflection.klass.primary_key, parent.aliased_table_name, options[:foreign_key] || klass.to_s.foreign_key ] @@ -1723,7 +1786,19 @@ module ActiveRecord def interpolate_sql(sql) instance_eval("%@#{sql.gsub('@', '\@')}@") - end + end + + private + def join_type + "LEFT OUTER JOIN" + end + + end + class ARJoinAssociation < JoinAssociation + private + def join_type + "INNER JOIN" + end end end end -- cgit v1.2.3