Getting Popular posts

tables: POSTS (id 开发者_如何学Go title data userid)

CONNECTIONS( postid userid)

I want to get rows from the POSTS table by the amount of c.postid values that are the same in the CONENCTIONS c table

In easier terms, I want to get the most popular posts, with the most connections.

I hope that makes sense.

---

SELECT p.id, p.name, c.cnt from posts p INNER JOIN (
    SELECT postid, count(*) as cnt 
    FROM connections 
    GROUP BY postid ORDER BY cnt DESC /*limit goes here if you need it!*/) c 
ON c.postid = p.id

A little warning:

It would be pretty heavy query in case if you dealing with high loads. I'd suggest to have the cnt (a number of connections) field right within your posts table (it would be a denormalization and it's harder to maintain, but would work good enough to decrease querying time by 2 times or more if you would create an index on cnt field).

Let me know if the query has worked or not, cause I can't check myself right now.

---

Well to get the max value from the connections table, try this:

SELECT MAX(postid) FROM userid

Not quite sure about the rest, but that will give you the highest value of postid in the table userid.

---

Replace the 50 with the number of popular posts that you want to get:

select * from post where postid in (
select postid from (
select count(*),postid from connections group by postid order by count(*) desc limit 50 ))

---

I haven't tested it, but try something like this. It will join posts to connections in order of the most connections. (you can adjust the limit depending on your requirements)

select posts.id, posts.title, posts.data, posts.userid, COUNT(connections.postid) AS total
from posts join connections on posts.id=connections.postid
order by total desc
limit 20