From cf62bcd3d31a801a16a06608b6a953613f68fbde Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Wed, 4 Sep 2013 09:28:27 +0200 Subject: Handle paging for entries with identical date Paging now works even when many entries have the same date. SQL speed could probably be improved by testing first on date, and then on CONCAT. Also, having an index on date would probably help too. --- app/models/Entry.php | 6 +++--- 1 file changed, 3 insertions(+), 3 deletions(-) diff --git a/app/models/Entry.php b/app/models/Entry.php index a763feca0..a27cce148 100755 --- a/app/models/Entry.php +++ b/app/models/Entry.php @@ -381,8 +381,8 @@ class EntryDAO extends Model_pdo { } elseif ($state == 'read') { $where .= ' AND is_read = 1'; } - if (!empty($limitFromId)) { - $where .= ' AND date ' . ($order === 'low_to_high' ? '<=' : '>=') . ' (SELECT date from freshrss_entry WHERE id = "' . $limitFromId . '")'; + if (!empty($limitFromId)) { //TODO: Consider using LPAD(e.date, 11) //CONCAT is for cases when many entries have the same date + $where .= ' AND CONCAT(e.date, e.id) ' . ($order === 'low_to_high' ? '<=' : '>=') . ' (SELECT CONCAT(s.date, s.id) from freshrss_entry s WHERE s.id = "' . $limitFromId . '")'; } if ($order == 'low_to_high') { @@ -393,7 +393,7 @@ class EntryDAO extends Model_pdo { $sql = 'SELECT e.* FROM ' . $this->prefix . 'entry e' . ' INNER JOIN ' . $this->prefix . 'feed f ON e.id_feed = f.id' . $where - . ' ORDER BY date' . $order . ', id' . $order; + . ' ORDER BY e.date' . $order . ', e.id' . $order; if (!empty($limitCount)) { $sql .= ' LIMIT ' . ($limitCount + 2); //TODO: See http://explainextended.com/2009/10/23/mysql-order-by-limit-performance-late-row-lookups/ -- cgit v1.2.3