:joins | change behavior inner join to left join

Question

I have two tables 'users' and 'lms_users'

class LmsUser
    belongs_to :user
end

class User
    has_one :lms_user
end

When I write User.all(:joins => :lms_user) it joins tables with Inner join.

I want a Left join. How can change this behavior? (I don't want to use SQL commands, only Ruby syntax for this.)

How can I handle this?

Welcome to StackOverflow. Please run a search on SO before asking a question. Here is your answer on how to do a LEFT OUTER JOIN in Rails 3: http://stackoverflow.com/questions/3245201/left-outer-joins-in-rails-3 — Chris Peters, Sep 19 '12 at 15:07
@MladenJablanović: Link that I posted to clearly shows that it's not possible. — Chris Peters, Sep 19 '12 at 17:10

Liping Huang · Answer 1 · 2012-09-20T07:08:47.940

0

:joins => "LEFT JOIN lms_users ON lms_users.user_id = users.id"

edited Sep 20 '12 at 07:08

answered Sep 19 '12 at 14:04

Liping Huang

4,378
4
29
46

Did you forget the "left outer" part? – Wayne Conrad Sep 19 '12 at 14:08
basically I don't want to use sql sytex. – Moosa Sep 19 '12 at 14:51

score 0 · Accepted Answer · answered Sep 19 '12 at 17:45

You can pull off LEFT JOIN without SQL, using includes, but only if you specify additional constraint (or merely an order clause) which involves a column from joined table(s). So,

User.includes(:lms_user).all

won't generate LEFT JOIN (but two separate queries instead), but

User.includes(:lms_user).where(:lms_user => {:role => 'admin'}).all

would.

More about this on

http://api.rubyonrails.org/classes/ActiveRecord/Associations/ClassMethods.html (see eager loading section)
http://ruby.ryanbigg.com/post/1468788928/left-outer-join-with-arel

score 0 · Answer 3 · answered Nov 03 '20 at 14:59

0

User.left_outer_joins(:lms_user) if You are on rails 5+

answered Nov 03 '20 at 14:59

alexey_the_cat

1,812
19
33

:joins | change behavior inner join to left join

3 Answers3