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

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

Если мы говорим о временной шкале твиттера, скажем, в таблице состояний есть следующие столбцы (P.S. Я их придумал):

  • 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.

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

  • 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, как я ожидал.

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


mysql android codeigniter timeline
person bangandi    schedule 26.05.2013    source источник

Ответы (1)


arrow_upward
0
arrow_downward

Безусловно, я придумал это решение. Я добавляю новый столбец в таблицу событий под названием «счетчик», который будет использоваться в качестве счетчика для событий с той же датой и временем. Например, если мы вставляем новые данные о событии, он будет проверять дату и время недавно вставленного события с другими. Например, если в таблице существует одно и то же время и дата с максимальным значением 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 :

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

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

person bangandi    schedule 26.05.2013