Merge pull request propelorm#1705 from mringler/feature/add_exists

Feature: Add exists operator
FXdSP · May 6, 2021 · 23ab61d · 23ab61d
2 parents 1e32784 + 5b05222
commit 23ab61d
Show file tree

Hide file tree

Showing 10 changed files with 749 additions and 23 deletions.
diff --git a/phpstan-baseline.neon b/phpstan-baseline.neon
diff --git a/src/Propel/Generator/Builder/Om/QueryBuilder.php b/src/Propel/Generator/Builder/Om/QueryBuilder.php
@@ -13,6 +13,7 @@
 use Propel\Generator\Model\ForeignKey;
 use Propel\Generator\Model\PropelTypes;
 use Propel\Generator\Model\Table;
+use Propel\Runtime\ActiveQuery\Criterion\ExistsCriterion;
 
 /**
  * Generates a base Query class for user object model (OM).
@@ -1550,6 +1551,7 @@ protected function addUseFkQuery(&$script, $fk)
 
         $this->addUseRelatedQuery($script, $fkTable, $queryClass, $relationName, $joinType);
         $this->addWithRelatedQuery($script, $fkTable, $queryClass, $relationName, $joinType);
+        $this->addUseRelatedExistsQuery($script, $fkTable, $queryClass, $relationName);
     }
 
     /**
@@ -1570,6 +1572,7 @@ protected function addUseRefFkQuery(&$script, ForeignKey $fk)
 
         $this->addUseRelatedQuery($script, $fkTable, $queryClass, $relationName, $joinType);
         $this->addWithRelatedQuery($script, $fkTable, $queryClass, $relationName, $joinType);
+        $this->addUseRelatedExistsQuery($script, $fkTable, $queryClass, $relationName);
     }
 
     /**
@@ -1606,6 +1609,59 @@ public function use" . $relationName . 'Query($relationAlias = null, $joinType =
 ";
     }
 
+    /**
+     * Adds a useExistsQuery and useNotExistsQuery to the object script.
+     *
+     * @param string $script The script will be modified in this method.
+     * @param \Propel\Generator\Model\Table $fkTable The target of the relation
+     * @param string $queryClass Query object class name that will be returned by the exists statement.
+     * @param string $relationName Name of the relation
+     *
+     * @return void
+     */
+    protected function addUseRelatedExistsQuery(&$script, Table $fkTable, $queryClass, $relationName)
+    {
+        $relationDescription = ($relationName === $fkTable->getPhpName()) ?
+            "relation to $relationName table" :
+            "$relationName relation to the {$fkTable->getPhpName()} table";
+
+        $notExistsType = ExistsCriterion::TYPE_NOT_EXISTS;
+        $existsType = ExistsCriterion::TYPE_EXISTS;
+
+        $script .= <<< EOT
+    /**
+     * Use the $relationDescription for an EXISTS query.
+     *
+     * @see \Propel\Runtime\ActiveQuery\ModelCriteria::useExistsQuery()
+     *
+     * @param string|null \$queryClass Allows to use a custom query class for the exists query, like ExtendedBookQuery::class
+     * @param string|null \$modelAlias sets an alias for the nested query
+     * @param string \$typeOfExists Either ExistsCriterion::TYPE_EXISTS or ExistsCriterion::TYPE_NOT_EXISTS
+     *
+     * @return $queryClass The inner query object of the EXISTS statement
+     */
+    public function use{$relationName}ExistsQuery(\$modelAlias = null, \$queryClass = null, \$typeOfExists = '$existsType')
+    {
+        return \$this->useExistsQuery('$relationName', \$modelAlias, \$queryClass, \$typeOfExists);
+    }
+
+    /**
+     * Use the $relationDescription for a NOT EXISTS query.
+     *
+     * @see use{$relationName}ExistsQuery()
+     *
+     * @param string|null \$modelAlias sets an alias for the nested query
+     * @param string|null \$queryClass Allows to use a custom query class for the exists query, like ExtendedBookQuery::class
+     *
+     * @return $queryClass The inner query object of the NOT EXISTS statement
+     */
+    public function use{$relationName}NotExistsQuery(\$modelAlias = null, \$queryClass = null)
+    {
+        return \$this->useExistsQuery('$relationName', \$modelAlias, \$queryClass, '$notExistsType');
+    }
+EOT;
+    }
+
     /**
      * Adds a withRelatedQuery method for this object.
      *

diff --git a/src/Propel/Runtime/ActiveQuery/BaseModelCriteria.php b/src/Propel/Runtime/ActiveQuery/BaseModelCriteria.php
@@ -28,6 +28,11 @@ class BaseModelCriteria extends Criteria implements IteratorAggregate
      */
     protected $modelTableMapName;
 
+    /**
+     * @var bool
+     */
+    protected $useAliasInSQL = false;
+
     /**
      * @var string|null
      */
@@ -260,6 +265,22 @@ public function getTableMap()
         return $this->tableMap;
     }
 
+    /**
+     * Returns the name of the table as used in the query.
+     *
+     * Either the SQL name or an alias.
+     *
+     * @return string
+     */
+    public function getTableNameInQuery()
+    {
+        if ($this->useAliasInSQL && $this->modelAlias) {
+            return $this->modelAlias;
+        }
+
+        return $this->getTableMap()->getName();
+    }
+
     /**
      * Execute the query with a find(), and return a Traversable object.
      *

diff --git a/src/Propel/Runtime/ActiveQuery/Criterion/ExistsCriterion.php b/src/Propel/Runtime/ActiveQuery/Criterion/ExistsCriterion.php
@@ -0,0 +1,95 @@
+<?php
+
+/**
+ * MIT License. This file is part of the Propel package.
+ * For the full copyright and license information, please view the LICENSE
+ * file that was distributed with this source code.
+ */
+
+namespace Propel\Runtime\ActiveQuery\Criterion;
+
+use Propel\Runtime\ActiveQuery\ModelJoin;
+use Propel\Runtime\Map\RelationMap;
+
+/**
+ * Specialized Criterion used for EXISTS
+ */
+class ExistsCriterion extends AbstractCriterion
+{
+    public const TYPE_EXISTS = 'EXISTS';
+
+    public const TYPE_NOT_EXISTS = 'NOT EXISTS';
+
+    /**
+     * The inner query of the exists
+     *
+     * @var \Propel\Runtime\ActiveQuery\ModelCriteria
+     */
+    private $existsQuery;
+
+    /**
+     * Build NOT EXISTS instead of EXISTS
+     *
+     * @var string $keyword Either ExistsCriterion::TYPE_EXISTS or ExistsCriterion::TYPE_NOT_EXISTS
+     *
+     * @phpstan-var ExistsCriterion::TYPE_*
+     */
+    private $typeOfExists = self::TYPE_EXISTS;
+
+    /**
+     * @phpstan-param ExistsCriterion::TYPE_*|null $typeOfExists
+     *
+     * @param \Propel\Runtime\ActiveQuery\ModelCriteria $outerQuery
+     * @param \Propel\Runtime\ActiveQuery\ModelCriteria $existsQuery
+     * @param string|null $typeOfExists Either ExistsCriterion::TYPE_EXISTS or ExistsCriterion::TYPE_NOT_EXISTS
+     * @param \Propel\Runtime\Map\RelationMap|null $relationMap where outer query is on the left side
+     */
+    public function __construct($outerQuery, $existsQuery, ?string $typeOfExists = null, ?RelationMap $relationMap = null)
+    {
+        parent::__construct($outerQuery, '', null, null);
+        $this->existsQuery = $existsQuery;
+        $this->typeOfExists = ($typeOfExists === self::TYPE_NOT_EXISTS) ? self::TYPE_NOT_EXISTS : self::TYPE_EXISTS;
+
+        if ($relationMap !== null) {
+            $joinCondition = $this->buildJoinCondition($outerQuery, $relationMap);
+            $this->existsQuery->addAnd($joinCondition);
+        }
+    }
+
+    /**
+     * @see \Propel\Runtime\ActiveQuery\Criterion\AbstractCriterion::appendPsForUniqueClauseTo()
+     *
+     * @param string $sb The string that will receive the Prepared Statement
+     * @param array $params A list to which Prepared Statement parameters will be appended
+     *
+     * @return void
+     */
+    protected function appendPsForUniqueClauseTo(&$sb, array &$params)
+    {
+        $existsQuery = $this->existsQuery
+            ->clearSelectColumns()
+            ->addAsColumn('existsFlag', '1')
+            ->createSelectSql($params);
+        $sb .= $this->typeOfExists . ' (' . $existsQuery . ')';
+    }
+
+    /**
+     * @param \Propel\Runtime\ActiveQuery\ModelCriteria $outerQuery
+     * @param \Propel\Runtime\Map\RelationMap $relationMap where outer query is on the left side
+     *
+     * @return \Propel\Runtime\ActiveQuery\Criterion\AbstractCriterion
+     */
+    protected function buildJoinCondition($outerQuery, RelationMap $relationMap)
+    {
+        $join = new ModelJoin();
+        $outerAlias = $outerQuery->getModelAlias();
+        $innerAlias = $this->existsQuery->getModelAlias();
+        $join->setRelationMap($relationMap, $outerAlias, $innerAlias);
+        $join->buildJoinCondition($outerQuery);
+
+        $joinCondition = $join->getJoinCondition();
+        $joinCondition->setTable($this->existsQuery->getTableNameInQuery());
+
+        return $joinCondition;
+    }
+}
diff --git a/src/Propel/Runtime/ActiveQuery/ModelCriteria.php b/src/Propel/Runtime/ActiveQuery/ModelCriteria.php
@@ -16,6 +16,7 @@
 use Propel\Runtime\ActiveQuery\Criterion\BasicModelCriterion;
 use Propel\Runtime\ActiveQuery\Criterion\BinaryModelCriterion;
 use Propel\Runtime\ActiveQuery\Criterion\CustomCriterion;
+use Propel\Runtime\ActiveQuery\Criterion\ExistsCriterion;
 use Propel\Runtime\ActiveQuery\Criterion\InModelCriterion;
 use Propel\Runtime\ActiveQuery\Criterion\LikeModelCriterion;
 use Propel\Runtime\ActiveQuery\Criterion\RawCriterion;
@@ -57,11 +58,6 @@ class ModelCriteria extends BaseModelCriteria
     public const FORMAT_OBJECT = '\Propel\Runtime\Formatter\ObjectFormatter';
     public const FORMAT_ON_DEMAND = '\Propel\Runtime\Formatter\OnDemandFormatter';
 
-    /**
-     * @var bool
-     */
-    protected $useAliasInSQL = false;
-
     /**
      * @var \Propel\Runtime\ActiveQuery\ModelCriteria|null
      */
@@ -99,6 +95,13 @@ class ModelCriteria extends BaseModelCriteria
      */
     protected $isSelfSelected = false;
 
+    /**
+     * Indicates that this query is wrapped in an EXISTS-statement
+     *
+     * @var bool
+     */
+    protected $isExistsQuery = false;
+
     /**
      * Adds a condition on a column based on a pseudo SQL clause
      * but keeps it for later use with combine()
@@ -209,6 +212,44 @@ public function where($clause, $value = null, $bindingType = null)
         return $this;
     }
 
+    /**
+     * Adds an EXISTS clause with a custom query object.
+     *
+     * Note that filter conditions linking data from the outer query with data from the inner
+     * query are not inferred and have to be added manually. If a relationship exists between
+     * outer and inner table, {@link ModelCriteria::useExistsQuery()} can be used to infer filter
+     * automatically..
+     *
+     * @example MyOuterQuery::create()->whereExists(MyDataQuery::create()->where('MyData.MyField = MyOuter.MyField'))
+     *
+     * @phpstan-param ExistsCriterion::TYPE_* $type
+     *
+     * @see ModelCriteria::useExistsQuery() can be used
+     *
+     * @param \Propel\Runtime\ActiveQuery\ModelCriteria $existsQueryCriteria the query object used in the EXISTS statement
+     * @param string $type Either ExistsCriterion::TYPE_EXISTS or ExistsCriterion::TYPE_NOT_EXISTS. Defaults to EXISTS
+     *
+     * @return \Propel\Runtime\ActiveQuery\ModelCriteria*
+     */
+    public function whereExists($existsQueryCriteria, string $type = ExistsCriterion::TYPE_EXISTS)
+    {
+        $criterion = new ExistsCriterion($this, $existsQueryCriteria, $type);
+
+        return $this->addUsingOperator($criterion);
+    }
+
+    /**
+     * Negation of {@link ModelCriteria::whereExists()}
+     *
+     * @param \Propel\Runtime\ActiveQuery\ModelCriteria $existsQueryCriteria
+     *
+     * @return \Propel\Runtime\ActiveQuery\ModelCriteria
+     */
+    public function whereNotExists($existsQueryCriteria)
+    {
+        return $this->whereExists($existsQueryCriteria, ExistsCriterion::TYPE_NOT_EXISTS);
+    }
+
     /**
      * Adds a having condition on a column based on a pseudo SQL clause
      * Uses introspection to translate the column phpName into a fully qualified name
@@ -807,6 +848,10 @@ public function useQuery($relationName, $secondaryCriteriaClass = null)
      */
     public function endUse()
     {
+        if ($this->isExistsQuery) {
+            return $this->getPrimaryCriteria();
+        }
+
         if (isset($this->aliases[$this->modelAlias])) {
             $this->removeAlias($this->modelAlias);
         }
@@ -817,6 +862,52 @@ public function endUse()
         return $primaryCriteria;
     }
 
+    /**
+     * Adds and returns an internal query to be used in an EXISTS-clause.
+     *
+     * @phpstan-param ExistsCriterion::TYPE_* $type
+     *
+     * @param string $relationName name of the relation
+     * @param string|null $modelAlias sets an alias for the nested query
+     * @param string|null $queryClass allows to use a custom query class for the exists query, like ExtendedBookQuery::class
+     * @param string $type Either ExistsCriterion::TYPE_EXISTS or ExistsCriterion::TYPE_NOT_EXISTS. Defaults to EXISTS
+     *
+     * @return \Propel\Runtime\ActiveQuery\ModelCriteria
+     */
+    public function useExistsQuery(string $relationName, ?string $modelAlias = null, ?string $queryClass = null, string $type = ExistsCriterion::TYPE_EXISTS)
+    {
+        $relationMap = $this->getTableMap()->getRelation($relationName);
+        $className = $relationMap->getRightTable()->getClassName();
+
+        $queryInExists = ($queryClass === null) ? PropelQuery::from($className) : new $queryClass();
+        $queryInExists->isExistsQuery = true;
+        $queryInExists->primaryCriteria = $this;
+        if ($modelAlias !== null) {
+            $queryInExists->setModelAlias($modelAlias, true);
+        }
+
+        $criterion = new ExistsCriterion($this, $queryInExists, $type, $relationMap);
+        $this->addUsingOperator($criterion);
+
+        return $queryInExists;
+    }
+
+    /**
+     * Use NOT EXISTS rather than EXISTS.
+     *
+     * @see ModelCriteria::useExistsQuery()
+     *
+     * @param string $relationName
+     * @param string|null $modelAlias sets an alias for the nested query
+     * @param string|null $queryClass allows to use a custom query class for the exists query, like ExtendedBookQuery::class
+     *
+     * @return \Propel\Runtime\ActiveQuery\ModelCriteria
+     */
+    public function useNotExistsQuery(string $relationName, ?string $modelAlias = null, ?string $queryClass = null)
+    {
+        return $this->useExistsQuery($relationName, $modelAlias, $queryClass, ExistsCriterion::TYPE_NOT_EXISTS);
+    }
+
     /**
      * Add the content of a Criteria to the current Criteria
      * In case of conflict, the current Criteria keeps its properties
@@ -2222,15 +2313,10 @@ protected function getRealColumnName($columnName)
         if (!$this->getTableMap()->hasColumnByPhpName($columnName)) {
             throw new UnknownColumnException('Unknown column ' . $columnName . ' in model ' . $this->modelName);
         }
+        $tableName = $this->getTableNameInQuery();
+        $columnName = $this->getTableMap()->getColumnByPhpName($columnName)->getName();
 
-        if ($this->useAliasInSQL) {
-            return $this->modelAlias . '.' . $this->getTableMap()->getColumnByPhpName($columnName)->getName();
-        }
-
-        return $this
-            ->getTableMap()
-            ->getColumnByPhpName($columnName)
-            ->getFullyQualifiedName();
+        return "$tableName.$columnName";
     }
 
     /**