[TASK] Doctrine: Add support for aggregate SQL functions 26/47826/2
authorMorton Jonuschat <m.jonuschat@mojocode.de>
Wed, 20 Apr 2016 17:59:47 +0000 (19:59 +0200)
committerFrank Naegler <frank.naegler@typo3.org>
Thu, 5 May 2016 20:59:46 +0000 (22:59 +0200)
This patch extends the SQL Expressionbuilder with support for generating
expressions for MIN, MAX, AVG, SUM and COUNT aggregate functions, as
well as adding support for selecting literal (unquoted) SQL expressions
with the QueryBuilder using ->selectLiteral() and ->addSelectLiteral().

Releases: master
Resolves: #75822
Change-Id: Id1043ec8a60be1e6d3c273278a776e92bb8c973e
Reviewed-on: https://review.typo3.org/47826
Reviewed-by: Wouter Wolters <typo3@wouterwolters.nl>
Tested-by: Wouter Wolters <typo3@wouterwolters.nl>
Reviewed-by: Frank Naegler <frank.naegler@typo3.org>
Tested-by: Frank Naegler <frank.naegler@typo3.org>
typo3/sysext/core/Classes/Database/Query/Expression/ExpressionBuilder.php
typo3/sysext/core/Classes/Database/Query/QueryBuilder.php
typo3/sysext/core/Tests/Unit/Database/Query/Expression/ExpressionBuilderTest.php
typo3/sysext/core/Tests/Unit/Database/Query/QueryBuilderTest.php

index 3246950..79f1082 100644 (file)
@@ -353,6 +353,89 @@ class ExpressionBuilder
     }
 
     /**
+     * Creates a MIN expression for the given field/alias.
+     *
+     * @param string $fieldName
+     * @param string|null $alias
+     * @return string
+     */
+    public function min(string $fieldName, string $alias = null): string
+    {
+        return $this->calculation('MIN', $fieldName, $alias);
+    }
+
+    /**
+     * Creates a MAX expression for the given field/alias.
+     *
+     * @param string $fieldName
+     * @param string|null $alias
+     * @return string
+     */
+    public function max(string $fieldName, string $alias = null): string
+    {
+        return $this->calculation('MAX', $fieldName, $alias);
+    }
+
+    /**
+     * Creates a AVG expression for the given field/alias.
+     *
+     * @param string $fieldName
+     * @param string|null $alias
+     * @return string
+     */
+    public function avg(string $fieldName, string $alias = null): string
+    {
+        return $this->calculation('AVG', $fieldName, $alias);
+    }
+
+    /**
+     * Creates a SUM expression for the given field/alias.
+     *
+     * @param string $fieldName
+     * @param string|null $alias
+     * @return string
+     */
+    public function sum(string $fieldName, string $alias = null): string
+    {
+        return $this->calculation('SUM', $fieldName, $alias);
+    }
+
+    /**
+     * Creates a COUNT expression for the given field/alias.
+     *
+     * @param string $fieldName
+     * @param string|null $alias
+     * @return string
+     */
+    public function count(string $fieldName, string $alias = null): string
+    {
+        return $this->calculation('COUNT', $fieldName, $alias);
+    }
+
+    /**
+     * Create a SQL aggregrate function.
+     *
+     * @param string $aggregateName
+     * @param string $fieldName
+     * @param string|null $alias
+     * @return string
+     */
+    protected function calculation(string $aggregateName, string $fieldName, string $alias = null): string
+    {
+        $aggregateSQL = sprintf(
+            '%s(%s)',
+            $aggregateName,
+            $this->connection->quoteIdentifier($fieldName)
+        );
+
+        if (!empty($alias)) {
+            $aggregateSQL .= ' AS ' . $this->connection->quoteIdentifier($alias);
+        }
+
+        return $aggregateSQL;
+    }
+
+    /**
      * Quotes a given input parameter.
      *
      * @param mixed $input The parameter to be quoted.
index 576ec2c..1530b21 100644 (file)
@@ -370,7 +370,6 @@ class QueryBuilder
      * Specifies items that are to be returned in the query result.
      * Replaces any previously specified selections, if any.
      *
-     *
      * @param string[] $selects
      * @return QueryBuilder This QueryBuilder instance.
      */
@@ -396,6 +395,37 @@ class QueryBuilder
     }
 
     /**
+     * Specifies items that are to be returned in the query result.
+     * Replaces any previously specified selections, if any.
+     * This should only be used for literal SQL expressions as no
+     * quoting/escaping of any kind will be performed on the items.
+     *
+     * @param string[] $selects Literal SQL expressions to be selected. Warning: No quoting will be done!
+     * @return QueryBuilder This QueryBuilder instance.
+     */
+    public function selectLiteral(string ...$selects): QueryBuilder
+    {
+        $this->concreteQueryBuilder->select(...$selects);
+
+        return $this;
+    }
+
+    /**
+     * Adds an item that is to be returned in the query result. This should
+     * only be used for literal SQL expressions as no quoting/escaping of
+     * any kind will be performed on the items.
+     *
+     * @param string[] $selects Literal SQL expressions to be selected.
+     * @return QueryBuilder This QueryBuilder instance.
+     */
+    public function addSelectLiteral(string ...$selects): QueryBuilder
+    {
+        $this->concreteQueryBuilder->addSelect(...$selects);
+
+        return $this;
+    }
+
+    /**
      * Turns the query being built into a bulk delete query that ranges over
      * a certain table.
      *
@@ -664,7 +694,6 @@ class QueryBuilder
      */
     public function setValue(string $column, $value, bool $createNamedParameter = true): QueryBuilder
     {
-
         $this->concreteQueryBuilder->setValue(
             $this->quoteIdentifier($column),
             $createNamedParameter ? $this->createNamedParameter($value) : $value
index 1224f98..df22b2e 100644 (file)
@@ -307,6 +307,91 @@ class ExpressionBuilderTest extends UnitTestCase
     /**
      * @test
      */
+    public function maxQuotesIdentifier()
+    {
+        $this->connectionProphet->quoteIdentifier(Argument::cetera())->will(function ($args) {
+            $platform = new MockPlatform();
+            return $platform->quoteIdentifier($args[0]);
+        });
+
+        $this->assertSame('MAX("tableName"."fieldName")', $this->subject->max('tableName.fieldName'));
+        $this->assertSame(
+            'MAX("tableName"."fieldName") AS "anAlias"',
+            $this->subject->max('tableName.fieldName', 'anAlias')
+        );
+    }
+
+    /**
+     * @test
+     */
+    public function minQuotesIdentifier()
+    {
+        $this->connectionProphet->quoteIdentifier(Argument::cetera())->will(function ($args) {
+            $platform = new MockPlatform();
+            return $platform->quoteIdentifier($args[0]);
+        });
+
+        $this->assertSame('MIN("tableName"."fieldName")', $this->subject->min('tableName.fieldName'));
+        $this->assertSame(
+            'MIN("tableName"."fieldName") AS "anAlias"',
+            $this->subject->min('tableName.fieldName', 'anAlias')
+        );
+    }
+
+    /**
+     * @test
+     */
+    public function sumQuotesIdentifier()
+    {
+        $this->connectionProphet->quoteIdentifier(Argument::cetera())->will(function ($args) {
+            $platform = new MockPlatform();
+            return $platform->quoteIdentifier($args[0]);
+        });
+
+        $this->assertSame('SUM("tableName"."fieldName")', $this->subject->sum('tableName.fieldName'));
+        $this->assertSame(
+            'SUM("tableName"."fieldName") AS "anAlias"',
+            $this->subject->sum('tableName.fieldName', 'anAlias')
+        );
+    }
+
+    /**
+     * @test
+     */
+    public function avgQuotesIdentifier()
+    {
+        $this->connectionProphet->quoteIdentifier(Argument::cetera())->will(function ($args) {
+            $platform = new MockPlatform();
+            return $platform->quoteIdentifier($args[0]);
+        });
+
+        $this->assertSame('AVG("tableName"."fieldName")', $this->subject->avg('tableName.fieldName'));
+        $this->assertSame(
+            'AVG("tableName"."fieldName") AS "anAlias"',
+            $this->subject->avg('tableName.fieldName', 'anAlias')
+        );
+    }
+
+    /**
+     * @test
+     */
+    public function countQuotesIdentifier()
+    {
+        $this->connectionProphet->quoteIdentifier(Argument::cetera())->will(function ($args) {
+            $platform = new MockPlatform();
+            return $platform->quoteIdentifier($args[0]);
+        });
+
+        $this->assertSame('COUNT("tableName"."fieldName")', $this->subject->count('tableName.fieldName'));
+        $this->assertSame(
+            'COUNT("tableName"."fieldName") AS "anAlias"',
+            $this->subject->count('tableName.fieldName', 'anAlias')
+        );
+    }
+
+    /**
+     * @test
+     */
     public function literalQuotesValue()
     {
         $this->connectionProphet->quote('aField', 'Doctrine\DBAL\Types\StringType')
index 03c8e4a..ec91ddb 100644 (file)
@@ -402,6 +402,34 @@ class QueryBuilderTest extends UnitTestCase
 
     /**
      * @test
+     */
+    public function selectLiteralDirectlyDelegatesToConcreteQueryBuilder()
+    {
+        $this->connection->quoteIdentifier(Argument::cetera())
+            ->shouldNotBeCalled();
+        $this->concreteQueryBuilder->select(Argument::exact('MAX(aField) AS anAlias'))
+            ->shouldBeCalled()
+            ->willReturn($this->subject);
+
+        $this->subject->selectLiteral('MAX(aField) AS anAlias');
+    }
+
+    /**
+     * @test
+     */
+    public function addSelectLiteralDirectlyDelegatesToConcreteQueryBuilder()
+    {
+        $this->connection->quoteIdentifier(Argument::cetera())
+            ->shouldNotBeCalled();
+        $this->concreteQueryBuilder->addSelect(Argument::exact('MAX(aField) AS anAlias'))
+            ->shouldBeCalled()
+            ->willReturn($this->subject);
+
+        $this->subject->addSelectLiteral('MAX(aField) AS anAlias');
+    }
+
+    /**
+     * @test
      * @todo: Test with alias
      */
     public function deleteQuotesIdentifierAndDelegatesToConcreteQueryBuilder()