Revize a0025b38

Optimalizace SQL dotazu

app/model/repository/TransliterationRepository.php

@@ -36 +36 @@
      * @param ArrayHash $queryParams objekt s podmínkami pro hledaný text
      * @param $offset null|int
      * @param $limit null|int
+     * @param bool $countOnly : flag pokud chceme vrátit pouze počet výsledků
      * @return \Nette\Database\ResultSet
      */
-    public function transliterationsFulltextSearch(ArrayHash $queryParams, int $offset = null, int $limit = null)
+    public function transliterationsFulltextSearch(ArrayHash $queryParams, int $offset = null, int $limit = null, bool $countOnly = false)
     {
         $where = '';
         $whereArgs = [];
@@ -118 +119 @@
             $whereArgs[] = $this->prepareQueryArgByOperator($queryParams['date'], $queryParams['date_condition']);
         }
 
-        $query = "SELECT 
-                    t.id_transliteration as id,
-                    s.id_surface as surface_id, 
-                    b.book_abrev, 
-                    t.chapter, 
-                    l.transliteration, 
-                    l.id_line as line_id,
-                    l.line_number 
-                  FROM transliteration t
+        if ($countOnly)
+        {
+            $query = "SELECT COUNT(t.id_transliteration) as count ";
+        } else
+        {
+            $query = "SELECT t.id_transliteration as id,
+                             s.id_surface as surface_id, 
+                             b.book_abrev, 
+                             t.chapter, 
+                             l.transliteration, 
+                             l.id_line as line_id,
+                             l.line_number ";
+        }
+
+        $query .= "FROM transliteration t
                   LEFT JOIN surface s ON s.id_transliteration = t.id_transliteration
                   LEFT JOIN line l ON l.id_surface = s.id_surface
                   LEFT JOIN book b ON t.id_book = b.id_book
-                  WHERE " . $where .
-                 " ORDER BY id DESC ";
+                  WHERE " . $where;
+
+        if (!$countOnly)
+        {
+            $query .= " ORDER BY id DESC ";
+        }
 
         if($offset !== null && $limit !== null)
         {
@@ -145 +156 @@
 
     public function getTransliterationsFulltextSearchTotalCount($queryParams)
     {
-        return $this->transliterationsFulltextSearch($queryParams, null, null)->getRowCount();
+        return $this->transliterationsFulltextSearch($queryParams, null, null, true)->fetch()->count;
     }
 
     /**
@@ -225 +236 @@
      * @param ArrayHash $queryParams : parametry hledání
      * @param int|null $offset
      * @param int|null $limit
+     * @param bool $countOnly : flag pokud chceme vrátit pouze počet výsledků
      * @return \Nette\Database\ResultSet
      */
-    public function transliterationCatalogueSearch(ArrayHash $queryParams, int $offset = null, int $limit = null)
+    public function transliterationCatalogueSearch(ArrayHash $queryParams, int $offset = null, int $limit = null, bool $countOnly = false)
     {
         /**
          * @var $mc string : udává jestli je mezi vyhledávanými parametry logické AND nebo OR
@@ -303 +315 @@
             $where = substr($where, strlen($mc));
         }
 
-        $query = "SELECT 
-                    t.id_transliteration as id,
-                    b.book_abrev, 
-                    t.chapter, 
-                    t.id_museum
-                  FROM transliteration t
+        if ($countOnly)
+        {
+            $query = "SELECT COUNT(t.id_transliteration) as count ";
+        } else
+        {
+            $query = "SELECT t.id_transliteration as id,
+                             b.book_abrev, 
+                             t.chapter, 
+                             t.id_museum ";
+        }
+
+        $query .= "FROM transliteration t
                   LEFT JOIN book b ON t.id_book = b.id_book ";
 
         if (!empty($where))
@@ -316 +334 @@
             $query .= "WHERE " . $where;
         }
 
-        $query .= " GROUP BY b.book_abrev, t.chapter, t.id_transliteration ORDER BY t.chapter ";
+        if(!$countOnly)
+        {
+            $query .= " GROUP BY b.book_abrev, t.chapter, t.id_transliteration ORDER BY t.chapter ";
 
-        if($offset !== NULL && $limit !== NULL){
-            $query .= ' LIMIT ?, ?';
-            $whereArgs[] = (int) $offset;
-            $whereArgs[] = (int) $limit;
+            if ($offset !== NULL && $limit !== NULL)
+            {
+                $query .= ' LIMIT ?, ?';
+                $whereArgs[] = (int)$offset;
+                $whereArgs[] = (int)$limit;
+            }
         }
 
 
@@ -340 +362 @@
      */
     public function getTransliterationCatalogueSearchTotalCount(ArrayHash $queryParams, int $offset = null, int $limit = null): int
     {
-        return $this->transliterationCatalogueSearch($queryParams, $offset, $limit)->getRowCount();
+        return $this->transliterationCatalogueSearch($queryParams, $offset, $limit, true)->fetch()->count;
     }
 }