MySQL Forums
Forum List  »  PHP

Ordering the Query Digg.com style
Posted by: Livvy Love
Date: April 06, 2006 09:50AM

I am wondering if there is a way to order a SQL query by having Highly voted nodes mixed in with more recent nodes that have a decent post count. I checked out the Order by pages on this site and it didn't seem to me to have what I needed by how they explained things.

I was thinking that 50% of the weight for the ranking be in how recent it is and 50% of the weight be how many votes it has. So if something is very recent and it has about 50 votes it could be ranked up there with an older story that has about 250 votes. Like how digg.com does it. I don't really know how they calculate the top stories but I figure it is something similar to that.

This is the query I have now that is only having the top voted stories.

$sql = db_rewrite_sql("SELECT n.nid, n.created, v.content_id, v.value, v.tag, v.function
FROM {node} n INNER JOIN {votingapi_cache} v ON n.nid = v.content_id
WHERE n.type = 'aggregator2_item' AND n.status = 1 AND v.tag = 'vote' AND v.function = 'sum' AND v.value > 1
OR n.type = 'weblink' AND n.status = 1 AND v.tag = 'vote' AND v.function = 'sum' AND v.value > 1
ORDER BY v.value DESC, n.created DESC");


So I was wondering if it's possible to create this functionality just with MySQL(which would make me so happy) or do I have to do some PHP code to create that sorted list? Please point me in the right direction

Options: ReplyQuote


Subject
Written By
Posted
Ordering the Query Digg.com style
April 06, 2006 09:50AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.