Реализация твиттер-подобного запроса временной шкалы на основе даты и времени для приложения для событий

Question

Я строю приложение для обмена событиями в Android с Codeigniter и MySQL. То, что я пытаюсь сделать, это отобразить список событий в приложении для Android с ограничением, скажем, только 10 результатов для каждого запроса, и позже я могу выполнить задачу "загрузить больше".

Если мы говорим о временной шкале Твиттера, допустим, что в таблице состояния есть следующие столбцы (PS, я их составил):

• id (считайте, что это первичный ключ с автоинкрементом)
• date_post
• чирикать

Если пользователь публикует твит, идентификатор автоматически присваивается со следующим значением автоматического приращения, а столбец даты и времени назначается с текущим временем, поэтому твит упорядочен правильно, более новый твит будет вставлен с большим идентификатором. При этом, чтобы отображать временную шкалу с результатом 20 каждый раз при загрузке, и мы загружаем ее из старого твита в новый, мы можем использовать следующий запрос (CMIIW):

SELECT * FROM TWEETS WHERE ID > last_id_loaded AND date_post >= last_date_loaded ORDER BY ID ASC LIMIT 20;

с параметром last_id_loaded, который мы отправляем на основе последнего загруженного твита, или 0, если это первый раз, то же самое с параметром last_date_loaded.

Теперь вернемся к моему приложению. У меня есть таблица с именем events со следующим столбцом:

• id (первичный ключ, автоинкремент)
• имя (название мероприятия)
• date_start (дата и время проведения мероприятия)
• так далее

Когда пользователь создает событие, идентификатору присваивается следующее значение автоинкремента, но в отличие от таблицы твитов, о которой я упоминал ранее, событие не упорядочено по дате и времени, поскольку больший идентификатор не означает более новое событие и наоборот. Например:

----- | ----------- | -------------
id    | name        | date_start
----- | ----------- | -------------
1       Event A       2013-05-26 13:13:00
2       Event B       2013-05-23 09:00:00
3       Event C       2013-05-27 10:00:00
4       Event D       2013-05-27 10:00:00
5       Event E       2013-05-27 10:00:00
6       Event F       2013-05-28 08:00:00
7       Event G       2013-05-31 13:13:00
8       Event H       2013-05-29 09:00:00
9       Event I       2013-05-29 08:00:00
10      Event J       2013-05-30 13:13:00

Если мы заказываем на основе столбца date_start, это должно быть: событие B, событие A, событие C, событие D, событие E, ...., событие G.

Как мы видим, есть 3 столбца с одинаковым значением date_start: Событие C, D и E. Если я хочу загрузить событие из более старого в более новое событие с ограничением только 3 для каждого запроса, то есть я ожидаю возврата события B, A, C, в настоящее время я использую этот следующий запрос:

SELECT * FROM EVENTS WHERE DATE_START >= last_date_retrieved ORDER BY DATE_START ASC, ID ASC

Но тогда как я могу убедиться, что следующий запрос приведет к следующему значению после этого? Я имею в виду предыдущий запрос: B, A, C с последним date_start 2013-05-27 10:00:00

Если я воспользуюсь предыдущим запросом, то получится C, D, E, а не D, E и F, как я ожидал.

Что мне здесь не хватает? Я готов получить несколько советов. Спасибо.

Answer 1

Безусловно, я придумаю это решение. Я добавляю новый столбец в таблицу событий под названием "counter", который будет использоваться как счетчик для событий с той же датой-временем. Например, если мы вставим новые данные о событии, он проверит недавно вставленное событие datetime с другим. Например, если в таблице существует такое же время и дата с максимальным значением 5, значение счетчика для вновь вставленного события будет присвоено с 5+1 = 6. Я использую триггер для этого

    CREATE TRIGGER `events_BINS` BEFORE INSERT ON events FOR EACH ROW
    BEGIN 
       SET @cnt = NULL;
       IF EXISTS (SELECT 1 FROM events where date_start = new.date_start)  THEN 
           SELECT MAX(counter) into @cnt FROM events where date_start = new.date_start;
           SET new.counter = @cnt + 1;
       ELSE
           SET new.counter = 1;
       END IF;
    END;

Я тоже создаю триггер перед обновлением:

CREATE TRIGGER `events_BUPD` BEFORE UPDATE ON events FOR EACH ROW
BEGIN
  SET @cnt = NULL;
IF old.date_start != new.date_start THEN 
  IF EXISTS (SELECT 1 FROM events where date_start = new.date_start)  THEN
    SELECT MAX(counter) into @cnt FROM events where date_start = new.date_start;
    SET new.counter = @cnt + 1;
  ELSE
    SET new.counter = 1;
  END IF;
END IF;
END;

При таком подходе я смогу сделать запрос с помощью следующего предложения:

WHERE (date_start = last_date_retrieved AND counter > last_counter_retrieved) 
OR (date_start > last_date_retrieved)
ORDER BY date_start, counter asc

Пожалуйста, дайте мне знать, если у кого-то из вас есть лучший подход к этому. Спасибо:)