Hi,
I am doing an eager loading in ruby on rails using below statement
Article.find(:all, :include => [:asset, :vote],
:conditions=>"assets.parent_id is null",
:order=>"stat_final_ranking desc", :limit=>20)
the above statement resulted in this expensive query. After indexing, it
take about 3 seconds to execute. (before indexing it is about 25
seconds)
SELECT DISTINCT articles.id
FROM articles LEFT OUTER JOIN assets ON assets.attachable_id =
articles.id AND assets.attachable_type = ‘Article’
LEFT OUTER JOIN votes ON votes.voteable_id = articles.id AND
votes.voteable_type = 'Article’AND user_id = 2
WHERE (assets.parent_id is null)
ORDER BY stat_final_ranking desc
LIMIT 20
so, what I plan to do it is to cache the expensive query without
including vote table
Article.find(:all, :include => [:asset],
:conditions=>“assets.parent_id is null”,
:order=>“stat_final_ranking desc”, :limit=>20)
then later combine it with vote table which is different for each user.
Is the method to cache the expensive query the right approach?
Also, I have another question how to combine @articles and @vote after
getting it from database.
@articles= Article.find(:all, :include => [:asset],
:conditions=>“assets.parent_id is null”,
:order=>“stat_final_ranking desc”, :limit=>20)
with
@votes = Vote.find(:all, :conditions=> “user_id” …
Thanks in advance,
Beta B. wrote:
seconds)
One way forward with this non-caching option would be to
only load the votes of the current user. This can be done
with a trick that dynamically sets the :conditions option on
the vote association.
Also, I have another question how to combine @articles and @vote after
getting it from database.
@articles= Article.find(:all, :include => [:asset],
:conditions=>“assets.parent_id is null”,
:order=>“stat_final_ranking desc”, :limit=>20)
with
@votes = Vote.find(:all, :conditions=> “user_id” …
You want the votes to be hashed by article id, which the above would
not give you unless you hashed it after retrieval.
The alternative is to do an Article query like your original one,
but leaving out the asset association, and, for speed, only selecting
the id of the article model. Now you can include the user_id
condition because don’t care if the articles the user hasn’t voted
for are dropped.
Id-only selection would however require use of plugins that allow use of
the find :select option in combination with eager loading of the votes.
–
We develop, watch us RoR, in numbers too big to ignore.
Thanks Mark for the feedback,
you did bring good point in “id-only selection”.
Here is what I am trying to achieve:
Article1 – Vote1(by current user)
Asset1, Asset2, Asset3
Article2 – Vote2 (by current user)
Asset3, Asset4
I did have eager loading query to only load votes of the current user.
So that’s why I want to separate it out. Article and assets most likely
will stay the same and it is good candidate to cache it. However, votes
by user will keep changing.
If I use this method, I still need to figure out how to combine the
cache (Article and Assets) with the votes for current user.
The alternative is to do an Article query like your original one,
but leaving out the asset association, and, for speed, only selecting
the id of the article model. Now you can include the user_id
condition because don’t care if the articles the user hasn’t voted
for are dropped.
for your alternative, If I leave out the Assets, I will not be able to
get the asset arrays. I think it is pretty late night right now. May be
tomorrow, I will get more ideas. 
Thanks again for the feedback
Mark Reginald J. wrote:
The alternative is to do an Article query like your original one,
but leaving out the asset association, and, for speed, only selecting
the id of the article model. Now you can include the user_id
condition because don’t care if the articles the user hasn’t voted
for are dropped.
Id-only selection would however require use of plugins that allow use of
the find :select option in combination with eager loading of the votes.
Now your alternative make sense. It speed up the performance a lot.
Thanks again,
Beta