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();
}
/**
*
* @return boolean
*/
public function process()
{
$this->form->submit($this->request);
if ($this->form->isValid()) {
$formData = $this->form->getData();
$name = (string) $formData->getName();
if ($name) {
$this->qb->andWhere($this->qb->expr()->like('p.name', ':name'))->setParameter('name', '%' . $name . '%');
}
$text = (string) $formData->getText();
if ($text) {
$this->qb->andWhere($this->qb->expr()->orx($this->qb->expr()->like('p.intro', ':intro'), $this->qb->expr()->like('p.content', ':content')))->setParameter('intro', '%' . $text . '%')->setParameter('content', '%' . $text . '%');
}
$isPublished = (string) $formData->getIsPublished();
if ($isPublished) {
if ($isPublished == 'yes') {
$this->qb->andWhere($this->qb->expr()->eq('p.status', ':status'))->setParameter('status', PostStatus::PUBLISHED);
}
if ($isPublished == 'no') {
$this->qb->andWhere($this->qb->expr()->neq('p.status', ':status'))->setParameter('status', PostStatus::PUBLISHED);
}
}
$order = (string) $formData->getOrder();
if ($order) {
$this->qb->orderBy('p.' . $order, 'DESC');
}
return true;
}
return false;
}
/**
* Build and returns Query for select rows
* @param \Doctrine\ORM\QueryBuilder $queryBuilder
* @param \EMC\TableBundle\Provider\QueryConfigInterface $queryConfig
* @param array $mapping
* @return \Doctrine\ORM\Query
*/
private function getQueryRows(QueryBuilder $queryBuilder, QueryConfigInterface $queryConfig, array &$mapping)
{
$queryBuilder->resetDQLPart('select');
$limit = $queryConfig->getLimit();
$page = $queryConfig->getPage();
$select = $queryConfig->getSelect();
$orderBy = $queryConfig->getOrderBy();
if ($limit > 0) {
$queryBuilder->setMaxResults($limit)->setFirstResult(($page - 1) * $limit);
}
$mapping = array_map(function ($i) {
return 'col' . $i;
}, array_flip($select));
foreach ($mapping as $column => $name) {
$queryBuilder->addSelect($column . ' AS ' . $name);
}
if (count($orderBy) === 0) {
$queryBuilder->orderBy($queryBuilder->getRootAlias() . '.id', 'ASC');
} else {
foreach ($orderBy as $column => $isAsc) {
$queryBuilder->orderBy($column, $isAsc ? 'ASC' : 'DESC');
}
}
return $queryBuilder->getQuery();
}
/**
* @param ClientConditionInterface $condition
* @return Client[]
*/
public function search(ClientConditionInterface $condition)
{
$this->qb = $this->em->createQueryBuilder();
$this->qb->select('c')->from('CoreBundle:Client', 'c');
$this->initQueryBuilder($condition);
$this->preparePagination($this->qb, $condition);
$this->qb->orderBy('c.createdAt', 'DESC');
return $this->qb->getQuery()->getResult();
}
/**
* Get the query result
*
* @param integer $limit
*
* @return ArrayCollection
*/
public function getQueryResults($limit = null)
{
if (isset($limit)) {
$this->queryBuilder->setMaxResults($limit);
}
if (!$this->queryBuilder->getDQLPart('orderBy')) {
$this->queryBuilder->orderBy('a.createdAt', 'ASC');
}
return $this->queryBuilder->getQuery()->getResult();
}
protected function order()
{
$column = $this->getParamAdapter()->getColumn();
$order = $this->getParamAdapter()->getOrder();
if (!$column) {
return;
}
$header = $this->getTable()->getHeader($column);
$tableAlias = $header ? $header->getTableAlias() : 'q';
if ($column) {
$this->query->orderBy($tableAlias . '.' . $column, $order);
}
}
public function sort(array $p_cols)
{
foreach (array_keys($p_cols) as $id => $property) {
if (!is_string($property)) {
// not sortable
continue;
}
if (@in_array($id, $p_params['sortCol'])) {
$dir = @in_array($id, $p_params['sortDir']) ?: 'asc';
$this->_queryObject->orderBy("e.{$property}", $dir);
}
}
}
/**
* Parse order conditions and add it to query
*
* @param string|null $sort
* @param string|null $order
*/
public function orderBy($sort = null, $order = null)
{
if (is_string($sort)) {
if (strpos($sort, '.') > 0) {
$parts = explode('.', $sort);
if (count($parts) >= 2 && array_key_exists($parts[0], $this->metadata->associationMappings)) {
$this->qBuilder->innerJoin('e.' . $parts[0], $parts[0], Join::WITH);
$this->qBuilder->orderBy($parts[0] . '.' . $parts[1], $order);
}
} else {
if (array_key_exists($sort, $this->metadata->fieldMappings)) {
$this->qBuilder->orderBy('e.' . $sort, $order);
}
}
}
}
/**
* @param QueryBuilder $qb
* @param string $alias
* @param string|null $sortField
* @param string|null $sortOrder
* @return QueryBuilder
*/
protected function sorting(QueryBuilder $qb, string $alias, string $sortField = null, string $sortOrder = null)
{
if (!empty($sortField) && (strtolower($sortOrder) == 'asc' || strtolower($sortOrder) == 'desc')) {
$qb->orderBy($alias . '.' . $sortField, $sortOrder);
}
return $qb;
}
public static function apply(QueryBuilder $qb, array $orderBy)
{
foreach ($orderBy as $field => $sortOrder) {
$qb->orderBy('a.' . Camelizer::camelize($field), $sortOrder);
}
return $qb;
}
/**
* @param QueryBuilder $qb
* @param string $dqlAlias
*/
public function modify(QueryBuilder $qb, $dqlAlias)
{
if ($this->dqlAlias !== null) {
$dqlAlias = $this->dqlAlias;
}
$qb->orderBy(sprintf('%s.%s', $dqlAlias, $this->field), $this->order);
}
/**
* set order
*
* @param string $order_field
* @param string $order_type
*
* @return Datatable
*/
public function setOrder($order_field, $order_type)
{
$this->order_field = $order_field;
$this->order_type = $order_type;
$this->queryBuilder->orderBy($order_field, $order_type);
return $this;
}
/**
* {@inheritdoc}
*/
public function filter(Request $request, QueryBuilder $queryBuilder)
{
$order = $request->query->get('order') ?: self::DEFAULT_ORDER;
if ($request->query->has('sort')) {
$alias = $queryBuilder->getRootAliases()[0];
$queryBuilder->orderBy($alias . '.' . $request->query->get('sort'), $order);
}
}
/**
* Apply the sortings to the query builder
*
* @param QueryBuilder $queryBuilder
* @param Sort $sortCollectionDefinition
*
* @return \Doctrine\ORM\QueryBuilder
*/
public function sort(QueryBuilder $queryBuilder, Sort $sortCollectionDefinition, $uniqueNameToQueryFieldMap)
{
/** @var $sortDefinition SortCondition */
foreach ($sortCollectionDefinition as $sortDefinition) {
$identifier = $uniqueNameToQueryFieldMap[$sortDefinition->getFieldName()];
$queryBuilder->orderBy($identifier, $sortDefinition->getDirection());
}
return $queryBuilder;
}
/**
* @param $columns \Sorien\DataGridBundle\Grid\Column\Column[]
* @param $page int Page Number
* @param $limit int Rows Per Page
* @return \Sorien\DataGridBundle\Grid\Rows
*/
public function execute($columns, $page = 0, $limit = 0)
{
$this->query = $this->manager->createQueryBuilder($this->class);
$this->query->from($this->class, self::TABLE_ALIAS);
$where = $this->query->expr()->andx();
$sorted = false;
foreach ($columns as $column) {
$this->query->addSelect($this->getFieldName($column));
if ($column->isSorted() && !$column->isDefaultSort()) {
$this->query->orderBy($this->getFieldName($column, false), $column->getOrder());
$sorted = true;
} elseif (!$sorted && $column->isSorted() && $column->isDefaultSort()) {
$this->query->orderBy($this->getFieldName($column, false), $column->getOrder());
}
if ($column->isFiltered()) {
if ($column->getFiltersConnection() == column::DATA_CONJUNCTION) {
foreach ($column->getFilters() as $filter) {
$operator = $this->normalizeOperator($filter->getOperator());
$where->add($this->query->expr()->{$operator}($this->getFieldName($column, false), $this->normalizeValue($filter->getOperator(), $filter->getValue())));
}
} elseif ($column->getFiltersConnection() == column::DATA_DISJUNCTION) {
$sub = $this->query->expr()->orx();
foreach ($column->getFilters() as $filter) {
$operator = $this->normalizeOperator($filter->getOperator());
$sub->add($this->query->expr()->{$operator}($this->getFieldName($column, false), $this->normalizeValue($filter->getOperator(), $filter->getValue())));
}
$where->add($sub);
}
$this->query->where($where);
}
}
foreach ($this->joins as $alias => $field) {
$this->query->leftJoin($field, $alias);
}
if ($page > 0) {
$this->query->setFirstResult($page * $limit);
}
if ($limit > 0) {
$this->query->setMaxResults($limit);
}
//call overridden prepareQuery or associated closure
$query = $this->prepareQuery(clone $this->query);
$items = $query->getQuery()->getResult();
// hydrate result
$result = new Rows();
foreach ($items as $item) {
$row = new Row();
foreach ($item as $key => $value) {
$row->setField($key, $value);
}
//call overridden prepareRow or associated closure
if (($modifiedRow = $this->prepareRow($row)) != null) {
$result->addRow($modifiedRow);
}
}
return $result;
}
protected function applySorting(QueryBuilder $queryBuilder, array $sorting = null)
{
if (null === $sorting) {
return;
}
$alias = $this->getAlias();
foreach ($sorting as $property => $order) {
$queryBuilder->orderBy($alias . '.' . $property, $order);
}
}
protected function order()
{
$column = $this->getParamAdapter()->getColumn();
$order = $this->getParamAdapter()->getOrder();
if (!$column) {
return;
}
$header = $this->getTable()->getHeader($column);
$tableAlias = $header ? $header->getTableAlias() : 'q';
if (false === strpos($tableAlias, '.')) {
$tableAlias = $tableAlias . '.' . $column;
}
if ($header->getOrderJoin()) {
$joinAlias = 'j' . str_replace('.', '_', $tableAlias);
$this->query->join($tableAlias, $joinAlias);
$this->query->orderBy($joinAlias . '.' . $header->getOrderJoin(), $order);
} else {
$this->query->orderBy($tableAlias, $order);
}
}
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();
}
/**
* Add a orderBy clause to the main query
*
* @param $column
* @param $order
* @return $this
*/
public function orderBy($column, $order)
{
$column = str_replace(".", "", $column);
$selects = $this->queryBuilder->getDQLPart('select');
foreach ($selects as $select) {
$selectSegments = explode(" ", $select);
if ($selectSegments[count($selectSegments) - 1] == $column) {
$this->queryBuilder->orderBy($selectSegments[0], $order);
}
}
return $this;
}
/**
* Load the collection
*
*/
protected function initialize()
{
if ($this->initialized) {
return;
}
// Handle search
if (isset($this->searchExpression)) {
$queryBuilderExpression = $this->buildQueryBuilderExpression($this->searchExpression);
$this->queryBuilder->andWhere($queryBuilderExpression);
}
// Handle filters
if (isset($this->filterExpression)) {
$queryBuilderExpression = $this->buildQueryBuilderExpression($this->filterExpression);
$this->queryBuilder->andWhere($queryBuilderExpression);
}
// Handle sort
if (null !== $this->sortField && null !== $this->sortDirection) {
$oldOrderBys = $this->queryBuilder->getDQLPart('orderBy');
$this->queryBuilder->resetDQLPart('orderBy');
$this->queryBuilder->orderBy($this->sortField, $this->sortDirection);
foreach ($oldOrderBys as $oldOrderBy) {
$this->queryBuilder->add('orderBy', $oldOrderBy, true);
}
}
// Handle pagination
if (isset($this->limitPerPage)) {
$paginator = new DoctrineORMPaginator($this->queryBuilder->getQuery());
$paginator->setLimitPerPage($this->limitPerPage)->setRangeLimit($this->rangeLimit)->setPage($this->page);
$this->iterator = $paginator->getIterator();
$this->paginator = $paginator;
} else {
$items = $this->queryBuilder->getQuery()->getResult();
$this->iterator = new \ArrayIterator($items);
$this->paginator = null;
}
$this->initialized = true;
}
public function implement(QueryBuilder $queryBuilder)
{
if ($this->payload->get('count')->getValue() === true) {
return $queryBuilder;
}
$i = 0;
foreach ($this->getValue() as $field => $direction) {
if ($i === 0) {
$queryBuilder->orderBy($field, $direction);
} else {
$queryBuilder->addOrderBy($field, $direction);
}
++$i;
}
}
/**
* Order data
* @param array $sorting
* @return self
*/
public function sort(array $sorting)
{
$alias = current($this->data_source->getDQLPart('from'))->getAlias();
if ($sorting) {
foreach ($sorting as $column => $sort) {
$this->data_source->orderBy("{$alias}.{$column}", $sort);
}
} else {
/**
* Has the statement already a order by clause?
*/
if (!$this->data_source->getDQLPart('orderBy')) {
$this->data_source->orderBy("{$alias}.{$this->primary_key}");
}
}
return $this;
}
/**
* Maps the specified criteria to the query builder
* @param QueryBuilder $qb The query builder
* @param mixed[string] $criteria The search criteria
* @param mixed[string] $ordering The result ordering
* @return \Doctrine\ORM\QueryBuilder
* @throws
*/
protected function mapCriteriaToBuilder(QueryBuilder $qb, array $criteria = [], array $ordering = [])
{
$method = 'orWhere';
//get the metadata
$metadata = $this->getClassMetadata();
foreach ($criteria as $propertyName => $property) {
if (is_array($property)) {
$propertyValue = $property['value'];
$comparator = $property['operator'];
} else {
$propertyValue = $property;
$comparator = 'eq';
}
//check the property exists on the entity (otherwise the query may contain injected DQL)
if (!$metadata->hasField($propertyName) && !$metadata->hasAssociation($propertyName)) {
throw new \InvalidArgumentException("Invalid property \"{$propertyName}\".");
}
//add the criteria
if ($comparator === 'eq') {
$qb->{$method}($qb->expr()->eq('e.' . $propertyName, $qb->expr()->literal($propertyValue)));
} elseif ($comparator === 'neq') {
$qb->{$method}($qb->expr()->neq('e.' . $propertyName, $qb->expr()->literal($propertyValue)));
} elseif ($method === 'lt') {
$qb->{$method}($qb->expr()->lt('e.' . $propertyName, $qb->expr()->literal($propertyValue)));
} elseif ($comparator === 'lte') {
$qb->{$method}($qb->expr()->lte('e.' . $propertyName, $qb->expr()->literal($propertyValue)));
} elseif ($comparator === 'gt') {
$qb->{$method}($qb->expr()->gt('e.' . $propertyName, $qb->expr()->literal($propertyValue)));
} elseif ($comparator === 'gte') {
$qb->{$method}($qb->expr()->gte('e.' . $propertyName, $qb->expr()->literal($propertyValue)));
} elseif ($comparator === 'like') {
$qb->{$method}($qb->expr()->like('e.' . $propertyName, $qb->expr()->literal($propertyValue)));
} elseif ($comparator === 'notlike') {
$qb->{$method}($qb->expr()->notlike('e.' . $propertyName, $qb->expr()->literal($propertyValue)));
}
}
//order by each property
foreach ($ordering as $propertyName => $propertyValue) {
$qb->orderBy('e.' . $propertyName, $propertyValue);
}
return $qb;
}
/**
* Sort data
* @param Sorting $sorting
* @return static
*/
public function sort(Sorting $sorting)
{
if (is_callable($sorting->getSortCallback())) {
call_user_func($sorting->getSortCallback(), $this->data_source, $sorting->getSort());
return $this;
}
$sort = $sorting->getSort();
if (!empty($sort)) {
foreach ($sort as $column => $order) {
$this->data_source->addOrderBy($this->checkAliases($column), $order);
}
} else {
/**
* Has the statement already a order by clause?
*/
if (!$this->data_source->getDQLPart('orderBy')) {
$this->data_source->orderBy($this->checkAliases($this->primary_key));
}
}
return $this;
}
public function buildSelectQuery(QueryBuilder $queryBuilder, $parameters, $class, $sort = array(), $join = "", $joinParams = array())
{
if (!empty($joinParams)) {
foreach ($joinParams as $key => $joinParam) {
unset($parameters[$key]);
}
}
if (!empty($sort)) {
unset($parameters[$sort['key']]);
}
$from = "DraftBundle:" . $class;
$queryBuilder->select("c")->from($from, "c");
if (isset($parameters['limit'])) {
$limit = $parameters['limit'];
$queryBuilder->setMaxResults($limit);
if (isset($parameters['page'])) {
$page = ($parameters['page'] - 1) * $limit;
$queryBuilder->setFirstResult($page);
unset($parameters['page']);
}
unset($parameters['limit']);
}
$i = 0;
foreach ($parameters as $key => $value) {
if ($value === "") {
continue;
}
if ($i == 0) {
$queryBuilder->where("c.{$key} = :param{$i}")->setParameter(":param{$i}", "{$value}");
} else {
$queryBuilder->andWhere("c.{$key} = :param{$i}")->setParameter(":param{$i}", "{$value}");
}
$i++;
}
if (!empty($sort)) {
$queryBuilder->orderBy("c." . $sort['key'], $sort['value']);
}
return $queryBuilder;
}
private function queryOrderField(QueryBuilder $qb, $field, $sens = 'ASC')
{
$this->logger->debug('VALUE field: ' . $field);
switch ($field) {
case 'duration':
$fieldName = 'v.duration';
break;
case 'title':
$fieldName = 'mov.title';
break;
case 'create':
$fieldName = 'v.createDate';
break;
default:
$fieldName = null;
}
$this->logger->debug('VALUE fieldName: ' . $fieldName);
if (!is_null($fieldName)) {
$qb->orderBy($fieldName, $sens);
}
return $qb;
}
/**
* {@inheritdoc}
*
* @return array Array of hydrated elements. Empty if reached the end of collection or collection is empty.
*
* @throws \UnexpectedValueException Thrown if iterator incorrectly initialized.
*/
public function next()
{
if (\is_null($this->qb)) {
if (null === $this->originalQb) {
throw new \UnexpectedValueException("Query Builder object not specified.");
}
if (null === $this->iterateBy) {
throw new \UnexpectedValueException("IterateBy value not specified.");
}
$this->qb = clone $this->originalQb;
$this->qb->orderBy($this->iterateBy);
$this->qb->setMaxResults($this->pageSize);
}
$qb = clone $this->qb;
if ($this->currentPage++ > 0) {
$qb->andWhere($this->qb->expr()->gt($this->iterateBy, $this->lastValue));
}
$result = $qb->getQuery()->getResult($this->hydrationMode);
if (\count($result)) {
$method = $this->pullClosure;
$this->lastValue = $method($result[\count($result) - 1]);
}
return $result;
}
/**
* @param QueryBuilder $qb
* @param string $name
*/
protected function addQueryBuilderSort(QueryBuilder $qb, $name)
{
$alias = current($qb->getDQLPart('from'))->getAlias();
if (is_array($order = $this->getOrder($name))) {
$qb->orderBy($alias . '.' . $order['field'], $order['type']);
}
}
/**
* Builds the query for the given data request object
*
* @access public
* @param \Zepi\DataSource\Core\Entity\DataRequest $dataRequest
* @param \Doctrine\ORM\QueryBuilder $queryBuilder
* @param string $entity
* @param string $tableCode
*/
public function buildDataRequestQuery(DataRequest $dataRequest, QueryBuilder $queryBuilder, $entity, $tableCode)
{
$queryBuilder->select($tableCode)->from($entity, $tableCode);
$hasWhere = false;
$i = 1;
foreach ($dataRequest->getFilters() as $filter) {
$whereQuery = $tableCode . '.' . $filter->getFieldName() . ' ' . $filter->getMode() . ' :' . $i;
if ($hasWhere) {
$queryBuilder->andWhere($whereQuery);
} else {
$queryBuilder->where($whereQuery);
$hasWhere = true;
}
$queryBuilder->setParameter($i, $filter->getNeededValue());
$i++;
}
// Sorting
if ($dataRequest->hasSorting()) {
$mode = 'ASC';
if (in_array($dataRequest->getSortByDirection(), array('ASC', 'DESC'))) {
$mode = $dataRequest->getSortByDirection();
}
$queryBuilder->orderBy($tableCode . '.' . $dataRequest->getSortBy(), $mode);
}
// Offset
if ($dataRequest->hasRange()) {
$queryBuilder->setFirstResult($dataRequest->getOffset());
$queryBuilder->setMaxResults($dataRequest->getNumberOfEntries());
}
}
/**
* {@inheritDoc}
*/
public function execute()
{
$this->queryBuilder = $this->createQueryBuilder();
foreach ($this->fields as $field) {
$this->queryBuilder->addSelect($field->getSelect() . ' AS ' . $field->getName());
}
if ($this->limit != null) {
$this->queryBuilder->setMaxResults($this->limit)->setFirstResult($this->limit * ($this->page - 1));
}
if ($this->sortField != null) {
$this->queryBuilder->orderBy($this->sortField->getSelect(), $this->sortOrder);
}
return $this->queryBuilder->getQuery()->getArrayResult();
}
