PHP DatabaseHandler::aliasedColumn方法代码示例

 /**
  * Creates a select query for content version objects, used for version loading w/o fields.
  *
  * Creates a select query with all necessary joins to fetch a complete
  * content object. Does not apply any WHERE conditions, and does not contain
  * name data as it will lead to large result set {@see createNamesQuery}.
  *
  * @return \eZ\Publish\Core\Persistence\Database\SelectQuery
  */
 public function createVersionInfoFindQuery()
 {
     /** @var $query \eZ\Publish\Core\Persistence\Database\SelectQuery */
     $query = $this->dbHandler->createSelectQuery();
     $query->select($this->dbHandler->aliasedColumn($query, 'id', 'ezcontentobject_version'), $this->dbHandler->aliasedColumn($query, 'version', 'ezcontentobject_version'), $this->dbHandler->aliasedColumn($query, 'modified', 'ezcontentobject_version'), $this->dbHandler->aliasedColumn($query, 'creator_id', 'ezcontentobject_version'), $this->dbHandler->aliasedColumn($query, 'created', 'ezcontentobject_version'), $this->dbHandler->aliasedColumn($query, 'status', 'ezcontentobject_version'), $this->dbHandler->aliasedColumn($query, 'contentobject_id', 'ezcontentobject_version'), $this->dbHandler->aliasedColumn($query, 'initial_language_id', 'ezcontentobject_version'), $this->dbHandler->aliasedColumn($query, 'language_mask', 'ezcontentobject_version'), $this->dbHandler->aliasedColumn($query, 'main_node_id', 'ezcontentobject_tree'), $this->dbHandler->aliasedColumn($query, 'id', 'ezcontentobject'), $this->dbHandler->aliasedColumn($query, 'contentclass_id', 'ezcontentobject'), $this->dbHandler->aliasedColumn($query, 'section_id', 'ezcontentobject'), $this->dbHandler->aliasedColumn($query, 'owner_id', 'ezcontentobject'), $this->dbHandler->aliasedColumn($query, 'remote_id', 'ezcontentobject'), $this->dbHandler->aliasedColumn($query, 'current_version', 'ezcontentobject'), $this->dbHandler->aliasedColumn($query, 'initial_language_id', 'ezcontentobject'), $this->dbHandler->aliasedColumn($query, 'modified', 'ezcontentobject'), $this->dbHandler->aliasedColumn($query, 'published', 'ezcontentobject'), $this->dbHandler->aliasedColumn($query, 'status', 'ezcontentobject'), $this->dbHandler->aliasedColumn($query, 'name', 'ezcontentobject'), $this->dbHandler->aliasedColumn($query, 'language_mask', 'ezcontentobject'))->from($this->dbHandler->quoteTable('ezcontentobject_version'))->innerJoin($this->dbHandler->quoteTable('ezcontentobject'), $query->expr->eq($this->dbHandler->quoteColumn('id', 'ezcontentobject'), $this->dbHandler->quoteColumn('contentobject_id', 'ezcontentobject_version')))->leftJoin($this->dbHandler->quoteTable('ezcontentobject_tree'), $query->expr->lAnd($query->expr->eq($this->dbHandler->quoteColumn('contentobject_id', 'ezcontentobject_tree'), $this->dbHandler->quoteColumn('contentobject_id', 'ezcontentobject_version')), $query->expr->eq($this->dbHandler->quoteColumn('main_node_id', 'ezcontentobject_tree'), $this->dbHandler->quoteColumn('node_id', 'ezcontentobject_tree'))));
     return $query;
 }

 /**
  * Remove all limitations for a policy.
  *
  * @param mixed $policyId
  */
 public function removePolicyLimitations($policyId)
 {
     $query = $this->handler->createSelectQuery();
     $query->select($this->handler->aliasedColumn($query, 'id', 'ezpolicy_limitation'), $this->handler->aliasedColumn($query, 'id', 'ezpolicy_limitation_value'))->from($this->handler->quoteTable('ezpolicy'))->leftJoin($this->handler->quoteTable('ezpolicy_limitation'), $query->expr->eq($this->handler->quoteColumn('policy_id', 'ezpolicy_limitation'), $this->handler->quoteColumn('id', 'ezpolicy')))->leftJoin($this->handler->quoteTable('ezpolicy_limitation_value'), $query->expr->eq($this->handler->quoteColumn('limitation_id', 'ezpolicy_limitation_value'), $this->handler->quoteColumn('id', 'ezpolicy_limitation')))->where($query->expr->eq($this->handler->quoteColumn('id', 'ezpolicy'), $query->bindValue($policyId, null, \PDO::PARAM_INT)));
     $statement = $query->prepare();
     $statement->execute();
     $limitationIdsSet = array();
     $limitationValuesSet = array();
     while ($row = $statement->fetch(\PDO::FETCH_ASSOC)) {
         if ($row['ezpolicy_limitation_id'] !== null) {
             $limitationIdsSet[$row['ezpolicy_limitation_id']] = true;
         }
         if ($row['ezpolicy_limitation_value_id'] !== null) {
             $limitationValuesSet[$row['ezpolicy_limitation_value_id']] = true;
         }
     }
     if (!empty($limitationIdsSet)) {
         $query = $this->handler->createDeleteQuery();
         $query->deleteFrom($this->handler->quoteTable('ezpolicy_limitation'))->where($query->expr->in($this->handler->quoteColumn('id'), array_keys($limitationIdsSet)));
         $query->prepare()->execute();
     }
     if (!empty($limitationValuesSet)) {
         $query = $this->handler->createDeleteQuery();
         $query->deleteFrom($this->handler->quoteTable('ezpolicy_limitation_value'))->where($query->expr->in($this->handler->quoteColumn('id'), array_keys($limitationValuesSet)));
         $query->prepare()->execute();
     }
 }

    /**
     * Get sorted arrays of content IDs, which should be returned
     *
     * @param Criterion $filter
     * @param array $sort
     * @param mixed $offset
     * @param mixed $limit
     * @param mixed $translations
     *
     * @return int[]
     */
    protected function getContentInfoList( Criterion $filter, $sort, $offset, $limit, $translations )
    {
        $query = $this->handler->createSelectQuery();
        $query->selectDistinct(
            'ezcontentobject.*',
            $this->handler->aliasedColumn( $query, 'main_node_id', 'main_tree' )
        );

        if ( $sort !== null )
        {
            $this->sortClauseConverter->applySelect( $query, $sort );
        }

        $query->from(
            $this->handler->quoteTable( 'ezcontentobject' )
        )->innerJoin(
            'ezcontentobject_version',
            'ezcontentobject.id',
            'ezcontentobject_version.contentobject_id'
        )->leftJoin(
            $this->handler->alias(
                $this->handler->quoteTable( 'ezcontentobject_tree' ),
                $this->handler->quoteIdentifier( 'main_tree' )
            ),
            $query->expr->lAnd(
                $query->expr->eq(
                    $this->handler->quoteColumn( "contentobject_id", "main_tree" ),
                    $this->handler->quoteColumn( "id", "ezcontentobject" )
                ),
                $query->expr->eq(
                    $this->handler->quoteColumn( "main_node_id", "main_tree" ),
                    $this->handler->quoteColumn( "node_id", "main_tree" )
                )
            )
        );

        if ( $sort !== null )
        {
            $this->sortClauseConverter->applyJoin( $query, $sort );
        }

        $query->where(
            $this->getQueryCondition( $filter, $query, $translations )
        );

        if ( $sort !== null )
        {
            $this->sortClauseConverter->applyOrderBy( $query );
        }

        $query->limit( $limit, $offset );

        $statement = $query->prepare();
        $statement->execute();

        return $statement->fetchAll( \PDO::FETCH_ASSOC );
    }

 /**
  * @param mixed[] $destinationContentIds
  *
  * @throws \Exception
  *
  * @return array
  */
 protected function getRelationXmlHashFromDB(array $destinationContentIds)
 {
     if (empty($destinationContentIds)) {
         return array();
     }
     $q = $this->db->createSelectQuery();
     $q->select($this->db->aliasedColumn($q, 'id', 'ezcontentobject'), $this->db->aliasedColumn($q, 'remote_id', 'ezcontentobject'), $this->db->aliasedColumn($q, 'current_version', 'ezcontentobject'), $this->db->aliasedColumn($q, 'contentclass_id', 'ezcontentobject'), $this->db->aliasedColumn($q, 'node_id', 'ezcontentobject_tree'), $this->db->aliasedColumn($q, 'parent_node_id', 'ezcontentobject_tree'), $this->db->aliasedColumn($q, 'identifier', 'ezcontentclass'))->from($this->db->quoteTable('ezcontentobject'))->leftJoin($this->db->quoteTable('ezcontentobject_tree'), $q->expr->lAnd($q->expr->eq($this->db->quoteColumn('contentobject_id', 'ezcontentobject_tree'), $this->db->quoteColumn('id', 'ezcontentobject')), $q->expr->eq($this->db->quoteColumn('node_id', 'ezcontentobject_tree'), $this->db->quoteColumn('main_node_id', 'ezcontentobject_tree'))))->leftJoin($this->db->quoteTable('ezcontentclass'), $q->expr->lAnd($q->expr->eq($this->db->quoteColumn('id', 'ezcontentclass'), $this->db->quoteColumn('contentclass_id', 'ezcontentobject')), $q->expr->eq($this->db->quoteColumn('version', 'ezcontentclass'), $q->bindValue(ContentType::STATUS_DEFINED, null, PDO::PARAM_INT))))->where($q->expr->in($this->db->quoteColumn('id', 'ezcontentobject'), $destinationContentIds));
     $stmt = $q->prepare();
     $stmt->execute();
     return $stmt->fetchAll(PDO::FETCH_ASSOC | PDO::FETCH_GROUP);
 }

 /**
  * @see loadContentInfo(), loadContentInfoByRemoteId()
  *
  * @param string $column
  * @param mixed $id
  *
  * @throws \eZ\Publish\Core\Base\Exceptions\NotFoundException
  *
  * @return array
  */
 private function internalLoadContentInfo($column, $id)
 {
     /** @var $query \eZ\Publish\Core\Persistence\Database\SelectQuery */
     $query = $this->dbHandler->createSelectQuery();
     $query->select('ezcontentobject.*', $this->dbHandler->aliasedColumn($query, 'main_node_id', 'ezcontentobject_tree'))->from($this->dbHandler->quoteTable('ezcontentobject'))->leftJoin($this->dbHandler->quoteTable('ezcontentobject_tree'), $query->expr->lAnd($query->expr->eq($this->dbHandler->quoteColumn('contentobject_id', 'ezcontentobject_tree'), $this->dbHandler->quoteColumn('id', 'ezcontentobject')), $query->expr->eq($this->dbHandler->quoteColumn('main_node_id', 'ezcontentobject_tree'), $this->dbHandler->quoteColumn('node_id', 'ezcontentobject_tree'))))->where($query->expr->eq($this->dbHandler->quoteColumn($column, 'ezcontentobject'), $query->bindValue($id, null, $column === 'id' ? PDO::PARAM_INT : PDO::PARAM_STR)));
     $statement = $query->prepare();
     $statement->execute();
     $row = $statement->fetch(PDO::FETCH_ASSOC);
     if (empty($row)) {
         throw new NotFound('content', "{$column}: {$id}");
     }
     return $row;
 }

 /**
  * Creates a select query for tag objects.
  *
  * Creates a select query with all necessary joins to fetch a complete
  * tag. Does not apply any WHERE conditions.
  *
  * @param string[] $translations
  * @param bool $useAlwaysAvailable
  *
  * @return \eZ\Publish\Core\Persistence\Database\SelectQuery
  */
 protected function createTagFindQuery(array $translations = null, $useAlwaysAvailable = true)
 {
     /** @var $query \eZ\Publish\Core\Persistence\Database\SelectQuery */
     $query = $this->handler->createSelectQuery();
     $query->select($this->handler->aliasedColumn($query, 'id', 'eztags'), $this->handler->aliasedColumn($query, 'parent_id', 'eztags'), $this->handler->aliasedColumn($query, 'main_tag_id', 'eztags'), $this->handler->aliasedColumn($query, 'keyword', 'eztags'), $this->handler->aliasedColumn($query, 'depth', 'eztags'), $this->handler->aliasedColumn($query, 'path_string', 'eztags'), $this->handler->aliasedColumn($query, 'modified', 'eztags'), $this->handler->aliasedColumn($query, 'remote_id', 'eztags'), $this->handler->aliasedColumn($query, 'main_language_id', 'eztags'), $this->handler->aliasedColumn($query, 'language_mask', 'eztags'), $this->handler->aliasedColumn($query, 'keyword', 'eztags_keyword'), $this->handler->aliasedColumn($query, 'locale', 'eztags_keyword'))->from($this->handler->quoteTable('eztags'))->leftJoin($this->handler->quoteTable('eztags_keyword'), $query->expr->lAnd($query->expr->eq($this->handler->quoteColumn('keyword_id', 'eztags_keyword'), $this->handler->quoteColumn('id', 'eztags')), $query->expr->eq($this->handler->quoteColumn('status', 'eztags_keyword'), $query->bindValue(1, null, PDO::PARAM_INT))));
     if (!empty($translations)) {
         if ($useAlwaysAvailable) {
             $query->where($query->expr->lOr($query->expr->in($this->handler->quoteColumn('locale', 'eztags_keyword'), $translations), $query->expr->eq($this->handler->quoteColumn('main_language_id', 'eztags'), $query->expr->bitAnd($this->handler->quoteColumn('language_id', 'eztags_keyword'), -2))));
         } else {
             $query->where($query->expr->in($this->handler->quoteColumn('locale', 'eztags_keyword'), $translations));
         }
     }
     return $query;
 }

 /**
  * @param mixed[] $destinationContentIds
  *
  * @throws \Exception
  *
  * @return array
  */
 protected function getRelationXmlHashFromDB(array $destinationContentIds)
 {
     if (empty($destinationContentIds)) {
         return array();
     }
     $q = $this->db->createSelectQuery();
     $q->select($this->db->aliasedColumn($q, 'id', 'ezcontentobject'), $this->db->aliasedColumn($q, 'remote_id', 'ezcontentobject'), $this->db->aliasedColumn($q, 'current_version', 'ezcontentobject'), $this->db->aliasedColumn($q, 'contentclass_id', 'ezcontentobject'), $this->db->aliasedColumn($q, 'node_id', 'ezcontentobject_tree'), $this->db->aliasedColumn($q, 'parent_node_id', 'ezcontentobject_tree'), $this->db->aliasedColumn($q, 'identifier', 'ezcontentclass'))->from($this->db->quoteTable('ezcontentobject'))->leftJoin($this->db->quoteTable('ezcontentobject_tree'), $q->expr->lAnd($q->expr->eq($this->db->quoteColumn('contentobject_id', 'ezcontentobject_tree'), $this->db->quoteColumn('id', 'ezcontentobject')), $q->expr->eq($this->db->quoteColumn('node_id', 'ezcontentobject_tree'), $this->db->quoteColumn('main_node_id', 'ezcontentobject_tree'))))->leftJoin($this->db->quoteTable('ezcontentclass'), $q->expr->lAnd($q->expr->eq($this->db->quoteColumn('id', 'ezcontentclass'), $this->db->quoteColumn('contentclass_id', 'ezcontentobject')), $q->expr->eq($this->db->quoteColumn('version', 'ezcontentclass'), $q->bindValue(ContentType::STATUS_DEFINED, null, PDO::PARAM_INT))))->where($q->expr->in($this->db->quoteColumn('id', 'ezcontentobject'), $destinationContentIds));
     $stmt = $q->prepare();
     $stmt->execute();
     $rows = $stmt->fetchAll(PDO::FETCH_ASSOC | PDO::FETCH_GROUP);
     if (empty($rows)) {
         throw new \Exception("Could find Content with id's" . var_export($destinationContentIds, true));
     } elseif (count($rows) !== count($destinationContentIds)) {
         throw new \Exception('Miss match of rows & id count:' . var_export($destinationContentIds, true));
     }
     return $rows;
 }

 /**
  * Loads complete URL alias data by given array of path hashes.
  *
  * @param string[] $urlHashes URL string hashes
  *
  * @return array
  */
 public function loadUrlAliasData(array $urlHashes)
 {
     /** @var $query \eZ\Publish\Core\Persistence\Database\SelectQuery */
     $query = $this->dbHandler->createSelectQuery();
     $count = count($urlHashes);
     foreach ($urlHashes as $level => $urlPartHash) {
         $tableName = "ezurlalias_ml" . ($level === $count - 1 ? "" : $level);
         if ($level === $count - 1) {
             $query->select($this->dbHandler->quoteColumn("id", $tableName), $this->dbHandler->quoteColumn("link", $tableName), $this->dbHandler->quoteColumn("is_alias", $tableName), $this->dbHandler->quoteColumn("alias_redirects", $tableName), $this->dbHandler->quoteColumn("is_original", $tableName), $this->dbHandler->quoteColumn("action", $tableName), $this->dbHandler->quoteColumn("action_type", $tableName), $this->dbHandler->quoteColumn("lang_mask", $tableName), $this->dbHandler->quoteColumn("text", $tableName), $this->dbHandler->quoteColumn("parent", $tableName), $this->dbHandler->quoteColumn("text_md5", $tableName))->from($this->dbHandler->quoteTable("ezurlalias_ml"));
         } else {
             $query->select($this->dbHandler->aliasedColumn($query, "id", $tableName), $this->dbHandler->aliasedColumn($query, "link", $tableName), $this->dbHandler->aliasedColumn($query, "is_alias", $tableName), $this->dbHandler->aliasedColumn($query, "alias_redirects", $tableName), $this->dbHandler->aliasedColumn($query, "is_original", $tableName), $this->dbHandler->aliasedColumn($query, "action", $tableName), $this->dbHandler->aliasedColumn($query, "action_type", $tableName), $this->dbHandler->aliasedColumn($query, "lang_mask", $tableName), $this->dbHandler->aliasedColumn($query, "text", $tableName), $this->dbHandler->aliasedColumn($query, "parent", $tableName), $this->dbHandler->aliasedColumn($query, "text_md5", $tableName))->from($query->alias("ezurlalias_ml", $tableName));
         }
         $query->where($query->expr->lAnd($query->expr->eq($this->dbHandler->quoteColumn("text_md5", $tableName), $query->bindValue($urlPartHash, null, \PDO::PARAM_STR)), $query->expr->eq($this->dbHandler->quoteColumn("parent", $tableName), isset($previousTableName) ? $this->dbHandler->quoteColumn("link", $previousTableName) : $query->bindValue(0, null, \PDO::PARAM_INT))));
         $previousTableName = $tableName;
     }
     $query->limit(1);
     $statement = $query->prepare();
     $statement->execute();
     return $statement->fetch(\PDO::FETCH_ASSOC);
 }

 /**
  * Creates an array of select columns for $tableName.
  *
  * @param \eZ\Publish\Core\Persistence\Database\SelectQuery $q
  * @param string $tableName
  */
 protected function selectColumns(SelectQuery $q, $tableName)
 {
     foreach ($this->columns[$tableName] as $col) {
         $q->select($this->dbHandler->aliasedColumn($q, $col, $tableName));
     }
 }

 /**
  * Creates a generalized query for fetching object state group(s)
  *
  * @return \eZ\Publish\Core\Persistence\Database\SelectQuery
  */
 protected function createObjectStateGroupFindQuery()
 {
     $query = $this->dbHandler->createSelectQuery();
     $query->select($this->dbHandler->aliasedColumn($query, 'default_language_id', 'ezcobj_state_group'), $this->dbHandler->aliasedColumn($query, 'id', 'ezcobj_state_group'), $this->dbHandler->aliasedColumn($query, 'identifier', 'ezcobj_state_group'), $this->dbHandler->aliasedColumn($query, 'language_mask', 'ezcobj_state_group'), $this->dbHandler->aliasedColumn($query, 'description', 'ezcobj_state_group_language'), $this->dbHandler->aliasedColumn($query, 'language_id', 'ezcobj_state_group_language'), $this->dbHandler->aliasedColumn($query, 'real_language_id', 'ezcobj_state_group_language'), $this->dbHandler->aliasedColumn($query, 'name', 'ezcobj_state_group_language'))->from($this->dbHandler->quoteTable('ezcobj_state_group'))->innerJoin($this->dbHandler->quoteTable('ezcobj_state_group_language'), $query->expr->eq($this->dbHandler->quoteColumn('id', 'ezcobj_state_group'), $this->dbHandler->quoteColumn('contentobject_state_group_id', 'ezcobj_state_group_language')));
     return $query;
 }