public function getItems()
{
$this->qb->setMaxResults($this->getLimit());
$this->qb->setFirstResult($this->getOffset());
list($sortColumn, $sortType) = $this->getSorting();
if ($sortColumn) {
$this->qb->orderBy($this->qb->getRootAlias() . "." . $sortColumn, $sortType);
}
return $this->qb->getQuery()->getResult();
}
/**
* Get the data
* @return array
*/
public function getData()
{
/**
* Paginator is better if the query uses ManyToMany associations
*/
return $this->data ?: $this->data_source->getQuery()->getResult();
}
/**
* @param ChannelConditionInterface $condition
* @return ChannelInterface
*/
public function searchOne(ChannelConditionInterface $condition)
{
$this->qb = $this->em->createQueryBuilder();
$this->qb->select('ch')->from('CoreBundle:Channel', 'ch');
$this->initQueryBuilder($condition);
return $this->qb->getQuery()->getOneOrNullResult();
}
/**
* Get the data
* @return array
*/
public function getData()
{
/**
* Paginator is better if the query uses ManyToMany associations
*/
return $this->data ?: $this->data_source->getQuery()->getResult(Doctrine\ORM\Query::HYDRATE_ARRAY);
}
/**
* {@inheritdoc}
*/
public function getEntities()
{
$query = $this->queryBuilder->getQuery();
foreach ($this->hints as $name => $value) {
$query->setHint($name, $value);
}
return $query->execute();
}
/**
* @param CompanyConditionInterface $condition
* @return Company[]
*/
public function search(CompanyConditionInterface $condition)
{
$this->qb = $this->em->createQueryBuilder();
$this->qb->select('co')->from('CoreBundle:Company', 'co');
$this->initQueryBuilder($condition);
$this->preparePagination($this->qb, $condition);
return $this->qb->getQuery()->getResult();
}
/**
* Convert an array of ID's to Doctrine entities / arrays
*
* @param array $data
* @return array
*/
public function convert($data)
{
if (empty($data)) {
return array();
}
$this->qb->andWhere($this->qb->expr()->in($this->searchfield, $data));
return $this->qb->getQuery()->getResult();
}
/**
* @return ResultRecordInterface[]
*/
public function getResults()
{
$results = $this->qb->getQuery()->execute();
$rows = [];
foreach ($results as $result) {
$rows[] = new ResultRecord($result);
}
return $rows;
}
/**
* {@inheritDoc}
*
* This function overwrites the original function to add the "automaticSorting" functionnality
*/
public function getEntities()
{
$entities = $this->queryBuilder->getQuery()->execute();
// If automaticSorting is On
if ($this->automaticSorting) {
// Sort the entities
$entities = $this->treeEntityOrderer->sortEntities($entities);
}
return $entities;
}
/**
* Returns the Query Builder or the results depending on the repository parameters
*
* @param \Doctrine\ORM\QueryBuilder $queryBuilder
* @param bool $singleResult
*
* @return mixed
*/
protected function processQuery($queryBuilder, $singleResult = false)
{
if ($this->returnQueryBuilder) {
return $queryBuilder;
}
if ($singleResult) {
return $queryBuilder->getQuery()->getSingleResult();
}
return $queryBuilder->getQuery()->getResult();
}
/**
* @return ResultRecordInterface[]
*/
public function getResults()
{
$query = $this->aclHelper->apply($this->qb->getQuery());
$results = $query->execute();
$rows = [];
foreach ($results as $result) {
$rows[] = new ResultRecord($result);
}
return $rows;
}
protected function processOrderByForList(QueryBuilder $qb, $orderBy)
{
$fieldName = is_array($orderBy) ? key($orderBy) : $orderBy;
$order = is_array($orderBy) ? $orderBy[$fieldName] : 'ASC';
if (in_array($fieldName, ['visits', 'visitsCount', 'visitCount'])) {
$qb->addSelect('COUNT(v) AS totalVisits')->leftJoin('s.visits', 'v')->groupBy('s')->orderBy('totalVisits', $order);
return array_column($qb->getQuery()->getResult(), 0);
} elseif (in_array($fieldName, ['originalUrl', 'shortCode', 'dateCreated'])) {
$qb->orderBy('s.' . $fieldName, $order);
}
return $qb->getQuery()->getResult();
}
protected function loadEntities()
{
if ($this->query) {
$items = $this->query->getQuery()->getResult();
} else {
$meta = $this->form->mapper->entityManager->getClassMetadata(get_class($this->parent->data));
if (!$meta->hasAssociation($this->name)) {
throw new Nette\InvalidStateException();
}
$class = $meta->associationMappings[$this->name]['targetEntity'];
$items = $this->getParent()->form->mapper->entityManager->getRepository($class)->findBy($this->criteria, $this->orderBy, $this->limit, $this->offset);
}
$this->setItems($items);
}
public function getData(array $p_params, array $p_cols)
{
$this->_queryObject = $this->_repository->createQueryBuilder('e');
// search
if (!empty($p_params['search'])) {
$this->search($p_params['search'], $p_cols);
}
// sort
if (@count($p_params['sortCol'])) {
$this->sort($p_params, $p_cols);
}
// limit
$this->_queryObject->setFirstResult((int) $p_params['displayStart'])->setMaxResults((int) $p_params['displayLength']);
return $this->_queryObject->getQuery()->getResult();
}
/**
* @return ResultRecordInterface[]
*/
public function getResults()
{
$query = $this->qb->getQuery();
$this->setQueryHints($query);
$event = new OrmResultBefore($this->datagrid, $query);
$this->eventDispatcher->dispatch(OrmResultBefore::NAME, $event);
$results = $event->getQuery()->execute();
$rows = [];
foreach ($results as $result) {
$rows[] = new ResultRecord($result);
}
$event = new OrmResultAfter($this->datagrid, $rows);
$this->eventDispatcher->dispatch(OrmResultAfter::NAME, $event);
return $event->getRecords();
}
/**
* Return an article array by tag
* @param $strings
* @return array
*/
public function searchArticlesByTags($strings)
{
$strings = $this->setCleanRequest($strings);
$this->qb->select('t')->from('AppBundle:Tag', 't')->where('t.slug LIKE :strings')->addOrderBy('t.frequency', 'DESC')->setParameter('strings', $strings);
$q = $this->qb->getQuery();
$tags = $q->getResult();
$articlesArray = array();
foreach ($tags as $tag) {
$articles = $tag->getArticles();
foreach ($articles as $article) {
array_push($articlesArray, $article);
}
}
return $articlesArray;
}
/**
* {@inheritdoc}
*/
public function execute()
{
// emit listbuilder.create event
$event = new ListBuilderCreateEvent($this);
$this->eventDispatcher->dispatch(ListBuilderEvents::LISTBUILDER_CREATE, $event);
$this->expressionFields = $this->getUniqueExpressionFieldDescriptors($this->expressions);
// first create simplified id query
// select ids with all necessary filter data
$ids = $this->findIdsByGivenCriteria();
// if no results are found - return
if (count($ids) < 1) {
return [];
}
// now select all data
$this->queryBuilder = $this->em->createQueryBuilder()->from($this->entityName, $this->entityName);
$this->assignJoins($this->queryBuilder);
// Add all select fields
foreach ($this->selectFields as $field) {
$this->queryBuilder->addSelect($field->getSelect() . ' AS ' . $field->getName());
}
// group by
$this->assignGroupBy($this->queryBuilder);
// assign sort-fields
$this->assignSortFields($this->queryBuilder);
// use ids previously selected ids for query
$this->queryBuilder->where($this->entityName . '.id IN (:ids)')->setParameter('ids', $ids);
return $this->queryBuilder->getQuery()->getArrayResult();
}
/**
* @route: blog_home
* Homepage controller
*/
public function homeAction()
{
// entity manager
$em = $this->getEm();
$qb = new QueryBuilder($em);
// dql query
$qb->select('p,c,t')
->from('Bundle\BlogBundle\Entity\Post', 'p')
->join('p.category', 'c')
->join('p.tags', 't')
->orderBy('p.date', 'DESC');
// array of objects
$posts = $qb->getQuery()->getResult();
$query = $em->createQuery('SELECT c,p FROM Bundle\BlogBundle\Entity\Category c JOIN c.posts p');
$categories = $query->getResult();
$query = $em->createQuery('SELECT t,p FROM Bundle\BlogBundle\Entity\Tag t JOIN t.posts p');
$tags = $query->getResult();
return $this->render('BlogBundle:Blog:homepage.html.twig', array(
'posts' => $posts,
'categories' => $categories,
'tags' => $tags
));
}
public function paginate(QueryBuilder $query, $limit, $page)
{
$query->setMaxResults($limit)->setFirstResult($limit * ($page - 1));
$paginator = new Paginator($query->getQuery());
$paginator->setUseOutputWalkers(false);
return $paginator;
}
/**
* {@inheritdoc}
*/
public function getResult(QueryBuilder $queryBuilder, DataSetRequestInterface $request, ColumnCollection $columns)
{
$query = $queryBuilder->getQuery();
$result = $query->getArrayResult();
$result = $this->transformResult($result);
return $result;
}
/**
* getAggregateResult
*
* performs aggregation SQL query
*
* @return null|SingleResult
*/
protected function getAggregateResult()
{
$select = '';
if ($native = $this->query instanceof NativeQuery) {
$rsm = new ResultSetMapping();
}
foreach ($this->list_fields as $field) {
if ($func = $field->getPatternAggregateSqlFunction()) {
$select .= sprintf("%s AS %s,", $func['func'], $func['name']);
if ($native) {
$rsm->addScalarResult($func['name'], $func['name']);
}
}
}
$select = trim($select, ',');
if ($select) {
if ($native) {
$q = strstr($this->query->getSQL(), " FROM ");
$q = "SELECT " . $select . $q;
$sumQuery = $this->em->createNativeQuery($q, $rsm)->setParameters($this->query->getParameters());
} else {
$q = strstr($this->query->getQuery()->getDql(), " FROM ");
$q = "SELECT " . $select . $q;
$sumQuery = $this->em->createQuery($q)->setParameters($this->query->getQuery()->getParameters())->setMaxResults(1);
}
try {
return $sumQuery->getSingleResult();
} catch (\Doctrine\Orm\NoResultException $e) {
return null;
}
}
}
public function getQuery()
{
$query = parent::getQuery();
$query->setHydrationMode(Query::HYDRATE_OBJECT);
$query->setHint(BaseQuery::HINT_INCLUDE_META_COLUMNS, true);
return $query;
}
/**
* Returns the query result count
*
* @return integer The query result count
* @throws Exception\DatabaseConnectionException
* @api
*/
public function count()
{
try {
$originalQuery = $this->queryBuilder->getQuery();
$dqlQuery = clone $originalQuery;
$dqlQuery->setParameters($originalQuery->getParameters());
$dqlQuery->setHint(\Doctrine\ORM\Query::HINT_CUSTOM_TREE_WALKERS, array('TYPO3\\Flow\\Persistence\\Doctrine\\CountWalker'));
$offset = $dqlQuery->getFirstResult();
$limit = $dqlQuery->getMaxResults();
if ($offset !== NULL) {
$dqlQuery->setFirstResult(NULL);
}
$numberOfResults = (int) $dqlQuery->getSingleScalarResult();
if ($offset !== NULL) {
$numberOfResults = max(0, $numberOfResults - $offset);
}
if ($limit !== NULL) {
$numberOfResults = min($numberOfResults, $limit);
}
return $numberOfResults;
} catch (\Doctrine\ORM\ORMException $ormException) {
$this->systemLogger->logException($ormException);
return 0;
} catch (\PDOException $pdoException) {
throw new Exception\DatabaseConnectionException($pdoException->getMessage(), $pdoException->getCode());
}
}
protected function assertValidQueryBuilder(QueryBuilder $qb, $expectedDql)
{
$dql = $qb->getDql();
$q = $qb->getQuery();
$this->assertEquals($expectedDql, $dql);
}
/**
* @param AbstractDataTable $table
* @param \Doctrine\ORM\QueryBuilder $qb
* @param Response $response
* @param DataToStringConverter $dataToStringConverter
* @throws \Exception
*/
public static function build(AbstractDataTable $table, \Doctrine\ORM\QueryBuilder $qb, Response $response, DataToStringConverter $dataToStringConverter, $rowCallback = null)
{
$entities = $qb->getQuery()->getResult();
foreach ($entities as $result) {
$entity = $result;
if ($table->getHasScalarColumns()) {
$entity = $result[0];
}
$tmp = [];
if (method_exists($entity, 'getId')) {
$tmp['DT_RowId'] = 'row_' . $entity->getId();
$tmp['DT_RowAttr'] = ['data-entity' => $entity->getId()];
// DT_RowClass
// DT_RowData
}
if (is_callable($rowCallback)) {
$cbResult = $rowCallback($entity, $table);
$tmp = array_merge($tmp, $cbResult);
}
foreach ($table->getColumns() as $column) {
if ($column instanceof EntitiesCountColumn) {
$tmp[$column->getName()] = $result[$column->getField() . '_count'];
} elseif ($column instanceof EntitiesScalarColumn) {
$tmp[$column->getName()] = $result[$column->getField() . '_' . $column->getOperation()];
} else {
$tmp[$column->getName()] = self::getColumnProperty($entity, $column, $dataToStringConverter);
}
}
$response->data[] = $tmp;
}
}
/**
* @param int $limit
* @param int $offset
* @return array
*/
public function getData($limit = NULL, $offset = NULL)
{
if ($limit !== NULL) {
$this->builder->setMaxResults($limit);
$this->builder->setFirstResult($offset);
}
$class = $this->DTO;
if ($class) {
foreach ($this->builder->getQuery()->getResult($this->resultType) as $item) {
(yield new $class($item));
}
} else {
foreach ($this->builder->getQuery()->getResult($this->resultType) as $item) {
(yield $item);
}
}
}
/**
* @return mixed
*/
public function getItems()
{
$this->qb->setMaxResults($this->getLimit());
$this->qb->setFirstResult($this->getOffset());
if (count($this->getSorting()) > 0) {
foreach ($this->getSorting() as $sortColumn => $sortType) {
if (isset($this->columnAliases[$sortColumn])) {
$sortColumn = $this->columnAliases[$sortColumn]->qbName;
} else {
$aliases = $this->qb->getRootAliases();
$sortColumn = $aliases[0] . '.' . $sortColumn;
}
$this->qb->addOrderBy($sortColumn, is_string($sortType) && strncasecmp($sortType, 'd', 1) || $sortType > 0 ? 'ASC' : 'DESC');
}
}
return $this->qb->getQuery()->getResult();
}
/**
* Creates pagination object
*/
protected function createPagination()
{
$paginatorOptions = ['sortFieldWhitelist' => $this->getOption('sortFieldWhitelist')];
if (null !== $this->getOption('defaultSortFieldName')) {
$paginatorOptions['defaultSortFieldName'] = $this->getOption('defaultSortFieldName');
}
$this->pagination = $this->paginator->paginate($this->queryBuilder->getQuery(), $this->page, $this->getOption('limit'), $paginatorOptions);
}
/**
* @param QueryBuilder $qbr
* @return array
*/
private function getIds(QueryBuilder $qbr)
{
$qbr->select(sprintf('%s.id', static::TABLE_ALIAS))->groupBy(sprintf('%s.id', static::TABLE_ALIAS));
$rows = $qbr->getQuery()->getResult();
return array_map(function (array $row) {
return $row['id'];
}, $rows);
}
/**
* {@inheritdoc}
*/
public function getQuery()
{
$query = parent::getQuery();
foreach ($this->hints as $name => $value) {
$query->setHint($name, $value);
}
return $query;
}
