Petar Vasilev Petar Vasilev - 12 days ago 5
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

where,
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?

Answer

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 links.id, (sum(vote) - 1) / POWER(DATEDIFF(now(),links.created_at)*24 + 2, 1.8)
from links, votes
where links.id = votes.link_id
group by links.id

sum(vote) ==> P

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

1.8 ==> G

Comments