From 9d5d35ce2396d0a059b4271ffbd454c0eacab0f8 Mon Sep 17 00:00:00 2001
From: Sean Molenaar <sean@seanmolenaar.eu>
Date: Tue, 23 Feb 2021 22:05:03 +0100
Subject: DB: Fix offset quotes

Issue GH-1200

Signed-off-by: Sean Molenaar <sean@seanmolenaar.eu>
---
 lib/Db/ItemMapperV2.php | 55 ++++++++++++++++++++++++++++---------------------
 1 file changed, 31 insertions(+), 24 deletions(-)

(limited to 'lib')

diff --git a/lib/Db/ItemMapperV2.php b/lib/Db/ItemMapperV2.php
index fe4bbabc0..33b4611f9 100644
--- a/lib/Db/ItemMapperV2.php
+++ b/lib/Db/ItemMapperV2.php
@@ -402,6 +402,22 @@ class ItemMapperV2 extends NewsMapperV2
         return $this->findEntities($builder);
     }
 
+    /**
+     * Generate an expression for the offset.
+     *
+     * @param bool $oldestFirst Sorting direction
+     *
+     * @return string
+     */
+    private function offsetWhere(bool $oldestFirst): string
+    {
+        if ($oldestFirst === true) {
+            return 'items.id > :offset';
+        }
+
+        return 'items.id < :offset';
+    }
+
     /**
      * @param string $userId      User identifier
      * @param int    $feedId      Feed identifier
@@ -424,21 +440,13 @@ class ItemMapperV2 extends NewsMapperV2
     ): array {
         $builder = $this->db->getQueryBuilder();
 
-        if ($oldestFirst === true) {
-            $offsetWhere = $builder->expr()->lt('items.id', ':offset');
-        } else {
-            $offsetWhere = $builder->expr()->gt('items.id', ':offset');
-        }
-
         $builder->select('items.*')
             ->from($this->tableName, 'items')
             ->innerJoin('items', FeedMapperV2::TABLE_NAME, 'feeds', 'items.feed_id = feeds.id')
             ->andWhere('feeds.user_id = :userId')
             ->andWhere('items.feed_id = :feedId')
-            ->andWhere($offsetWhere)
             ->setParameter('userId', $userId)
             ->setParameter('feedId', $feedId)
-            ->setParameter('offset', $offset)
             ->setMaxResults($limit)
             ->orderBy('items.last_modified', ($oldestFirst ? 'ASC' : 'DESC'))
             ->addOrderBy('items.id', ($oldestFirst ? 'ASC' : 'DESC'));
@@ -451,6 +459,11 @@ class ItemMapperV2 extends NewsMapperV2
             }
         }
 
+        if ($offset !== 0) {
+            $builder->andWhere($this->offsetWhere($oldestFirst))
+                ->setParameter('offset', $offset);
+        }
+
         if ($hideRead === true) {
             $builder->andWhere('items.unread = 1');
         }
@@ -486,20 +499,12 @@ class ItemMapperV2 extends NewsMapperV2
             $folderWhere = $builder->expr()->eq('feeds.folder_id', new Literal($folderId), IQueryBuilder::PARAM_INT);
         }
 
-        if ($oldestFirst === true) {
-            $offsetWhere = $builder->expr()->lt('items.id', ':offset');
-        } else {
-            $offsetWhere = $builder->expr()->gt('items.id', ':offset');
-        }
-
         $builder->select('items.*')
             ->from($this->tableName, 'items')
             ->innerJoin('items', FeedMapperV2::TABLE_NAME, 'feeds', 'items.feed_id = feeds.id')
             ->andWhere('feeds.user_id = :userId')
             ->andWhere($folderWhere)
-            ->andWhere($offsetWhere)
             ->setParameter('userId', $userId)
-            ->setParameter('offset', $offset)
             ->setMaxResults($limit)
             ->orderBy('items.last_modified', ($oldestFirst ? 'ASC' : 'DESC'))
             ->addOrderBy('items.id', ($oldestFirst ? 'ASC' : 'DESC'));
@@ -512,6 +517,11 @@ class ItemMapperV2 extends NewsMapperV2
             }
         }
 
+        if ($offset !== 0) {
+            $builder->andWhere($this->offsetWhere($oldestFirst))
+                ->setParameter('offset', $offset);
+        }
+
         if ($hideRead === true) {
             $builder->andWhere('items.unread = 1');
         }
@@ -540,19 +550,11 @@ class ItemMapperV2 extends NewsMapperV2
     ): array {
         $builder = $this->db->getQueryBuilder();
 
-        if ($oldestFirst === true) {
-            $offsetWhere = $builder->expr()->lt('items.id', ':offset');
-        } else {
-            $offsetWhere = $builder->expr()->gt('items.id', ':offset');
-        }
-
         $builder->select('items.*')
             ->from($this->tableName, 'items')
             ->innerJoin('items', FeedMapperV2::TABLE_NAME, 'feeds', 'items.feed_id = feeds.id')
             ->andWhere('feeds.user_id = :userId')
-            ->andWhere($offsetWhere)
             ->setParameter('userId', $userId)
-            ->setParameter('offset', $offset)
             ->setMaxResults($limit)
             ->orderBy('items.last_modified', ($oldestFirst ? 'ASC' : 'DESC'))
             ->addOrderBy('items.id', ($oldestFirst ? 'ASC' : 'DESC'));
@@ -565,6 +567,11 @@ class ItemMapperV2 extends NewsMapperV2
             }
         }
 
+        if ($offset !== 0) {
+            $builder->andWhere($this->offsetWhere($oldestFirst))
+                ->setParameter('offset', $offset);
+        }
+
         switch ($type) {
             case ListType::STARRED:
                 $builder->andWhere('items.starred = 1');
-- 
cgit v1.2.3