PHP eZ\Publish\Core\Persistence\Database SelectQuery::leftJoin Examples

leftJoin() public method

This method could be used in two forms: leftJoin( 't2', $joinCondition ) Takes 2 string arguments and returns \eZ\Publish\Core\Persistence\Database\SelectQuery. The first parameter is the name of the table to join with. The table to which is joined should have been previously set with the from() method. The second parameter should be a string containing a join condition that is returned by an \eZ\Publish\Core\Persistence\Database\SelectQueryExpression. Example: the following code will produce the SQL SELECT id FROM t1 LEFT JOIN t2 ON t1.id = t2.id $q->select( 'id' )->from( 't1' )->leftJoin( 't2', $q->expr->eq('t1.id', 't2.id' ) ); leftJoin( 't2', 't1.id', 't2.id' ) Takes 3 string arguments and returns \eZ\Publish\Core\Persistence\Database\SelectQuery. This is a simplified form of the 2 parameter version. leftJoin( 't2', 't1.id', 't2.id' ) is equal to leftJoin( 't2', $this->expr->eq('t1.id', 't2.id' ) ); The first parameter is the name of the table to join with. The table to which is joined should have been previously set with the from() method. The second parameter is the name of the column on the table set previously with the from() method and the third parameter the name of the column to join with on the table that was specified in the first parameter. Example: the following code will produce the SQL SELECT id FROM t1 LEFT JOIN t2 ON t1.id = t2.id $q->select( 'id' )->from( 't1' )->leftJoin( 't2', 't1.id', 't2.id' );

public leftJoin ( ) : eZ\Publish\Core\Persistence\Database\SelectQuery
return	eZ\Publish\Core\Persistence\Database\SelectQuery

Example #1

File: Subtree.php Project: brookinsconsulting/ezecosystem

 /**
  * Generate query expression for a Criterion this handler accepts
  *
  * accept() must be called before calling this method.
  *
  * @param \eZ\Publish\Core\Persistence\Legacy\Content\Search\Common\Gateway\CriteriaConverter $converter
  * @param \eZ\Publish\Core\Persistence\Database\SelectQuery $query
  * @param \eZ\Publish\API\Repository\Values\Content\Query\Criterion $criterion
  *
  * @return \eZ\Publish\Core\Persistence\Database\Expression
  */
 public function handle(CriteriaConverter $converter, SelectQuery $query, Criterion $criterion)
 {
     $table = $this->getUniqueTableName();
     $statements = array();
     foreach ($criterion->value as $pattern) {
         $statements[] = $query->expr->like($this->dbHandler->quoteColumn('path_string', $table), $query->bindValue($pattern . '%'));
     }
     $query->leftJoin($query->alias($this->dbHandler->quoteTable('ezcontentobject_tree'), $this->dbHandler->quoteIdentifier($table)), $query->expr->eq($this->dbHandler->quoteColumn('contentobject_id', $table), $this->dbHandler->quoteColumn('id', 'ezcontentobject')));
     return $query->expr->lOr($statements);
 }

Example #2

File: MapLocationDistance.php Project: ezsystems/ezpublish-kernel

 /**
  * Applies joins to the query, required to fetch sort data.
  *
  * @param \eZ\Publish\Core\Persistence\Database\SelectQuery $query
  * @param \eZ\Publish\API\Repository\Values\Content\Query\SortClause $sortClause
  * @param int $number
  * @param array $languageSettings
  */
 public function applyJoin(SelectQuery $query, SortClause $sortClause, $number, array $languageSettings)
 {
     /** @var \eZ\Publish\API\Repository\Values\Content\Query\SortClause\Target\FieldTarget $fieldTarget */
     $fieldTarget = $sortClause->targetData;
     $fieldMap = $this->contentTypeHandler->getSearchableFieldMap();
     if (!isset($fieldMap[$fieldTarget->typeIdentifier][$fieldTarget->fieldIdentifier]['field_definition_id'])) {
         throw new InvalidArgumentException('$sortClause->targetData', 'No searchable fields found for the given sort clause target ' . "'{$fieldTarget->fieldIdentifier}' on '{$fieldTarget->typeIdentifier}'.");
     }
     $fieldDefinitionId = $fieldMap[$fieldTarget->typeIdentifier][$fieldTarget->fieldIdentifier]['field_definition_id'];
     $table = $this->getSortTableName($number);
     $externalTable = $this->getSortTableName($number, 'ezgmaplocation');
     $query->leftJoin($query->alias($this->dbHandler->quoteTable('ezcontentobject_attribute'), $this->dbHandler->quoteIdentifier($table)), $query->expr->lAnd($query->expr->eq($query->bindValue($fieldDefinitionId, null, PDO::PARAM_INT), $this->dbHandler->quoteColumn('contentclassattribute_id', $table)), $query->expr->eq($this->dbHandler->quoteColumn('contentobject_id', $table), $this->dbHandler->quoteColumn('id', 'ezcontentobject')), $query->expr->eq($this->dbHandler->quoteColumn('version', $table), $this->dbHandler->quoteColumn('current_version', 'ezcontentobject')), $this->getFieldCondition($query, $languageSettings, $table)))->leftJoin($query->alias($this->dbHandler->quoteTable('ezgmaplocation'), $this->dbHandler->quoteIdentifier($externalTable)), $query->expr->lAnd($query->expr->eq($this->dbHandler->quoteColumn('contentobject_version', $externalTable), $this->dbHandler->quoteColumn('version', $table)), $query->expr->eq($this->dbHandler->quoteColumn('contentobject_attribute_id', $externalTable), $this->dbHandler->quoteColumn('id', $table))));
 }

Example #3

File: PermissionSubtree.php Project: Heyfara/ezpublish-kernel

 /**
  * Generate query expression for a Criterion this handler accepts.
  *
  * accept() must be called before calling this method.
  *
  * @param \eZ\Publish\Core\Search\Legacy\Content\Common\Gateway\CriteriaConverter $converter
  * @param \eZ\Publish\Core\Persistence\Database\SelectQuery $query
  * @param \eZ\Publish\API\Repository\Values\Content\Query\Criterion $criterion
  * @param array $fieldFilters
  *
  * @return \eZ\Publish\Core\Persistence\Database\Expression
  */
 public function handle(CriteriaConverter $converter, SelectQuery $query, Criterion $criterion, array $fieldFilters)
 {
     $table = 'permission_subtree';
     $statements = array();
     foreach ($criterion->value as $pattern) {
         $statements[] = $query->expr->like($this->dbHandler->quoteColumn('path_string', $table), $query->bindValue($pattern . '%'));
     }
     // Check if ezcontentobject_tree was already joined, if it was there is no need to join
     // with it again - first join will be reused by all other PermissionSubtree criteria
     /** @var $query \eZ\Publish\Core\Persistence\Doctrine\SelectDoctrineQuery */
     if (!$query->permissionSubtreeJoinAdded) {
         $query->leftJoin($query->alias($this->dbHandler->quoteTable('ezcontentobject_tree'), $this->dbHandler->quoteIdentifier($table)), $query->expr->eq($this->dbHandler->quoteColumn('contentobject_id', $table), $this->dbHandler->quoteColumn('id', 'ezcontentobject')));
         // Set joined state to true
         $query->permissionSubtreeJoinAdded = true;
     }
     return $query->expr->lOr($statements);
 }

Example #4

File: Depth.php Project: nlescure/ezpublish-kernel

 /**
  * Generate query expression for a Criterion this handler accepts
  *
  * accept() must be called before calling this method.
  *
  * @param \eZ\Publish\Core\Search\Legacy\Content\Common\Gateway\CriteriaConverter $converter
  * @param \eZ\Publish\Core\Persistence\Database\SelectQuery $query
  * @param \eZ\Publish\API\Repository\Values\Content\Query\Criterion $criterion
  * @param array $fieldFilters
  *
  * @return \eZ\Publish\Core\Persistence\Database\Expression
  */
 public function handle(CriteriaConverter $converter, SelectQuery $query, Criterion $criterion, array $fieldFilters)
 {
     $table = $this->getUniqueTableName();
     $column = $this->dbHandler->quoteColumn('depth', $table);
     $query->leftJoin($query->alias($this->dbHandler->quoteTable('ezcontentobject_tree'), $this->dbHandler->quoteIdentifier($table)), $query->expr->eq($this->dbHandler->quoteColumn('contentobject_id', $table), $this->dbHandler->quoteColumn('id', 'ezcontentobject')));
     switch ($criterion->operator) {
         case Criterion\Operator::IN:
             return $query->expr->in($column, $criterion->value);
         case Criterion\Operator::BETWEEN:
             return $query->expr->between($column, $query->bindValue($criterion->value[0]), $query->bindValue($criterion->value[1]));
         case Criterion\Operator::EQ:
         case Criterion\Operator::GT:
         case Criterion\Operator::GTE:
         case Criterion\Operator::LT:
         case Criterion\Operator::LTE:
             $operatorFunction = $this->comparatorMap[$criterion->operator];
             return $query->expr->{$operatorFunction}($column, $query->bindValue(reset($criterion->value)));
         default:
             throw new RuntimeException("Unknown operator '{$criterion->operator}' for Depth criterion handler.");
     }
 }

Example #5

File: Field.php Project: xcorp1986/ezpublish-kernel

 /**
  * Applies joins to the query, required to fetch sort data.
  *
  * @param \eZ\Publish\Core\Persistence\Database\SelectQuery $query
  * @param \eZ\Publish\API\Repository\Values\Content\Query\SortClause $sortClause
  * @param int $number
  */
 public function applyJoin(SelectQuery $query, SortClause $sortClause, $number)
 {
     /** @var \eZ\Publish\API\Repository\Values\Content\Query\SortClause\Target\FieldTarget $fieldTarget */
     $fieldTarget = $sortClause->targetData;
     $fieldMap = $this->contentTypeHandler->getSearchableFieldMap();
     if (!isset($fieldMap[$fieldTarget->typeIdentifier][$fieldTarget->fieldIdentifier]['field_definition_id'])) {
         throw new InvalidArgumentException('$sortClause->targetData', 'No searchable fields found for the given sort clause target ' . "'{$fieldTarget->fieldIdentifier}' on '{$fieldTarget->typeIdentifier}'.");
     }
     $fieldDefinitionId = $fieldMap[$fieldTarget->typeIdentifier][$fieldTarget->fieldIdentifier]['field_definition_id'];
     $table = $this->getSortTableName($number);
     if ($fieldTarget->languageCode === null) {
         $languageExpression = $query->expr->gt($query->expr->bitAnd($query->expr->bitAnd($this->dbHandler->quoteColumn('language_id', $table), ~1), $this->dbHandler->quoteColumn('initial_language_id', 'ezcontentobject')), 0);
     } else {
         $languageExpression = $query->expr->gt($query->expr->bitAnd($query->expr->bitAnd($this->dbHandler->quoteColumn('language_id', $table), ~1), $query->bindValue($this->languageHandler->loadByLanguageCode($fieldTarget->languageCode)->id, null, \PDO::PARAM_INT)), 0);
     }
     $query->leftJoin($query->alias($this->dbHandler->quoteTable('ezcontentobject_attribute'), $this->dbHandler->quoteIdentifier($table)), $query->expr->lAnd($query->expr->eq($query->bindValue($fieldDefinitionId, null, PDO::PARAM_INT), $this->dbHandler->quoteColumn('contentclassattribute_id', $table)), $query->expr->eq($this->dbHandler->quoteColumn('contentobject_id', $table), $this->dbHandler->quoteColumn('id', 'ezcontentobject')), $query->expr->eq($this->dbHandler->quoteColumn('version', $table), $this->dbHandler->quoteColumn('current_version', 'ezcontentobject')), $languageExpression));
 }

Example #6

File: MapLocationDistance.php Project: Jenkosama/ezpublish-kernel

 /**
  * Applies joins to the query, required to fetch sort data
  *
  * @param \eZ\Publish\Core\Persistence\Database\SelectQuery $query
  * @param \eZ\Publish\API\Repository\Values\Content\Query\SortClause $sortClause
  * @param int $number
  * @param array $fieldMap
  *
  * @return void
  */
 public function applyJoin(SelectQuery $query, SortClause $sortClause, $number, array $fieldMap)
 {
     /** @var \eZ\Publish\API\Repository\Values\Content\Query\SortClause\Target\FieldTarget $fieldTarget */
     $fieldTarget = $sortClause->targetData;
     $fieldDefinitionId = $fieldMap[$fieldTarget->typeIdentifier][$fieldTarget->fieldIdentifier];
     $table = $this->getSortTableName($number);
     $externalTable = $this->getSortTableName($number, "ezgmaplocation");
     if ($fieldTarget->languageCode === null) {
         $languageExpression = $query->expr->gt($query->expr->bitAnd($query->expr->bitAnd($this->dbHandler->quoteColumn("language_id", $table), ~1), $this->dbHandler->quoteColumn("initial_language_id", "ezcontentobject")), 0);
     } else {
         $languageExpression = $query->expr->gt($query->expr->bitAnd($query->expr->bitAnd($this->dbHandler->quoteColumn("language_id", $table), ~1), $query->bindValue($this->languageHandler->loadByLanguageCode($fieldTarget->languageCode)->id, null, PDO::PARAM_INT)), 0);
     }
     $query->leftJoin($query->alias($this->dbHandler->quoteTable("ezcontentobject_attribute"), $this->dbHandler->quoteIdentifier($table)), $query->expr->lAnd($query->expr->eq($query->bindValue($fieldDefinitionId, null, PDO::PARAM_INT), $this->dbHandler->quoteColumn("contentclassattribute_id", $table)), $query->expr->eq($this->dbHandler->quoteColumn("contentobject_id", $table), $this->dbHandler->quoteColumn("id", "ezcontentobject")), $query->expr->eq($this->dbHandler->quoteColumn("version", $table), $this->dbHandler->quoteColumn("current_version", "ezcontentobject")), $languageExpression))->leftJoin($query->alias($this->dbHandler->quoteTable("ezgmaplocation"), $this->dbHandler->quoteIdentifier($externalTable)), $query->expr->lAnd($query->expr->eq($this->dbHandler->quoteColumn("contentobject_version", $externalTable), $this->dbHandler->quoteColumn("version", $table)), $query->expr->eq($this->dbHandler->quoteColumn("contentobject_attribute_id", $externalTable), $this->dbHandler->quoteColumn("id", $table))));
 }

Example #7

File: SectionName.php Project: Jenkosama/ezpublish-kernel

 /**
  * Applies joins to the query, required to fetch sort data
  *
  * @param \eZ\Publish\Core\Persistence\Database\SelectQuery $query
  * @param \eZ\Publish\API\Repository\Values\Content\Query\SortClause $sortClause
  * @param int $number
  * @param array $fieldMap
  *
  * @return void
  */
 public function applyJoin(SelectQuery $query, SortClause $sortClause, $number, array $fieldMap)
 {
     $table = $this->getSortTableName($number);
     $query->leftJoin($query->alias($this->dbHandler->quoteTable('ezsection'), $this->dbHandler->quoteIdentifier($table)), $query->expr->eq($this->dbHandler->quoteColumn('id', $table), $this->dbHandler->quoteColumn('section_id', 'ezcontentobject')));
 }