From e658820e19a045cd6e08b79d1800805b47d6c011 Mon Sep 17 00:00:00 2001
From: Sean Molenaar <sean@seanmolenaar.eu>
Date: Tue, 13 Apr 2021 13:50:31 +0200
Subject: DB: only sort on item IDs

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

(limited to 'lib')

diff --git a/lib/Db/ItemMapperV2.php b/lib/Db/ItemMapperV2.php
index 44d1740cd..6e50a5fb5 100644
--- a/lib/Db/ItemMapperV2.php
+++ b/lib/Db/ItemMapperV2.php
@@ -62,7 +62,7 @@ class ItemMapperV2 extends NewsMapperV2
                 ->setParameter('user_id', $userId, IQueryBuilder::PARAM_STR);
 
         foreach ($params as $key => $value) {
-            $builder->andWhere("${key} = " . $builder->createNamedParameter($value));
+            $builder->andWhere("$key = " . $builder->createNamedParameter($value));
         }
 
         return $this->findEntities($builder);
@@ -201,7 +201,6 @@ class ItemMapperV2 extends NewsMapperV2
             ->from($this->tableName)
             ->where('feed_id = :feedId')
             ->andWhere('starred = false')
-            ->orderBy('last_modified', 'DESC')
             ->addOrderBy('id', 'DESC');
 
         if ($removeUnread === false) {
@@ -425,22 +424,6 @@ 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
@@ -471,16 +454,9 @@ class ItemMapperV2 extends NewsMapperV2
             ->andWhere('items.feed_id = :feedId')
             ->setParameter('userId', $userId)
             ->setParameter('feedId', $feedId)
-            ->orderBy('items.last_modified', ($oldestFirst ? 'ASC' : 'DESC'))
             ->addOrderBy('items.id', ($oldestFirst ? 'ASC' : 'DESC'));
 
-        if ($search !== []) {
-            foreach ($search as $key => $term) {
-                $term = $this->db->escapeLikeParameter($term);
-                $builder->andWhere("items.search_index LIKE :term${key}")
-                        ->setParameter("term${key}", "%$term%");
-            }
-        }
+        $builder = $this->addSearch($builder, $search);
 
         if ($limit >= 1) {
             $builder->setMaxResults($limit);
@@ -534,16 +510,9 @@ class ItemMapperV2 extends NewsMapperV2
             ->andWhere('feeds.deleted_at = 0')
             ->andWhere($folderWhere)
             ->setParameter('userId', $userId)
-            ->orderBy('items.last_modified', ($oldestFirst ? 'ASC' : 'DESC'))
             ->addOrderBy('items.id', ($oldestFirst ? 'ASC' : 'DESC'));
 
-        if ($search !== []) {
-            foreach ($search as $key => $term) {
-                $term = $this->db->escapeLikeParameter($term);
-                $builder->andWhere("items.search_index LIKE :term${key}")
-                        ->setParameter("term${key}", "%$term%");
-            }
-        }
+        $builder = $this->addSearch($builder, $search);
 
         if ($limit >= 1) {
             $builder->setMaxResults($limit);
@@ -589,17 +558,8 @@ class ItemMapperV2 extends NewsMapperV2
             ->andWhere('feeds.user_id = :userId')
             ->andWhere('feeds.deleted_at = 0')
             ->setParameter('userId', $userId)
-            ->orderBy('items.last_modified', ($oldestFirst ? 'ASC' : 'DESC'))
             ->addOrderBy('items.id', ($oldestFirst ? 'ASC' : 'DESC'));
 
-        if ($search !== []) {
-            foreach ($search as $key => $term) {
-                $term = $this->db->escapeLikeParameter($term);
-                $builder->andWhere("items.search_index LIKE :term${key}")
-                        ->setParameter("term${key}", "%$term%");
-            }
-        }
-
         if ($limit >= 1) {
             $builder->setMaxResults($limit);
         }
@@ -609,6 +569,8 @@ class ItemMapperV2 extends NewsMapperV2
                     ->setParameter('offset', $offset);
         }
 
+        $builder = $this->addSearch($builder, $search);
+
         switch ($type) {
             case ListType::STARRED:
                 $builder->andWhere('items.starred = :starred')
@@ -626,4 +588,43 @@ class ItemMapperV2 extends NewsMapperV2
 
         return $this->findEntities($builder);
     }
+
+    /**
+     * Add search parameters.
+     *
+     * @param IQueryBuilder $builder
+     * @param array         $terms
+     *
+     * @return IQueryBuilder
+     */
+    private function addSearch(IQueryBuilder $builder, array $terms): IQueryBuilder
+    {
+        if ($terms === []) {
+            return $builder;
+        }
+
+        foreach ($terms as $key => $term) {
+            $term = $this->db->escapeLikeParameter($term);
+            $builder->andWhere("items.search_index LIKE :term$key")
+                ->setParameter("term$key", "%$term%");
+        }
+
+        return $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';
+    }
 }
-- 
cgit v1.2.3