[icinga-checkins] icinga.org: icingaweb2/master: Use GROUPBY instead of DISTINCT and subqueries when counting

git at icinga.org git at icinga.org
Thu Nov 13 16:45:15 CET 2014


Module: icingaweb2
Branch: master
Commit: 77f5bc39321528381b9d0540927b242b2f12513a
URL:    https://git.icinga.org/?p=icingaweb2.git;a=commit;h=77f5bc39321528381b9d0540927b242b2f12513a

Author: Johannes Meyer <johannes.meyer at netways.de>
Date:   Thu Nov 13 16:32:20 2014 +0100

Use GROUPBY instead of DISTINCT and subqueries when counting

---

 library/Icinga/Data/Db/DbQuery.php |    8 +++++++-
 library/Icinga/Data/PivotTable.php |   30 +++++++++++-------------------
 2 files changed, 18 insertions(+), 20 deletions(-)

diff --git a/library/Icinga/Data/Db/DbQuery.php b/library/Icinga/Data/Db/DbQuery.php
index 94765d9..56118ed 100644
--- a/library/Icinga/Data/Db/DbQuery.php
+++ b/library/Icinga/Data/Db/DbQuery.php
@@ -72,6 +72,12 @@ class DbQuery extends SimpleQuery
         parent::init();
     }
 
+    public function setUseSubqueryCount($useSubqueryCount = true)
+    {
+        $this->useSubqueryCount = $useSubqueryCount;
+        return $this;
+    }
+
     public function where($condition, $value = null)
     {
         // $this->count = $this->select = null;
@@ -259,7 +265,7 @@ class DbQuery extends SimpleQuery
      */
     public function getCountQuery()
     {
-		// TODO: there may be situations where we should clone the "select"
+        // TODO: there may be situations where we should clone the "select"
         $count = $this->dbSelect();
 
         $this->applyFilterSql($count);
diff --git a/library/Icinga/Data/PivotTable.php b/library/Icinga/Data/PivotTable.php
index ab6fab1..0a1f009 100644
--- a/library/Icinga/Data/PivotTable.php
+++ b/library/Icinga/Data/PivotTable.php
@@ -69,11 +69,13 @@ class PivotTable
     protected function prepareQueries()
     {
         $this->xAxisQuery = clone $this->baseQuery;
-        $this->xAxisQuery->distinct();
+        $this->xAxisQuery->group($this->xAxisColumn);
         $this->xAxisQuery->columns(array($this->xAxisColumn));
+        $this->xAxisQuery->setUseSubqueryCount();
         $this->yAxisQuery = clone $this->baseQuery;
-        $this->yAxisQuery->distinct();
+        $this->yAxisQuery->group($this->yAxisColumn);
         $this->yAxisQuery->columns(array($this->yAxisColumn));
+        $this->yAxisQuery->setUseSubqueryCount();
 
         return $this;
     }
@@ -85,24 +87,14 @@ class PivotTable
      */
     protected function adjustSorting()
     {
-        $currentOrderColumns = $this->baseQuery->getOrder();
-        $xAxisOrderColumns = array(array($this->baseQuery->getMappedField($this->xAxisColumn), SimpleQuery::SORT_ASC));
-        $yAxisOrderColumns = array(array($this->baseQuery->getMappedField($this->yAxisColumn), SimpleQuery::SORT_ASC));
-
-        foreach ($currentOrderColumns as $orderInfo) {
-            if ($orderInfo[0] === $xAxisOrderColumns[0][0]) {
-                $xAxisOrderColumns[0] = $orderInfo;
-            } elseif ($orderInfo[0] === $yAxisOrderColumns[0][0]) {
-                $yAxisOrderColumns[0] = $orderInfo;
-            } else {
-                $xAxisOrderColumns[] = $orderInfo;
-                $yAxisOrderColumns[] = $orderInfo;
-            }
+        if (false === $this->xAxisQuery->hasOrder($this->xAxisColumn)) {
+            $this->xAxisQuery->order($this->xAxisColumn, 'ASC');
+        }
+
+        if (false === $this->yAxisQuery->hasOrder($this->yAxisColumn)) {
+            $this->yAxisQuery->order($this->yAxisColumn, 'ASC');
         }
-//TODO: simplify this whole function. No need to care about mapping
-//        foreach ($xAxisOrderColumns as 
-//        $this->xAxisQuery->setOrder($xAxisOrderColumns);
-//        $this->yAxisQuery->setOrder($yAxisOrderColumns);
+
         return $this;
     }
 



More information about the icinga-checkins mailing list