[BUGFIX] Extbase: Fix counting objects on joined tables 20/51320/3
authorMorton Jonuschat <m.jonuschat@mojocode.de>
Sun, 15 Jan 2017 21:21:44 +0000 (13:21 -0800)
committerBenni Mack <benni@typo3.org>
Fri, 27 Jan 2017 12:11:25 +0000 (13:11 +0100)
While it's normally fine to replace DISTINCT with GROUP BY when counting
objects this results in a different resultset. The GROUP BY statement will
have n rows with the count per grouped combination while the DISTINCT
based query will have one row with the count of unique combinations.

Use a COUNT(DISTINCT uid) to get the expected result.
DISTINCT is supported on MySQL, PostgreSQL, Oracle and MSSQL Server.

Change-Id: If3193113529f1bb01693dba39abccfe349f169b1
Resolves: #79330
Releases: master
Reviewed-on: https://review.typo3.org/51320
Tested-by: TYPO3com <no-reply@typo3.com>
Reviewed-by: Manuel Selbach <manuel_selbach@yahoo.de>
Reviewed-by: Christian Kuhn <lolli@schwarzbu.ch>
Tested-by: Christian Kuhn <lolli@schwarzbu.ch>
Reviewed-by: Benni Mack <benni@typo3.org>
Tested-by: Benni Mack <benni@typo3.org>
typo3/sysext/extbase/Classes/Persistence/Generic/Storage/Typo3DbBackend.php
typo3/sysext/extbase/Tests/Functional/Persistence/CountTest.php
typo3/sysext/extbase/Tests/Functional/Persistence/Fixtures/persons.xml
typo3/sysext/extbase/Tests/Functional/Persistence/Fixtures/tags-mm.xml

index 95aaa1a..78a262b 100644 (file)
@@ -416,13 +416,22 @@ class Typo3DbBackend implements BackendInterface, SingletonInterface
         }
 
         $queryBuilder = $this->objectManager->get(Typo3DbQueryParser::class)
-            ->convertQueryToDoctrineQueryBuilder($query);
+            ->convertQueryToDoctrineQueryBuilder($query)
+            ->resetQueryPart('orderBy');
+
+        if (count($queryBuilder->getQueryPart('groupBy')) !== 0) {
+            $source = $queryBuilder->getQueryPart('from')[0];
+            // Tablename is already quoted for the DBMS, we need to treat table and field names separately
+            $tableName = $source['alias'] ?: $source['table'];
+            $fieldName = $queryBuilder->quoteIdentifier('uid');
+            $queryBuilder->resetQueryPart('groupBy')
+                ->selectLiteral(sprintf('COUNT(DISTINCT %s.%s)', $tableName, $fieldName));
+        } else {
+            $queryBuilder->count('*');
+        }
 
         try {
-            $count = $queryBuilder->resetQueryPart('orderBy')
-                ->count('*')
-                ->execute()
-                ->fetchColumn(0);
+            $count = $queryBuilder->execute()->fetchColumn(0);
         } catch (DBALException $e) {
             throw new SqlErrorException($e->getPrevious()->getMessage(), 1472074379);
         }
index 79ab879..3567fcf 100644 (file)
@@ -235,6 +235,6 @@ class CountTest extends \TYPO3\CMS\Components\TestingFramework\Core\FunctionalTe
                 $query->equals('tagsSpecial.name', 'SpecialTagForAuthor1')
             )
         );
-        $this->assertSame(3, $query->count());
+        $this->assertSame(4, $query->count());
     }
 }
index f140363..693e88e 100644 (file)
                <tags>0</tags>
                <tags_special>1</tags_special>
        </tx_blogexample_domain_model_person>
+       <tx_blogexample_domain_model_person>
+               <uid>4</uid>
+               <pid>0</pid>
+               <firstname>Another Author</firstname>
+               <lastname>With special tag</lastname>
+               <tags>0</tags>
+               <tags_special>1</tags_special>
+       </tx_blogexample_domain_model_person>
 </dataset>
index 560562c..cc46786 100644 (file)
                <sorting>1</sorting>
                <sorting_foreign>1</sorting_foreign>
        </tx_blogexample_domain_model_tag_mm>
-</dataset>
\ No newline at end of file
+       <tx_blogexample_domain_model_tag_mm>
+               <uid_local>4</uid_local>
+               <uid_foreign>13</uid_foreign>
+               <tablenames>tx_blogexample_domain_model_person</tablenames>
+               <fieldname>tags_special</fieldname>
+               <sorting>1</sorting>
+               <sorting_foreign>1</sorting_foreign>
+       </tx_blogexample_domain_model_tag_mm>
+</dataset>