Petar Vasilev Petar Vasilev - 3 months ago 32
PHP Question

How to make a complex SQL query

I am trying to make a similar website to Hacker News and I found this algorithm which needs to help me sort the links on my site, here it is:

Score = (P-1) / (T+2)^G

P = points of an item (and -1 is to negate submitters vote)
T = time since submission (in hours)
G = Gravity, defaults to 1.8 in news.arc

so I need to make an SQL query which will sort the data using the above equation but I have no idea how to do that. Here is my tables structure:

Table 1 name: links
Table 1 fields: id, title, url, user_id, created_at

Table 2 name: votes
Table 2 fields: id, user_id, link_id, vote, created_at

in table 2 vote can be one of three values: 1, 0, -1

So is that even possible to do with SQL or do I have to do it in some other way?


If you want to use SQL only method.

First let map every parameter with its candidate in tables:

P Sum of vote column in votes Table.

T Current date - now() function - minus created_at column in links Table.

G Constant.

With this in mind, the following SQL select statement should return the score for each link.

select, (sum(vote) - 1) / POWER(DATEDIFF(now(),links.created_at)*24 + 2, 1.8)
from links, votes
where = votes.link_id
group by

sum(vote) ==> P

DATEDIFF(now(),links.created_at)*24 ==> T.

1.8 ==> G