Mysql – How to SELECT most common (viral) from a table and LIMIT results for pagination

countjoin;MySQLorder-byselect

Sorry if it's a dubplicate but I can't find an answer to the question.

Hello there so I have three tables users and articles and likes, a user can write an article, in the table articles there is also a timestamp column and can be liked by other users where a like is saved in likes table with the user id and secret id.

articles table:

+----+---------+--------------------+
| id | article |       timestamp    |
+----+---------+--------------------+
| 1  | A       |2019-02-10 22:19:02 |
| 2  | B       |2019-02-9 22:20:28  |
| 3  | C       |2019-02-9 22:21:10  |
| 4  | D       |2019-02-8 18:20:10  |   
| 5  | E       |2019-02-8 13:25:04  |
+----+---------+--------------------+

likes table:

+-----------+--------+
| articleid | userid |
+-----------+--------+
| 1         | 1      |
| 1         | 3      |
| 1         | 5      |
| 1         | 3      |
| 2         | 1      |
| 3         | 2      |
| 3         | 3      |
| 3         | 4      |
| 3         | 5      |
| 3         | 2      |
| 4         | 4      |
+-----------+--------+

I'm trying to do the pagination of the most liked articles in the shortest time,

as for example in this case article A is the top of the order because it got more likes in less time,

How would I do that and at the same time LIMIT the result to do the pagination?
My try was:

SELECT * FROM articles, (SELECT count(*) FROM likes WHERE articleid = id) as likes
GROUP BY articles
OREDER BY likes
LIMIT 10

I would do it in php without LIMIT but at the same time I want to repeat the query to get the next articles for the second page in the same way and I have no idea how to do that.

Thanks in advance!

Best Answer

Once the database is more than a 'toy', you will need to keep an up-to-date (or nearly so) table of actual like-counts. It would have two columns: article_id and like_ct with article_id as the PK.

With that (plus a JOIN) you can do an efficient pagination without OFFSET.

most liked in the last day or two days

Start with a 2-column table:

CREATE TABLE Likes (
    article_id ...,   -- for JOINing to Articles
    ts TIMESTAMP NOT NULL,
    PRIMARY KEY (article_id)
) ENGINE=InnoDB;

This should give you the list of article_ids ordered by most likes in the last 2 days, counting back from the instant the query was run.

SELECT article_id,
       COUNT(*) AS like_ct
    FROM Likes
    WHERE ts >= NOW() - INTERVAL 2 DAY
    GROUP BY article_id
    ORDER BY tot_likes DESC

With LIMIT 10 you can get the top 10. With OFFSET you can inefficiently get other 'pages'.

Use the above as a "derived" table and JOIN Articles USING(article_id) to get other info.

Related Question