0

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!

0

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.

  • And how do I do that relatively to time, as in ordering by most liked in shortest time? – Casper X Feb 10 at 23:50
  • @CasperX - Be more precise in that ordering definition. "Max number in the last hour" will give you one list; "max number in the last week" is likely to give you quite a different list. Offhand, I can't think of a formula that gives a realistic metric somewhere between those. – Rick James Feb 10 at 23:55
  • tbh idk how to be precise with words, well let's say article 1 had 4 likes in 1hr and article 2 had 5 likes in 2hrs, in the way I want to order the articles I would like article 1 to show first and article 2 to show after, even so it has more likes, but relatively to time article 1 is more viral. Any idea how to do that? – Casper X Feb 11 at 0:02
  • @CasperX - You are saying (4 likes / 1 hour = 4.0) > ( 5 likes / 2 hr = 2.5) and if article 3 has 1 like in 5 minutes ( 1 / (1/12 hour) = 12.0 ), does that go to the top of the list? And 48 likes in a day (48/24 = 2.0) at the bottom of the list? – Rick James Feb 11 at 0:07
  • @CasperX - But when does the "nnn Hours" start? When the article is posted? Or when the site is opened for business? Example: a boring post that has been sitting there for a week, then gets 4 likes in the last hour -- high? or low in the ranking? – Rick James Feb 11 at 0:09
0

ORDER BY likes DESC should be used to get the highest likes first.

The second page is going to have LIMIT 10,10.

With a likes table as it is, its going to be hard to get quick performance out of this. Getting the number of likes into the main articles table would help get a quicker results of this if they are used often. If you need to use both perhaps an INSERT trigger on the likes table.

Once you have a likes column in articles, it will need an index on it.

Then your query becomes simply:

SELECT *
FROM articles
ORDER BY likes DESC
LIMIT 10

The second page way is:

SELECT *
FROM articles
ORDER BY likes DESC
LIMIT 10,10

If you got too many pages, you'll eventually start to be slow down as getting 10 pages at an offset needs to walk down to that offset first.

  • Adding likes column to articles and keep likes table to keep track of user's likes sounds like a good idea which is a lot easier actually, but I actually wanted to show most liked first relatively to time, how do I do that? – Casper X Feb 10 at 23:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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