Left outer join does not bring expected results

Let say I have 3 models described below:

class User < ActiveRecord::Base
   has_many :subscriptions
   has_many :newsletters, through: :subscriptions
end

class Newsletter < ActiveRecord::Base
   has_many :subscriptions
   has_many :users, through: :subscriptions
end

class Subscription < ActiveRecord::Base
   belongs_to :user
   belongs_to :newsletter
end

I have created 2 users: Alice and Bob, and 3 newsletters (title 1, title 2 and title 3) Alice subscribed to "title 1" in HTML and Bob subscribed to "title 3" in Plain text

For a user's show action I want to render a table of all available newsletters and if the user subscribed to any - show relevant info:

User name: Alice

Subscriptions:
------------------- 
|Newsletter|Format|
------------------- 
| title 1  |Html  |
------------------- 
| title 2  |<NONE>|
------------------- 
| title 3  |<NONE>|
------------------- 

But instead I am getting this table:

------------------- 
|Newsletter|Format|
------------------- 
| title 1  |Html  |
------------------- 
| title 2  |<NONE>|
------------------- 
| title 3  |Text  |  <--- this is coming from Bob's record
------------------- 

here is how I am pulling the data:

def show
  @subs = Newsletter.joins("LEFT OUTER JOIN subscriptions ON  
      subscriptions.newsletter_id = newsletters.id and   
      subscriptions.user_id = #{@user.id}").includes(:subscriptions)
end

The query above turned to following SQL:

Newsletter Load (0.3ms)    
SELECT "newsletters".* FROM "newsletters"   
LEFT OUTER JOIN subscriptions 
ON subscriptions.newsletter_id = newsletters.id and subscriptions.user_id = 1

Subscription Load (0.2ms)  
SELECT "subscriptions".* FROM "subscriptions" 
WHERE "subscriptions"."newsletter_id" IN (1, 2, 3)

I have tried adding .where(:user_id == @user.id) to the end of the query but result was the same.

What I am doing wrong?

Answers


I found a workaround...

I am pulling data and manually building an array to pass to view:

  def show
    @subs =[]

    Newsletter.all.each do |nl|
    @subs << {:newsletter => nl, :subscription =>  
                  @user.subscriptions.to_a.detect{|s| s.newsletter_id==nl.id}}

    end
  end

Then in view I iterate that array and have access to the models:

<ul>
    <% @subs.each do |s| %>
         <li>
             <%= s[:newsletter].title %> - ( 
               <%= if s[:subscription] 
                 s[:subscription].format 
               else
                 'NONE'
               end %> )
         </li>
   <% end %>
</ul>

If someone has better solution I would be happy to know


Need Your Help

JQuery Cross Fade Transition Onload

jquery transition onload fade

What I'm trying to achieve is simple. I've seen plugins do this, but I don't wanna use a plugin.