ruby - LEFT OUTER joins in Rails 3


Translate

I have the following code:

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

which is meant to find all posts and return them and the associated users and blogs. However, users are optional which means that the INNER JOIN that :joins generates is not returning lots of records.

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


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

  • Translate

    You can do with this with includes as documented in the Rails guide:

    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)
    

  • Translate

    I'm a big fan of the 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 polymorphic belongs_to relationships.


  • Translate

    Use eager_load:

    @posts = Post.eager_load(:user)
    

  • Translate

    By default when you pass ActiveRecord::Base#joins a named association, it will perform an INNER JOIN. You'll have 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 needed), named associations in the same form used for the :include option, which will perform an INNER JOIN on the associated table(s), or an array containing a mixture of both strings and named associations.

    If the value is a string, then the records will be returned read-only since they will have attributes that do not correspond to the table‘s columns. Pass :readonly => false to override.


  • Translate

    There is a left_outer_joins method in activerecord. You can use it like this:

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

  • Translate

    Good news, Rails 5 now supports LEFT OUTER JOIN. Your query would now look like:

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

  • Translate
    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")