Как правильно выполнить зарос, начиная с указанного ID элемента?

Question

[Алексей Коновалов]

Всем привет! Пишу API и появилась такая задача...
Предположим в базе лежит 30 постов, с ID равным от 1 до 30. Все по порядку как положено (AUTO INCREMENT)
Запрашиваются список постов, получили 10 первых постов, затем приложение, для получения следующих 10 постов делает запрос с указанием ID последней известной записи. В нашем случае id_last_element=10.
По идее, сервер проверяет, если присутствует id то добавляем к запросу условие. В нашем случае получаем запрос:

SELECT * FROM posts WHERE id < 10 ORDER BY id DESC LIMIT 10

Все хорошо, но что если появляется возможность сортировки? Мы не знаем какие условия сортировки выберет пользователь. Это может быть дата, может быть DESC или ASC, может быть количество просомтров, рейтинг, количество лайков и т.д. Но везде нужно добавитьWHERE id < 10, или WHERE id > 10. И все это в зависимости от указанной сортировки DESC или ASC.
Поэтому, на стороне PHP приходится создавать кучу условий на каждую возможную сортировку и присутствие параметра id_last_element.

т.е. суть вопроса в том, как эффективней заставить Mysql выбрать данные учитывая сортировку, но отбрасывая все ID, которые были выведены до указанного?

Сразу оговорюсь, что LIMIT 10, 10 не подходит. Потому что посты постоянно добавляются, а значит отсчет от первого, что и делает offset в limit, не подходит. Ведь записи будут дублироваться.

Answer 1

[ThunderCat]

Какой то непонятный вопрос, точнее странный способ выбирать следующие N постов. Способ сортировки НИКАК не влияет на следующие 10, точнее это не ваша забота. В случае апи я указываю метод сортировки и страницу, так как апи НЕ ЗНАЕТ где и как я остановился, к нему приходит запрос "отдать страницу 4 с сортировкой по рейтингу", что должно вызвать у вас sql

SELECT * FROM `posts` 
WHERE 1 
ORDER BY `rating` DESC 
LIMIT 40,10

Все, результат отдать пользователю. Если следующим запросом к апи будет 5 страница с сортировкой по дате - отдаете 5 страницу с сортировкой по дате.

UPD:

Все по порядку как положено (AUTO INCREMENT)

и тут посты с ид 5 и 12 удаляются, и - опа, не по порядку (

Comments

[Алексей Коновалов]

Опишу пример:
Приложение запросило 1 страницу (10 постов с ID от 1 до 10), все ок, но теперь пользователь остановился скажем на 5 минут почитать пост. В этот момент другие пользователи добавляют еще 10 постов... Теперь выполнив запрос с LIMIT 10, 10 окажется так, что offset начинается с новых постов, и поэтому посты с ID 1 - 10 сервер вернет повторно. Потому что в списке offset они теперь на позициях 10-20

[ThunderCat]

Алексей Коновалов,

В этот момент другие пользователи добавляют еще 10 постов...

которые добавятся в конец и никак не повлияют на выборку которая стоит выше.

Потому что в списке offset они теперь на позициях 10-20

чегой-то вдруг, они в конец пойдут, по вашему старые будут там же, а новые будут в 31-40.
Это раз.

Второе - вы бы сказали конкретно для чего это у вас такие хитрозадрюченные "посты" - было бы легче что-то советовать, а так - для каждого вида данных есть решение, которое лучше всего подходит, а решений почти всегда больше одного естественно.

[Алексей Коновалов]

ThunderCat, я это сказал в конце вопроса:

Сразу оговорюсь, что LIMIT 10, 10 не подходит. Потому что посты постоянно добавляются, а значит отсчет от первого, что и делает offset в limit, не подходит. Ведь записи будут дублироваться.

[ThunderCat]

Алексей Коновалов, вы как то или объясняете криво что нужно, или логика у вас хромает,

Потому что посты постоянно добавляются

Все добавляющиеся посты идут В КОНЕЦ выборки, причем тут офсет и дублирование???

[Алексей Коновалов]

ThunderCat,

которые добавятся в конец и никак не повлияют на выборку которая стоит выше. Это раз.

Я так же упоминал про сортировку. Она может меняться, и если посты будут выводиться с конца в начало, то "это раз" не сработает.

[ThunderCat]

Алексей Коновалов, с динамическими данными в таком случае в любом раскладе будет лажа, по этому я и спросил - что за посты?

[Евгений]

Алексей Коновалов, все ровно это не проблема бэка, что юзер пропал на 5 минут и тут в его выборке появилась целая страница, обновит и перелистнет, только рад будет, что по интересующей его теме появилось 10 постов

Answer 2

[Rsa97]

Если у вас произвольная сортировка, то на id вообще нельзя полагаться. С чего вы взяли, что при сортировке, например, по рейтингу, на первую страницу попадут записи с id от 1 до 10? Это могут быть id 27, 31, 105, ..., 7, 12. И никакого условия по id вы не придумаете.
Мало того, пока пользователь просматривает страницу, рейтинги могут поменяться. Если вы продолжите выдачу по списку id, то пользователь получит уже неправильно отсортированные данные.
Общего решения этой задачи не существует, приходится либо выводить записи только в хронологическом порядке, либо использовать произвольную сортировку и мириться с возможным пропуском и/или дублированием записей на разных страницах.

Answer 3

[Stalker_RED]

Передавайте не id_last_element, а номер страницы.

Comments

[Алексей Коновалов]

Это ничего не меняет. Будь то страница или смещение offset, ниже в ответе я описал в комментарии пример:

Приложение запросило 1 страницу (10 постов с ID от 1 до 10), все ок, но теперь пользователь остановился скажем на 5 минут почитать пост. В этот момент другие пользователи добавляют еще 10 постов... Теперь выполнив запрос с LIMIT 10, 10 окажется так, что offset начинается с новых постов, и поэтому посты с ID 1 - 10 сервер вернет повторно. Потому что в списке offset они теперь на позициях 10-20

[Stalker_RED]

Алексей Коновалов, а если он отошел не на пять минут, а на три месяца, например? А потом снова запросил вторую страницу.

Хабр покажет новые посты. Башорг покажет примерно те-же самые, потому что у него нумерация с конца (там первая страница, это самые старые записи).

А вы что хотите показать?

Answer 4

[Артур]

При первом запросе запоминать минимальное/максимальное значение поля по которому сортируется, в зависимости от направления сортировки, и при втором запросе его учитывать.

SELECT * FROM posts WHERE id > 20 ORDER BY id DESC LIMIT 10 OFFSET 10

И соответственно условие меняется в зависимости от сортировки.