1

I have three tables:

  1. posts - contains the posts by users,
  2. post_reviews - contains the likes on the posts, and
  3. post_share - contains the post id and user id of that which is shared.

I would like to select five popular posts using these three tables. The popular posts are those that have the highest no of reviews and shares. I am finding it really hard to construct a SQL query for this.

A slight help or guidance in this matter will be highly appreciated.

UPDATE:

The table structure is as follows:

table posts{

id - PK
uid - FK
post - text
}

table post_reviews{

id - PK
pid - FK (Post Id)
uid - FK
like_unlike - ENUM
}

table post_share{

id - PK
pid - FK (Post Id)
uid - FK
}
2
  • Please supply more information. Run these SHOW CREATE TABLE posts\G, SHOW CREATE TABLE posts_reviews\G and SHOW CREATE TABLE posts_share\G and post their output in the question. Commented Oct 16, 2012 at 14:46
  • @RolandoMySQLDBA - Updated question with the table structures. Commented Oct 16, 2012 at 14:56

1 Answer 1

1

5 Most Popular Posts By Reviews

SELECT A.uid,B.post_count reviews,A.post
FROM posts A LEFT JOIN
(
    SELECT * FROM
    (
        SELECT pid,COUNT(1) post_count
        FROM post_reviews
        GROUP BY pid
        ORDER BY post_count DESC
    ) AA LIMIT 5
) B
ON A.id = B.pid;

5 Most Popular Posts By Shares

SELECT A.uid,B.post_count shares,A.post
FROM posts A LEFT JOIN
(
    SELECT * FROM
    (
        SELECT pid,COUNT(1) post_count
        FROM post_shares
        GROUP BY pid
        ORDER BY post_count DESC
    ) AA LIMIT 5
) B
ON A.id = B.pid;

I hope this gives you a start...

2
  • @RonaldoMySQLDBA - Thank you for the effort. I will start with this. Commented Oct 16, 2012 at 15:47
  • There was a syntax error I saw. I corrected it. Commented Oct 16, 2012 at 15:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.