/**
* @param array $conditions
* @param PagingProperties $pagingProperties
* @param ApiUser $user
* @return \Doctrine\Common\Collections\Collection|static
* @throws \Exception
*/
public function filter(array &$conditions, PagingProperties $pagingProperties, $user = null)
{
$qb = $this->_em->createQueryBuilder();
$orderByField = sprintf('%s.%s', self::SELECT_ALIAS, $pagingProperties->getSort());
$offset = ($pagingProperties->getPage() - 1) * $pagingProperties->getLimit();
$qb->select(self::SELECT_ALIAS)->from($this->_entityName, self::SELECT_ALIAS);
foreach ($conditions as $condition) {
$whereExpression = $this->buildWhereExpression($qb, $condition);
$qb->orWhere($whereExpression);
}
if (!$this->userState->isOroUser()) {
$publicComments = sprintf('%s.private = false', self::SELECT_ALIAS);
$qb->andWhere($publicComments);
}
$qb->addOrderBy($orderByField, $pagingProperties->getOrder());
$qb->setFirstResult($offset);
$qb->setMaxResults($pagingProperties->getLimit());
$query = $qb->getQuery();
try {
$result = $query->getResult(Query::HYDRATE_OBJECT);
} catch (\Exception $e) {
$result = null;
}
return $result;
}
/**
* @param array $conditions
* @param PagingProperties $pagingProperties
* @return \Doctrine\ORM\QueryBuilder
*/
protected function createFilterQuery(array $conditions, PagingProperties $pagingProperties)
{
$qb = $this->_em->createQueryBuilder();
$orderByField = sprintf('%s.%s', self::SELECT_ALIAS, $pagingProperties->getSort());
$offset = ($pagingProperties->getPage() - 1) * $pagingProperties->getLimit();
$qb->select(self::SELECT_ALIAS)->from($this->_entityName, self::SELECT_ALIAS);
foreach ($conditions as $condition) {
$whereExpression = $this->buildWhereExpression($qb, $condition);
$qb->andWhere($whereExpression);
}
$qb->addOrderBy($orderByField, $pagingProperties->getOrder());
$qb->setFirstResult($offset);
$qb->setMaxResults($pagingProperties->getLimit());
return $qb;
}
/**
* Sorting the mysql result.
* Implemented because Doctrine ORM not support select from subQuery
*
* @param $result
* @param PagingProperties $pagingProperties
* @return array
*/
public function sortByKey($result, PagingProperties $pagingProperties)
{
if (!$result || empty($result)) {
return $result;
}
usort($result, function ($a, $b) use($pagingProperties) {
$reflectionA = new \ReflectionClass($a);
$reflectionB = new \ReflectionClass($b);
$sortBy = $pagingProperties->getSort();
$orderBy = $pagingProperties->getOrder();
if (!$reflectionA->getProperty($sortBy) || !$reflectionB->getProperty($sortBy)) {
return 0;
}
$propertyA = $reflectionA->getProperty($sortBy);
$propertyB = $reflectionB->getProperty($sortBy);
$propertyA->setAccessible(true);
$propertyB->setAccessible(true);
$valueA = (string) $a->getKey();
$valueB = (string) $b->getKey();
if (is_string($valueA) || is_string($valueB)) {
$sortableArray = array($valueA, $valueB);
$originalSortableArray = $sortableArray;
asort($sortableArray);
if ($orderBy == 'desc') {
return $sortableArray !== $originalSortableArray;
} else {
return $sortableArray === $originalSortableArray;
}
}
return 0;
});
return $result;
}
/**
* @param array $conditions
* @param PagingProperties $pagingProperties
* @return \Doctrine\ORM\QueryBuilder
*/
protected function orderByTicketKey(array $conditions, PagingProperties $pagingProperties)
{
$qb = $this->_em->createQueryBuilder();
$offset = ($pagingProperties->getPage() - 1) * $pagingProperties->getLimit();
$qb->select(self::SELECT_ALIAS)->addSelect('CONCAT(b.key, \'-\', ' . self::SELECT_ALIAS . '.sequenceNumber) AS HIDDEN ticketKey')->from('DiamanteDeskBundle:Ticket', self::SELECT_ALIAS)->join(self::SELECT_ALIAS . '.branch', 'b');
foreach ($conditions as $condition) {
$whereExpression = $this->buildWhereExpression($qb, $condition);
$qb->andWhere($whereExpression);
}
$qb->addOrderBy('ticketKey', $pagingProperties->getOrder());
$qb->setFirstResult($offset);
$qb->setMaxResults($pagingProperties->getLimit());
return $qb;
}
