/** * 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); }