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