Topic: Complicated Join Logic
So I'm working on a digg style system.
Here'e the deal I have
Articles and Users Joined by a Picks model.
So articles and users have many picks and picks belong_to users and articles.
Picks have a "vote" column that can be -2 (sucks bad), -1 (kinda sucks), 0 (don't care), 1 (cool), 2 (very cool).
Now a simple method to find the most popular article and worst articles would be to sum up all the votes.
HOWEVER each User has a multiplier that updates daily.
So an articles score equals the sum of (pick1.vote * Pick1.User_Id.multiplier + pick2.vote * Pick2.User_Id.multiplier + pick3.vote ...
This makes things a bit complicated. Using the following SQL I can find all the articles with their scores sorted in order.
SUM(u.multiplier * p.vote) as 'score',
p.article_id as 'pick_article_id',
a.company_name as 's_comp',
u.id as 'user_id',
p.user_id as 'puid'
FROM picks p, users u, articles a
WHERE u.id = p.user_id
AND a.id = p.article_id
GROUP BY p.article_id
ORDER BY score desc
This leads to the first question. This result set comes back with a bunch of fields that will become attributes (I believe, I haven't tried this yet) of the Article objects that get returned from the find_by_sql method. If this is true is there some way to hide these?
Second I'm not sure how to do a search (simple one right now using like) to search all articles and return the articles that have a score with a score, articles without a score get a default score of 0.
Any leads would be appreciated!