Rails 4中的左外连接

小开

最佳答案

You can pass a string that is the join-sql too. eg joins("LEFT JOIN StudentEnrollment se ON c.id = se.course_id")

Though I'd use rails-standard table naming for clarity:

joins("LEFT JOIN student_enrollments ON courses.id = student_enrollments.course_id")

小开

You'd execute the query as:

Course.joins('LEFT JOIN student_enrollment on courses.id = student_enrollment.course_id')
.where(active: true, student_enrollments: { student_id: SOME_VALUE, id: nil })

小开

It'a join query in Active Model in Rails.

Please click here for More info about Active Model Query Format.

@course= Course.joins("LEFT OUTER JOIN StudentEnrollment
ON StudentEnrollment .id = Courses.user_id").
where("StudentEnrollment .id IS NULL AND StudentEnrollment .student_id =
<SOME_STUDENT_ID_VALUE> and Courses.active = true").select

小开

There is actually a "Rails Way" to do this.

You could use Arel, which is what Rails uses to construct queries for ActiveRecrods

I would wrap it in method so that you can call it nicely and pass in whatever argument you would like, something like:

class Course < ActiveRecord::Base
....
def left_join_student_enrollments(some_user)
courses = Course.arel_table
student_entrollments = StudentEnrollment.arel_table


enrollments = courses.join(student_enrollments, Arel::Nodes::OuterJoin).
on(courses[:id].eq(student_enrollments[:course_id])).
join_sources


joins(enrollments).where(
student_enrollments: {student_id: some_user.id, id: nil},
active: true
)
end
....
end

There is also the quick (and slightly dirty) way that many use

Course.eager_load(:students).where(
student_enrollments: {student_id: some_user.id, id: nil},
active: true
)

eager_load works great, it just has the "side effect" of loding models in memory that you might not need (like in your case)
Please see Rails ActiveRecord::QueryMethods .eager_load
It does exactly what you are asking in a neat way.

小开

I've been struggling with this kind of problem for quite some while, and decided to do something to solve it once and for all. I published a Gist that addresses this issue: https://gist.github.com/nerde/b867cd87d580e97549f2

I created a little AR hack that uses Arel Table to dynamically build the left joins for you, without having to write raw SQL in your code:

class ActiveRecord::Base
# Does a left join through an association. Usage:
#
#     Book.left_join(:category)
#     # SELECT "books".* FROM "books"
#     # LEFT OUTER JOIN "categories"
#     # ON "books"."category_id" = "categories"."id"
#
# It also works through association's associations, like `joins` does:
#
#     Book.left_join(category: :master_category)
def self.left_join(*columns)
_do_left_join columns.compact.flatten
end


private


def self._do_left_join(column, this = self) # :nodoc:
collection = self
if column.is_a? Array
column.each do |col|
collection = collection._do_left_join(col, this)
end
elsif column.is_a? Hash
column.each do |key, value|
assoc = this.reflect_on_association(key)
raise "#{this} has no association: #{key}." unless assoc
collection = collection._left_join(assoc)
collection = collection._do_left_join value, assoc.klass
end
else
assoc = this.reflect_on_association(column)
raise "#{this} has no association: #{column}." unless assoc
collection = collection._left_join(assoc)
end
collection
end


def self._left_join(assoc) # :nodoc:
source = assoc.active_record.arel_table
pk = assoc.association_primary_key.to_sym
joins source.join(assoc.klass.arel_table,
Arel::Nodes::OuterJoin).on(source[assoc.foreign_key].eq(
assoc.klass.arel_table[pk])).join_sources
end
end

Hope it helps.

小开

Use Squeel:

Person.joins{articles.inner}
Person.joins{articles.outer}

小开

See below my original post to this question.

Since then, I have implemented my own .left_joins() for ActiveRecord v4.0.x (sorry, my app is frozen at this version so I've had no need to port it to other versions):

In file app/models/concerns/active_record_extensions.rb, put the following:

module ActiveRecordBaseExtensions
extend ActiveSupport::Concern


def left_joins(*args)
self.class.left_joins(args)
end


module ClassMethods
def left_joins(*args)
all.left_joins(args)
end
end
end


module ActiveRecordRelationExtensions
extend ActiveSupport::Concern


# a #left_joins implementation for Rails 4.0 (WARNING: this uses Rails 4.0 internals
# and so probably only works for Rails 4.0; it'll probably need to be modified if
# upgrading to a new Rails version, and will be obsolete in Rails 5 since it has its
# own #left_joins implementation)
def left_joins(*args)
eager_load(args).construct_relation_for_association_calculations
end
end


ActiveRecord::Base.send(:include, ActiveRecordBaseExtensions)
ActiveRecord::Relation.send(:include, ActiveRecordRelationExtensions)

Now I can use .left_joins() everywhere I'd normally use .joins().

----------------- ORIGINAL POST BELOW -----------------

If you want OUTER JOINs without all the extra eagerly loaded ActiveRecord objects, use .pluck(:id) after .eager_load() to abort the eager load while preserving the OUTER JOIN. Using .pluck(:id) thwarts eager loading because the column name aliases (items.location AS t1_r9, for example) disappear from the generated query when used (these independently named fields are used to instantiate all the eagerly loaded ActiveRecord objects).

A disadvantage of this approach is that you then need to run a second query to pull in the desired ActiveRecord objects identified in the first query:

# first query
idents = Course
.eager_load(:students)  # eager load for OUTER JOIN
.where(
student_enrollments: {student_id: some_user.id, id: nil},
active: true
)
.distinct
.pluck(:id)  # abort eager loading but preserve OUTER JOIN


# second query
Course.where(id: idents)

小开

Combining includes and where results in ActiveRecord performing a LEFT OUTER JOIN behind the scenes (without the where this would generate the normal set of two queries).

So you could do something like:

Course.includes(:student_enrollments).where(student_enrollments: { course_id: nil })

Docs here: http://guides.rubyonrails.org/active_record_querying.html#specifying-conditions-on-eager-loaded-associations

小开

If anyone came here looking for a generic way to do a left outer join in Rails 5, you can use the #left_outer_joins function.

Multi-join example:

Ruby:

Source.
select('sources.id', 'count(metrics.id)').
left_outer_joins(:metrics).
joins(:port).
where('ports.auto_delete = ?', true).
group('sources.id').
having('count(metrics.id) = 0').
all

SQL:

SELECT sources.id, count(metrics.id)
FROM "sources"
INNER JOIN "ports" ON "ports"."id" = "sources"."port_id"
LEFT OUTER JOIN "metrics" ON "metrics"."source_id" = "sources"."id"
WHERE (ports.auto_delete = 't')
GROUP BY sources.id
HAVING (count(metrics.id) = 0)
ORDER BY "sources"."id" ASC

小开

Adding to the answer above, to use includes, if you want an OUTER JOIN without referencing the table in the where (like id being nil) or the reference is in a string you can use references. That would look like this:

Course.includes(:student_enrollments).references(:student_enrollments)

or

Course.includes(:student_enrollments).references(:student_enrollments).where('student_enrollments.id = ?', nil)

http://api.rubyonrails.org/classes/ActiveRecord/QueryMethods.html#method-i-references

小开

You could use left_joins gem, which backports left_joins method from Rails 5 for Rails 4 and 3.

Course.left_joins(:student_enrollments)
.where('student_enrollments.id' => nil)

小开

I know that this is an old question and an old thread but in Rails 5, you could simply do

Course.left_outer_joins(:student_enrollments)

小开

If anyone out there still needs true left_outer_joins support in Rails 4.2 then if you install the gem "brick" on Rails 4.2.0 or later it automatically adds the Rails 5.0 implementation of left_outer_joins. You would probably want to turn off the rest of its functionality, that is unless you want an automatic "admin panel" kind of thing available in your app!