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.

SELECT a.*,
SUM(u.multiplier * p.vote) as 'score',
p.article_id as 'pick_article_id',
a.company_name as 's_comp',
p.vote,
u.id as 'user_id',
u.multiplier,
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!

Re: Complicated Join Logic

bitdamaged wrote:

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?

Check out the count_by_sql method. This should return just a single value for the first column in the select - not an Article model with attributes. If you do this, don't select "a.*", just select the "SUM".

bitdamaged wrote:

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.

You can do a similar SELECT query as before, but of course use find_by_sql instead of count_by_sql. To limit the search based on the score, include a WHERE condition to handle that:

...
WHERE u.id = p.user_id
AND a.id = p.article_id
AND score > 0
...

If you want to simplify things and not use this crazy long query, you can cache the score inside of a new column in the articles table. This way it is very easy to limit the search, sort on it, etc. without doing the complicated find. However, the downside is you need to update the cached score for every vote or when the user's multiplier changes.

Railscasts - Free Ruby on Rails Screencasts

Re: Complicated Join Logic

Yeah I'm considering the cached score method.

A little more work up front but it might make the rest quite a bit easier.