LEFT OUTER joins Rails 3

Question

I have the following code:

@posts = Post.joins(:user).joins(:blog).select 

which is designed to search for all posts and return them, as well as related users and blogs. However, users are optional, which means that the INNER JOIN that generates :joins does not return many records.

How to use this to generate a LEFT OUTER JOIN instead?

Answer 1

 @posts = Post.joins("LEFT OUTER JOIN users ON users.id = posts.user_id"). joins(:blog).select 

Answer 2

You can do this with includes as described in the Rails manual :

 Post.includes(:comments).where(comments: {visible: true}) 

Results in:

 SELECT "posts"."id" AS t0_r0, ... "comments"."updated_at" AS t1_r5 FROM "posts" LEFT OUTER JOIN "comments" ON "comments"."post_id" = "posts"."id" WHERE (comments.visible = 1) 

Answer 3

I am a big fan of squeel gem :

 Post.joins{user.outer}.joins{blog} 

It supports both inner and outer joins, as well as the ability to specify a class / type for belongs_to polymorphic relationships.

Answer 4

Use eager_load :

 @posts = Post.eager_load(:user) 

Answer 5

By default, when passing an ActiveRecord::Base#joins named association, it executes an INNER JOIN. You will need to pass a string representing your LEFT OUTER JOIN.

From the documentation :

:joins - either an SQL fragment for additional joins, like " LEFT JOIN comments ON comments.post_id = id " (rarely required), called associations in the same form as for the :include option, which will execute an INNER JOIN in a linked table ( tables) or an array containing a mixture of both rows and named associations.

If the value is a string, then the records will be returned read-only, as they will have attributes that do not match the columns of the table. Pass :readonly => false to cancel.

Answer 6

In activerecord there is a left_outer_joins method. You can use it as follows:

 @posts = Post.left_outer_joins(:user).joins(:blog).select 

Answer 7

Good news, Rails 5 now supports LEFT OUTER JOIN . Your request will now look like this:

 @posts = Post.left_outer_joins(:user, :blog) 

Answer 8

 class User < ActiveRecord::Base has_many :friends, :foreign_key=>"u_from",:class_name=>"Friend" end class Friend < ActiveRecord::Base belongs_to :user end friends = user.friends.where(:u_req_status=>2).joins("LEFT OUTER JOIN users ON users.u_id = friends.u_to").select("friend_id,u_from,u_to,u_first_name,u_last_name,u_email,u_fbid,u_twtid,u_picture_url,u_quote")