V
V
veydlin2017-05-10 22:59:43
PHP
veydlin, 2017-05-10 22:59:43

How to load news not in chronological order?

The site has a news feed, while the user is scrolling through the first 10 news, a few more new ones can be added to the database or some of those that the user is watching right now can be deleted
. There is also a view mode by rating, where the rating can be from 0 to 5, of course, with In this scenario, the output chronology deteriorates, and it is no longer possible to rely on the latest news ID when loading new ones (WHERE `id` > blah_blah) The
request looks something like this:

SELECT *, (хитрая формула расчета рейтинга) as rating FROM `news`
WHERE (некое условие для дополнительной сортировки по категориям)
ORDER BY `rating` ASC
LIMIT 20

How then to upload new news?

Answer the question

In order to leave comments, you need to log in

3 answer(s)
I
Ivan Koryukov, 2017-05-11
@veydlin

Just use pagination:

SELECT *, something FROM `news`
WHERE (некое условие для фильтрации по категориям) AND  date < "time of page hit"
ORDER BY `something` ASC
LIMIT 20, 20

I've only added two small changes to this query. First, I set the offset for LIMIT, it must be calculated in PHP and inserted into the request. The first number is simply the page number minus one times the number of posts per page (page_num-1)*page_size. The second number is, as usual, the number of posts per page.
The second change - added an additional condition to where: select only those news that were already in the database, the user opened the page. This is necessary because if another one is added during the loading of news, then the pages will go (you could notice this when you scroll through the habr and the last post from the previous one is the first to be seen on the next page) and some news will be duplicated in the general list on the client. Time of page hit must be stored on the client and transmitted to the server when each next piece of data is loaded.
This method does not depend on how you sort the news - by date, rating, any other field... the main thing is that the first load and all subsequent ones are sorted the same way.

D
d-stream, 2017-05-10
@d-stream

decide for yourself what is more important, what is less important and indicate this with a comma in order by
ps where - this is not sorting, but selection

R
romy4, 2017-05-10
@romy4

Why not use output by date added/published?

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question