PHP ResultSetMappingBuilder::generateSelectClause方法代码示例

 /**
  * Find all members with a birthday in the next $days days.
  *
  * When $days equals 0 or isn't given, it will give all birthdays of today.
  *
  * @param int $days The number of days to look ahead.
  *
  * @return array Of members sorted by birthday
  */
 public function findBirthdayMembers($days)
 {
     // unfortunately, there is no support for functions like DAY() and MONTH()
     // in doctrine2, thus we have to use the NativeSQL here
     $builder = new ResultSetMappingBuilder($this->em);
     $builder->addRootEntityFromClassMetadata('Decision\\Model\\Member', 'm');
     $select = $builder->generateSelectClause(array('m' => 't1'));
     $sql = "SELECT {$select} FROM Member AS t1" . " WHERE DATEDIFF(DATE_SUB(t1.birth, INTERVAL YEAR(t1.birth) YEAR)," . " DATE_SUB(CURDATE(), INTERVAL YEAR(CURDATE()) YEAR)) BETWEEN 0 AND :days" . " AND t1.expiration >= CURDATE()" . "ORDER BY DATE_SUB(t1.birth, INTERVAL YEAR(t1.birth) YEAR) ASC";
     $query = $this->em->createNativeQuery($sql, $builder);
     $query->setParameter('days', $days);
     return $query->getResult();
 }

 public function getAdminsOfBases(array $basList)
 {
     $rsm = new ResultSetMappingBuilder($this->em);
     $rsm->addRootEntityFromClassMetadata('Alchemy\\Phrasea\\Model\\Entities\\User', 'u');
     $rsm->addScalarResult('base_id', 'base_id');
     $selectClause = $rsm->generateSelectClause();
     $query = $this->em->createNativeQuery('
         SELECT b.base_id, ' . $selectClause . ' FROM Users u, basusr b
         WHERE u.id = b.usr_id
             AND b.base_id IN (' . implode(', ', $basList) . ')
             AND u.model_of IS NULL
             AND b.actif="1"
             AND b.canadmin="1"
             AND u.deleted="0"', $rsm);
     return $query->getResult();
 }

 /**
  * {@inheritDoc}
  */
 public function loadCriteria(PersistentCollection $coll, Criteria $criteria)
 {
     $mapping = $coll->getMapping();
     $owner = $coll->getOwner();
     $ownerMetadata = $this->em->getClassMetadata(get_class($owner));
     $whereClauses = $params = array();
     foreach ($mapping['relationToSourceKeyColumns'] as $key => $value) {
         $whereClauses[] = sprintf('t.%s = ?', $key);
         $params[] = $ownerMetadata->getFieldValue($owner, $value);
     }
     $parameters = $this->expandCriteriaParameters($criteria);
     foreach ($parameters as $parameter) {
         list($name, $value) = $parameter;
         $whereClauses[] = sprintf('te.%s = ?', $name);
         $params[] = $value;
     }
     $mapping = $coll->getMapping();
     $targetClass = $this->em->getClassMetadata($mapping['targetEntity']);
     $tableName = $this->quoteStrategy->getTableName($targetClass, $this->platform);
     $joinTable = $this->quoteStrategy->getJoinTableName($mapping, $ownerMetadata, $this->platform);
     $onConditions = $this->getOnConditionSQL($mapping);
     $rsm = new Query\ResultSetMappingBuilder($this->em);
     $rsm->addRootEntityFromClassMetadata($mapping['targetEntity'], 'te');
     $sql = 'SELECT ' . $rsm->generateSelectClause() . ' FROM ' . $tableName . ' te' . ' JOIN ' . $joinTable . ' t ON' . implode(' AND ', $onConditions) . ' WHERE ' . implode(' AND ', $whereClauses);
     $stmt = $this->conn->executeQuery($sql, $params);
     $hydrator = $this->em->newHydrator(Query::HYDRATE_OBJECT);
     return $hydrator->hydrateAll($stmt, $rsm);
 }

 /**
  * @group DDC-2055
  */
 public function testGenerateSelectClauseIncrement()
 {
     $rsm = new ResultSetMappingBuilder($this->_em, ResultSetMappingBuilder::COLUMN_RENAMING_INCREMENT);
     $rsm->addRootEntityFromClassMetadata('Doctrine\\Tests\\Models\\CMS\\CmsUser', 'u');
     $selectClause = $rsm->generateSelectClause();
     $this->assertEquals('u.id AS id0, u.status AS status1, u.username AS username2, u.name AS name3, u.email_id AS email_id4', $selectClause);
 }

 public function getTranslateMenuItemBySlug($slug, $locale)
 {
     $em = $this->getEntityManager();
     $rsm = new ResultSetMappingBuilder($em);
     $rsm->addRootEntityFromClassMetadata('CoreBundle\\Entity\\MenuItemTranslation', 'alias');
     $selectClause = $rsm->generateSelectClause(['alias' => 'table_alias']);
     $sql = "SELECT " . $selectClause . " FROM menuitem_translation table_alias WHERE table_alias.slug = '{$slug}' ";
     $query = $em->createNativeQuery($sql, $rsm);
     $entity = $query->getOneOrNullResult();
     $sql = "SELECT " . $selectClause . " FROM menuitem_translation table_alias WHERE table_alias.translatable_id = '" . $entity->getTranslatable()->getId() . "' " . "AND table_alias.locale = '" . $locale . "' ";
     $query = $em->createNativeQuery($sql, $rsm);
     $entity = $query->getOneOrNullResult();
     return $entity;
 }

 /**
  * {@inheritDoc}
  */
 public function loadCriteria(PersistentCollection $collection, Criteria $criteria)
 {
     $mapping = $collection->getMapping();
     $owner = $collection->getOwner();
     $ownerMetadata = $this->em->getClassMetadata(get_class($owner));
     $id = $this->uow->getEntityIdentifier($owner);
     $targetClass = $this->em->getClassMetadata($mapping['targetEntity']);
     $onConditions = $this->getOnConditionSQL($mapping);
     $whereClauses = $params = [];
     if (!$mapping['isOwningSide']) {
         $associationSourceClass = $targetClass;
         $mapping = $targetClass->associationMappings[$mapping['mappedBy']];
         $sourceRelationMode = 'relationToTargetKeyColumns';
     } else {
         $associationSourceClass = $ownerMetadata;
         $sourceRelationMode = 'relationToSourceKeyColumns';
     }
     foreach ($mapping[$sourceRelationMode] as $key => $value) {
         $whereClauses[] = sprintf('t.%s = ?', $key);
         $params[] = $ownerMetadata->containsForeignIdentifier ? $id[$ownerMetadata->getFieldForColumn($value)] : $id[$ownerMetadata->fieldNames[$value]];
     }
     $parameters = $this->expandCriteriaParameters($criteria);
     foreach ($parameters as $parameter) {
         list($name, $value) = $parameter;
         $field = $this->quoteStrategy->getColumnName($name, $targetClass, $this->platform);
         $whereClauses[] = sprintf('te.%s = ?', $field);
         $params[] = $value;
     }
     $tableName = $this->quoteStrategy->getTableName($targetClass, $this->platform);
     $joinTable = $this->quoteStrategy->getJoinTableName($mapping, $associationSourceClass, $this->platform);
     $rsm = new Query\ResultSetMappingBuilder($this->em);
     $rsm->addRootEntityFromClassMetadata($targetClass->name, 'te');
     $sql = 'SELECT ' . $rsm->generateSelectClause() . ' FROM ' . $tableName . ' te' . ' JOIN ' . $joinTable . ' t ON' . implode(' AND ', $onConditions) . ' WHERE ' . implode(' AND ', $whereClauses);
     $sql .= $this->getOrderingSql($criteria, $targetClass);
     $sql .= $this->getLimitSql($criteria);
     $stmt = $this->conn->executeQuery($sql, $params);
     return $this->em->newHydrator(Query::HYDRATE_OBJECT)->hydrateAll($stmt, $rsm);
 }

 /**
  * Search for a series
  *
  * @param string $q The search query
  * @param integer $page The page of results to retrieve
  * @param integer $perPage How many results to retrieve
  * @return SearchResult The search results
  * @see ChaosTangent\FansubEbooks\Bundle\AppBundle\DataFixtures\ORM\CreateSearchIndex
  */
 public function search($q, $page = 1, $perPage = 30)
 {
     // count total results
     $countSql = 'SELECT COUNT(s.id)
         FROM series s
         WHERE to_tsvector(:config, s.title) @@ to_tsquery(:query)';
     $total = $this->_em->getConnection()->fetchColumn($countSql, [':config' => 'english', ':query' => $q], 0);
     $fileSql = 'SELECT fs.id, COUNT(f.id) AS file_count
         FROM series fs
         JOIN files f ON f.series_id = fs.id
         GROUP BY fs.id';
     $lineSql = 'SELECT ls.id, COUNT(l.id) AS line_count
         FROM series ls
         JOIN files lf ON lf.series_id = ls.id
         JOIN lines l ON l.file_id = lf.id
         GROUP BY ls.id';
     $tweetSql = 'SELECT ts.id, COUNT(t.id) AS tweet_count
         FROM series ts
         JOIN files tf ON tf.series_id = ts.id
         JOIN lines tl ON tl.file_id = tf.id
         JOIN tweets t ON t.line_id = tl.id
         GROUP BY ts.id';
     // fetch page of results
     $rsm = new ResultSetMappingBuilder($this->_em);
     $rsm->addRootEntityFromClassMetadata('ChaosTangent\\FansubEbooks\\Entity\\Series', 's');
     $rsm->addScalarResult('file_count', 'file_count', 'integer');
     $rsm->addScalarResult('line_count', 'line_count', 'integer');
     $rsm->addScalarResult('tweet_count', 'tweet_count', 'integer');
     $sql = 'WITH file_counts AS (' . $fileSql . '), line_counts AS (' . $lineSql . '), tweet_counts AS (' . $tweetSql . ')
         SELECT ' . $rsm->generateSelectClause() . ', fc.file_count, lc.line_count, tc.tweet_count
             FROM series s
             LEFT JOIN file_counts fc ON fc.id = s.id
             LEFT JOIN line_counts lc ON lc.id = s.id
             LEFT JOIN tweet_counts tc ON tc.id = s.id
             WHERE to_tsvector(:config, s.title) @@ to_tsquery(:query)
             ORDER BY ts_rank(to_tsvector(:config, s.title), to_tsquery(:query))
             LIMIT :limit OFFSET :offset';
     $query = $this->_em->createNativeQuery($sql, $rsm);
     $query->setParameters(['query' => $q, 'limit' => $perPage, 'offset' => ($page - 1) * $perPage, 'config' => 'english']);
     $result = $query->getResult();
     $ret = [];
     foreach ($result as $row) {
         $ret[] = $row[0]->setFileCount($row['file_count'])->setLineCount($row['line_count'])->setTweetCount($row['tweet_count']);
     }
     return new SearchResult($q, $ret, $total, $page, $perPage);
 }

 /**
  * Search for a line
  *
  * @param string $q The search query
  * @param integer $page The page of results to retrieve
  * @param integer $perPage How many results to retrieve
  * @return SearchResult The search results
  * @see ChaosTangent\FansubEbooks\Bundle\AppBundle\DataFixtures\ORM\CreateSearchIndex
  */
 public function search($q, $page = 1, $perPage = 30, Series $series = null)
 {
     if (empty(trim($q))) {
         return new SearchResult($q, [], 0, $page, $perPage);
     }
     // default query parameters
     $defaultParams = [':query' => trim($q), ':config' => 'english'];
     // default where clause
     $whereClause = 'WHERE to_tsvector(:config, l.line) @@ to_tsquery(:query)';
     if ($series !== null) {
         $whereClause .= ' AND f.series_id = :series';
         $defaultParams['series'] = $series->getId();
     }
     // count total results from search query
     $countSql = 'SELECT COUNT(l.id)
         FROM lines l
         JOIN files f ON f.id = l.file_id ' . $whereClause;
     $total = $this->_em->getConnection()->fetchColumn($countSql, $defaultParams, 0);
     // get the selected page of results from search query
     $rsm = new ResultSetMappingBuilder($this->_em);
     $rsm->addRootEntityFromClassMetadata('ChaosTangent\\FansubEbooks\\Entity\\Line', 'l');
     $rsm->addScalarResult('positive_votes', 'positive_votes', 'integer');
     $rsm->addScalarResult('negative_votes', 'negative_votes', 'integer');
     $rsm->addScalarResult('tweet_id', 'tweet_id');
     $sql = 'SELECT ' . $rsm->generateSelectClause() . ',
             SUM(CASE WHEN v.positive = true THEN 1 ELSE 0 END) AS positive_votes,
             SUM(CASE WHEN v.positive = false THEN 1 ELSE 0 END) AS negative_votes,
             t.tweet_id
         FROM lines l
         JOIN files f ON f.id = l.file_id
         LEFT JOIN votes v ON v.line_id = l.id
         LEFT JOIN tweets t ON t.line_id = l.id ' . $whereClause . '
         GROUP BY l.id, t.tweet_id
         ORDER BY ts_rank(to_tsvector(:config, l.line), to_tsquery(:query))
         LIMIT :limit OFFSET :offset';
     $query = $this->_em->createNativeQuery($sql, $rsm);
     $query->setParameters(array_merge($defaultParams, ['limit' => $perPage, 'offset' => ($page - 1) * $perPage]));
     $result = $query->getResult();
     $ret = [];
     foreach ($result as $row) {
         $ret[] = $row[0]->setPositiveVoteCount($row['positive_votes'])->setNegativeVoteCount($row['negative_votes'])->setTweetId($row['tweet_id']);
     }
     // bundle it all into a searchresult object
     return new SearchResult($q, $ret, $total, $page, $perPage);
 }