Eager loading for the readership tracking problem
Martin Sojka
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/.
Mark Reginald James
> 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.
Martin Sojka
>
> 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!
Mark Reginald James
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