Rails - Feb 2007 - Eager loading for the readership tracking problem

Hello,

In summary, I''m trying to find the best solution for eager loading one
association based on the current_user passed from the controller.

This is the necessary models code:

class User < ActiveRecord::Base
  has_many :posts
  has_many :readerships
end


class Post < ActiveRecord::Base
  belongs_to :author, :class_name => ''User''
  has_many :readerships
end

class Readership < ActiveRecord::Base
  belongs_to :post
  belongs_to :reader, :class_name => ''User''
end


Next I have action in the posts controller that lists 50 most recent
posts plus I want to show whether the post was read by the current_user
(logged in user) or not using the ''NEW'' icon (each readership
includes
read_at attribute that should be also displayed when the post was read
by the current_user).

The thing is that I want to prevent additional SQL queries to get the
current_user''s readership for each post (resulting in 50 more queries
in
the view).

I cannot use this:

@posts = Post.find :all,
  :include => [ :readerships, :author ],
  :conditions => "readerships.reader_id IS NULL OR readerships.reader_id
= #{current_user.id}",
  :order => ''posts.created_at DESC'',
  :limit => 50

because this would exclude the posts that were read by some other users
except the current_user. If no user read the post, reader_id for that
joined row would be NULL so it would be included which is good. But if
some other user read the post (and not the current_user) the joined row
would not be included because reader_id in the joined row would not be
NULL nor current_user.id


It seems like I cannot use this:

@posts = Post.find :all,
  :include => [ :readerships, :author ],
  :joins => "LEFT OUTER JOIN readerships ON (readerships.post_id
posts.id AND readerships.reader_id=#{current_user.id})",
  :order => ''posts.created_at DESC'',
  :limit => 50

at first I thought that it is possible to override include join with
custom :join but after reading about Table Aliases in AR
(http://api.rubyonrails.com/classes/ActiveRecord/Associations/ClassMethods.html)
it seems like the custom join takes precedence but it doesn''t override
the join generated by :include.


I know that I can omit the :include and just use 2 custom joins (one for
author and one for readership) but that would not map the results into
model objects automatically. Plus I hope there is some elegant solution
for this.

IMO the ideal solution for this should be something like this (it is not
valid code, just an example):

@posts = Post.find :all,
  :include => [ :readerships.by_reader(current_user), :author ],
  :order => ''posts.created_at DESC'',
  :limit => 50

:readerships.by_reader would be defined in the Post model as the
readerships extension method accepting reader and using it''s id as the
join condition ( e.g. ON (readerships.post_id = #{id} AND
readerships.reader_id=#{reader.id} )

Any ideas?

-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Martin Sojka wrote:
> It seems like I cannot use this:
> 
> @posts = Post.find :all,
>   :include => [ :readerships, :author ],
>   :joins => "LEFT OUTER JOIN readerships ON (readerships.post_id
> posts.id AND readerships.reader_id=#{current_user.id})",
>   :order => ''posts.created_at DESC'',
>   :limit => 50
Perhaps something like this would work:

class Post < ActiveRecord::Base
   cattr_accessor :current_user_id
   has_one :current_user_readership, :class_name =>
''Readership'',
              :conditions => ''readerships.reader_id =
#{current_user_id}''
end

Post.current_user_id = current_user.id
@posts = Post.find :all, :include => [:current_user_readership, :author],
                          :order => ''posts.created_at
DESC'', :limit => 50


Otherwise you''ll have to either use the available AR mods that allow
the writing of custom eager sql, or select the author and readership
info into the Post models.

-- 
We develop, watch us RoR, in numbers too big to ignore.


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

> Perhaps something like this would work:
> 
> class Post < ActiveRecord::Base
>    cattr_accessor :current_user_id
>    has_one :current_user_readership, :class_name =>
''Readership'',
>               :conditions => "readerships.reader_id = 
> #{current_user_id}"
> end
Mark, thanks for great idea. I''ve started to play with this in the 
console. It works great when I hardcode the current_user_id into the 
:conditions (like :conditions => "readerships.reader_id = > 9").

But so far I had no luck to use the current_user_id in the condition. It 
always results in blank space there, eventually resulting in bad SQL - 
SELECT * FROM readerships WHERE ... AND (readerships.reader_id = ).

In the console, Post.current_user_id = 9 assigns the class variable 
well. I then can see it when typing:

Post.current_user_id
=> 9
p = Post.find :first
p.current_user_id
=> 9

But calling p.current_user_readership results in the invalid SQL 
described above.

Somehow, the current_user_id is not passed into the :conditions 
statement.

Otherwise this solution would be great since I can assign 
current_user_id to Post before calling the find to eager load just the 
current user readership. I just need to find the way to pass that 
variable into the has_one :conditions.

Anybody solved this before? Thanks!


-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Martin Sojka wrote:
>> Perhaps something like this would work:
>>
>> class Post < ActiveRecord::Base
>>    cattr_accessor :current_user_id
>>    has_one :current_user_readership, :class_name =>
''Readership'',
>>               :conditions => "readerships.reader_id = 
>> #{current_user_id}"
>> end
> 
> Mark, thanks for great idea. I''ve started to play with this in the
> console. It works great when I hardcode the current_user_id into the 
> :conditions (like :conditions => "readerships.reader_id = >
9").
> 
> But so far I had no luck to use the current_user_id in the condition. It 
> always results in blank space there, eventually resulting in bad SQL - 
> SELECT * FROM readerships WHERE ... AND (readerships.reader_id = ).
> 
> In the console, Post.current_user_id = 9 assigns the class variable 
> well. I then can see it when typing:
> 
> Post.current_user_id
> => 9
> p = Post.find :first
> p.current_user_id
> => 9
> 
> But calling p.current_user_readership results in the invalid SQL 
> described above.
> 
> Somehow, the current_user_id is not passed into the :conditions 
> statement.
> 
> Otherwise this solution would be great since I can assign 
> current_user_id to Post before calling the find to eager load just the 
> current user readership. I just need to find the way to pass that 
> variable into the has_one :conditions.
> 
> Anybody solved this before? Thanks!
Sorry, it looks like the sql interpolation is not done on the
model class, perhaps it''s on the association class itself. If
that''s the case you can''t use the accessor-setting approach
since has_one does not support association extensions.

However I''ve posted a before about setting the conditions for
an association dynamically:

http://groups.google.com/group/rubyonrails-talk/browse_thread/thread/7f7f715d917b9faa

That should work:

class Post < ActiveRecord::Base
   has_one :current_user_readership, :class_name =>
''Readership''

   def self.current_user=(user)
      reflect_on_association(:current_user_readership).options[:conditions]     
"readerships.reader_id = #{user.id}"
   end
end

Post.current_user = current_user
@posts = Post.find :all, :include => [:current_user_readership, :author],
                           :order => ''posts.created_at
DESC'', :limit => 50


-- 
We develop, watch us RoR, in numbers too big to ignore.


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Thanks Mark! This works like expected. Now I can finish it ;)

-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---