/**
* @return QueryBuilder
*/
private function buildBaseQuery()
{
$qb = $this->entityRepository->createQueryBuilder('z');
GathererHelper::addFiltersDataToQuery($this->filters, $qb);
GathererHelper::addAdditionalFilterDataToQuery($this->additionalFilters, $qb);
return $qb;
}
/**
* {@inheritDoc}
*/
public function findTopicsByCategory(CategoryInterface $category, $page = 0)
{
$qb = $this->repository->createQueryBuilder('t')->select('t, c, u')->join('t.category', 'c')->join('t.user', 'u')->where('c.id = :category')->andWhere('t.isDeleted = 0')->orderBy('t.lastPost', 'desc')->setParameter('category', $category->getId());
$pager = new Pager(new ProxyQuery($qb));
$pager->setPage($page);
return $pager;
}
/**
* {@inheritDoc}
*/
public function findPostsByTopic(TopicInterface $topic, $page = 1)
{
$qb = $this->repository->createQueryBuilder('p')->select('p, t, u')->join('p.topic', 't')->join('p.user', 'u')->where('t.id = :topic')->andWhere('p.isDeleted = 0')->orderBy('p.created', 'asc')->setParameter('topic', $topic->getId());
$pager = new Pager(new ProxyQuery($qb));
$pager->setLimit(25)->setPage($page);
return $pager;
}
/**
* @param QueryBuilder $qb
* @param string $orderStatus
*/
protected function addFilterByOrderStatus(QueryBuilder $qb, $orderStatus)
{
$aliases = $qb->getRootAliases();
$subQueryBuilder = $this->customerRepository->createQueryBuilder('c');
$subQueryBuilder->select('IDENTITY(c.account)')->join('c.orders', 'o', 'WITH', $subQueryBuilder->expr()->eq($subQueryBuilder->expr()->lower('o.status'), ':filteredOrderStatus'));
$qb->andWhere($qb->expr()->in($aliases[0] . '.account', $subQueryBuilder->getDQL()))->setParameter('filteredOrderStatus', $orderStatus);
}
public function createQueryBuilder($alias = null, $indexBy = null)
{
if ($alias === null) {
$alias = $this->getName();
}
return $this->baseRepository->createQueryBuilder($alias, $indexBy);
}
/**
* {@inheritdoc}
*/
public function findTextBySlugAndLocale($slug, $locale)
{
$qb = $this->repository->createQueryBuilder('text');
$qb->innerJoin('text.translations', 'translation', Join::WITH, $qb->expr()->eq('translation.lang', ':locale'))->setParameter('locale', $locale);
$qb->andWhere($qb->expr()->eq('text.slug', ':slug'))->setParameter('slug', $slug);
$qb->select('text, translation');
return $qb->getQuery()->getOneOrNullResult();
}
/**
* Возвращает базовый запрос на выборку
* @return \Doctrine\ORM\QueryBuilder
*/
private function getBaseQuery()
{
$result = $this->entityRepository->createQueryBuilder('currency');
if ($this->limit) {
$result->setMaxResults($this->limit);
}
return $result;
}
/**
* @param \Twig_Environment $twig
* @param string $alias
* @return \Twig_Template
* @throws \InvalidArgumentException
*/
public function renderBlock(\Twig_Environment $twig, $alias)
{
$block = $this->repo->createQueryBuilder('b')->where('b.alias = :alias')->setParameters(compact('alias'))->setMaxResults(1)->getQuery()->useResultCache(true)->setResultCacheId('cms_block.' . $alias)->getResult();
$block = current($block);
if (!$block) {
throw new \InvalidArgumentException(sprintf("CMS block '%s' could not be found", $alias));
}
return $twig->createTemplate($block->getContent());
}
/**
* {@inheritdoc}
*/
public function getQuery()
{
if (!$this->query) {
$qb = $this->categoryRepository->createQueryBuilder('c');
$qb->orderBy('c.root')->addOrderBy('c.left');
$this->query = $qb->getQuery();
}
return $this->query;
}
/**
* {@inheritdoc}
*/
public function find()
{
$qb = $this->repository->createQueryBuilder('s');
$qb->leftJoin('s.seoMetadata', 'sm');
$qb->setMaxResults(1);
$qb->select('s, sm');
$q = $qb->getQuery();
$q->useResultCache(true, 3600, SiteInterface::SITE);
return $q->getOneOrNullResult();
}
public function findVisible($organizer)
{
if (!$organizer instanceof UserInterface) {
$organizer = null;
}
$qb = $this->repo->createQueryBuilder('e');
$qb->join('e.calendar', 'c')->andWhere('e.organizer = :organizer')->orWhere('c.visibility = :calendarVisibility');
$qb->setParameters(array('organizer' => $organizer, 'calendarVisibility' => CalendarInterface::VISIBILITY_PUBLIC));
return $qb->getQuery()->execute();
}
/**
* @dataProvider provideApplyTestData
*/
public function testApply($properties, array $filterParameters, string $expected)
{
$request = Request::create('/api/dummies', 'GET', $filterParameters);
$requestStack = new RequestStack();
$requestStack->push($request);
$queryBuilder = $this->repository->createQueryBuilder('o');
$filter = new NumericFilter($this->managerRegistry, $requestStack, null, $properties);
$filter->apply($queryBuilder, new QueryNameGenerator(), $this->resourceClass);
$actual = $queryBuilder->getQuery()->getDQL();
$this->assertEquals($expected, $actual);
}
/**
* {@inheritdoc}
*/
public function findCommentTreeByCommentId($commentId, $sorter = null)
{
$qb = $this->repository->createQueryBuilder('c');
$qb->join('c.thread', 't')->where('LOCATE(:path, CONCAT(\'/\', CONCAT(c.ancestors, \'/\'))) > 0')->orderBy('c.ancestors', 'ASC')->setParameter('path', "/{$commentId}/");
$comments = $qb->getQuery()->execute();
if (!$comments) {
return array();
}
$sorter = $this->sortingFactory->getSorter($sorter);
$trimParents = current($comments)->getAncestors();
return $this->organiseComments($comments, $sorter, $trimParents);
}
/**
* {@inheritdoc}
*/
public function findNodeAllChildrenTranslationsByLang(NodeInterface $node, $lang)
{
$qb = $this->repository->createQueryBuilder('t');
$qb->innerJoin('t.node', 'n');
$qb->andWhere($qb->expr()->eq('t.lang', ':locale'));
$qb->setParameter('locale', $lang);
$qb->andWhere($qb->expr()->eq('n.root', ':root'))->setParameter('root', $node->getRoot());
$qb->andWhere($qb->expr()->gte('n.left', ':left'))->setParameter('left', $node->getLeft());
$qb->andWhere($qb->expr()->lte('n.right', ':right'))->setParameter('right', $node->getRight());
$qb->select('t');
return $qb->getQuery()->getResult();
}
/**
* {@inheritdoc}
*/
public function findExistingNodeTypes(TreeInterface $tree)
{
$qb = $this->repository->createQueryBuilder('n');
$qb->andWhere($qb->expr()->eq('n.tree', ':tree'))->setParameter('tree', $tree);
$qb->andWhere($qb->expr()->isNotNull('n.type'));
$qb->select('DISTINCT n.type');
$result = array();
foreach ($qb->getQuery()->getResult() as $row) {
$result[] = $row['type'];
}
return $result;
}
/**
* @param integer $id
* @return mixed
*/
public function get($id = null)
{
if ($id !== null) {
if ($this->crudManager instanceof CrudManagerInterface) {
return $this->crudManager->get($id);
} else {
$query = $this->source->createQueryBuilder('entity');
return $query->andWhere('entity.id = :id')->setParameter('id', $id)->getQuery()->getOneOrNullResult();
}
}
return null;
}
/**
* @param FormBuilderInterface $builder
* @param array $options
*/
public function buildForm(FormBuilderInterface $builder, array $options)
{
$qb = $this->repository->createQueryBuilder('d');
$qb->innerJoin('d.values', 'v');
/** @var FamilyInterface $family */
$family = $options['family'];
if ($family) {
$qb->andWhere('d.family = :family')->setParameter('family', $family->getCode());
if ($family->getAttributeAsLabel()) {
$qb->andWhere('v.attributeCode = :attributeCode')->setParameter('attributeCode', $family->getAttributeAsLabel()->getCode());
}
}
$builder->setAttribute('query-builder', $qb);
}
/**
* @dataProvider filterProvider
*/
public function testApply(array $filterParameters, array $query, $expected)
{
$request = Request::create('/api/dummies', 'GET', $query);
$requestStack = new RequestStack();
$requestStack->push($request);
$queryBuilder = $this->repository->createQueryBuilder('o');
$filter = new DateFilter($this->managerRegistry, $requestStack, $filterParameters['properties']);
$uniqid = $this->getFunctionMock('Dunglas\\ApiBundle\\Doctrine\\Orm\\Util', 'uniqid');
$uniqid->expects($this->any())->willReturn('123456abcdefg');
$filter->apply($this->resource, $queryBuilder);
$actual = strtolower($queryBuilder->getQuery()->getDQL());
$expected = strtolower($expected);
$this->assertEquals($expected, $actual, sprintf('Expected `%s` for this `%s %s` request', $expected, 'GET', $request->getUri()));
}
/**
* @param string $token
* @return bool
*/
protected function validate($token)
{
if (!$this->tokenFormatValidator->isValid($token)) {
return false;
}
$qb = $this->repository->createQueryBuilder('r');
$qb->select('1')->where('r.token = :t');
$qb->setParameter('t', $token);
if (!count($qb->getQuery()->getResult())) {
$this->error(self::INVALID);
return false;
}
return true;
}
public function total()
{
$metadata = $this->em->getClassMetadata($this->repository->getClassName());
$identifiers = $metadata->getIdentifierFieldNames();
$id = $identifiers[0];
return $this->repository->createQueryBuilder("q")->select("COUNT(q.{$id})")->getQuery()->getSingleScalarResult();
}
/**
* @param array $entityIds
* @return array
*/
protected function getEntitiesByIds(array $entityIds)
{
/** @var QueryBuilder $queryBuilder */
$queryBuilder = $this->entityRepository->createQueryBuilder('e');
$queryBuilder->where($queryBuilder->expr()->in('e.' . $this->idFieldName, $entityIds));
return $queryBuilder->getQuery()->getResult();
}
/**
* @param string $alias
* @return QueryBuilder
*/
public function getQueryBuilder($alias = 'e')
{
if (!$this->queryBuilder) {
$this->alias = $alias;
$this->queryBuilder = $this->repository->createQueryBuilder($alias);
}
return $this->queryBuilder;
}
/**
* Returns the next sort order value
*
* @param array $where
*
* @return int
*/
public function getNextSortOrder(array $where = array())
{
$queryBuild = $this->repository->createQueryBuilder("t")->select("MAX(t.sortOrder)");
// apply the where clauses
if (0 < count($completeWhere = $this->getCompleteWhere($where))) {
$queryParts = array();
$index = 0;
foreach ($completeWhere as $key => $value) {
$queryParts[] = "t.{$key} = :where_value_{$index}";
$queryBuild->setParameter("where_value_{$index}", $value);
$index++;
}
$queryBuild->where(implode(" AND ", $queryParts));
}
$currentMaxValue = $queryBuild->getQuery()->getSingleScalarResult();
return is_null($currentMaxValue) ? 0 : 1 + (int) $currentMaxValue;
}
/**
* @param string $status
* @param string $title
* @param string $description
* @param string $type
* @param string $priority
*
* @return array
*/
public function search($status = null, $title = null, $description = null, $type = null, $priority = null)
{
$builder = $this->repository->createQueryBuilder('t');
foreach (['status', 'type', 'priority'] as $field) {
if (null !== ${$field}) {
$builder->andWhere("t.{$field} = :{$field}");
$builder->setParameter($field, ${$field});
}
}
foreach (['title', 'description'] as $field) {
if (null !== ${$field}) {
$builder->andWhere("t.{$field} LIKE :{$field}");
$builder->setParameter($field, "%" . ${$field} . "%");
}
}
return $builder->getQuery()->execute();
}
/**
* {@inheritdoc}
*/
public function createQueryBuilder($alias)
{
$queryBuilder = parent::createQueryBuilder($alias);
/* @var $securityContext SecurityContext */
$securityContext = \AppKernel::getStaticContainer()->get('security.context');
if (!$securityContext->isGranted('ROLE_EDIT_ALL_CLIENTS')) {
$queryBuilder->where($alias . '.user = :user')->setParameter(':user', $securityContext->getToken()->getUser());
}
return $queryBuilder;
}
/**
* {@inheritdoc}
*/
public function createQueryBuilder($alias)
{
$queryBuilder = parent::createQueryBuilder($alias);
/* @var $securityContext SecurityContext */
$securityContext = \AppKernel::getStaticContainer()->get('security.context');
if (!$securityContext->isGranted('ROLE_SUPER_ADMIN')) {
$queryBuilder->where($alias . '.id != :admin_id')->setParameter(':admin_id', 1);
}
return $queryBuilder;
}
/**
* {@inheritdoc}
*/
public function findSystemSlugs()
{
$qb = $this->repository->createQueryBuilder('bz');
$qb->andWhere($qb->expr()->eq('bz.system', $qb->expr()->literal(1)))->select('bz.slug');
$result = $qb->getQuery()->getArrayResult();
$data = array();
foreach ($result as $row) {
$data[] = $row['slug'];
}
return $data;
}
/**
* Clears all cache records.
*
* @return boolean TRUE if cache is cleared FALSE otherwise
*/
public function clear()
{
try {
$this->_repository->createQueryBuilder('c')->delete()->where('1 = 1')->getQuery()->execute();
$this->resetCacheEntity();
} catch (\Exception $e) {
$this->log('warning', sprintf('Enable to clear cache : %s', $e->getMessage()));
return false;
}
return true;
}
protected function createQueryBuilder(SearchQuery $searchQuery)
{
$builder = $this->sourceRepository->createQueryBuilder('e')->select('distinct e');
if ($searchQuery->getLabels()) {
switch ($searchQuery->getMode()) {
case SearchQuery::MODE_OR:
$this->addLabelsOr($builder, $searchQuery);
break;
case SearchQuery::MODE_AND:
$this->addLabelsAnd($builder, $searchQuery);
break;
case SearchQuery::MODE_CATEGORIES:
$this->addLabelsCategories($builder, $searchQuery);
break;
default:
throw new \Exception('Invalid search query mode: ' . $searchQuery->getMode());
}
}
return $builder;
}
/**
* @dataProvider filterProvider
*/
public function testApply(array $filterParameters, array $query, $expected)
{
$request = Request::create('/api/dummies', 'GET', $query);
$requestStack = new RequestStack();
$requestStack->push($request);
$queryBuilder = $this->repository->createQueryBuilder('o');
$filter = new SearchFilter($this->managerRegistry, $requestStack, $this->iriConverter, $this->propertyAccessor, $filterParameters['properties']);
$uniqid = $this->getFunctionMock('Dunglas\\ApiBundle\\Doctrine\\Orm\\Util', 'uniqid');
$uniqid->expects($this->any())->willReturn('123456abcdefg');
$filter->apply($this->resource, $queryBuilder);
$actual = strtolower($queryBuilder->getQuery()->getDQL());
$expectedDql = strtolower($expected['dql']);
$this->assertEquals($expectedDql, $actual, sprintf('Expected `%s` for this `%s %s` request', $expectedDql, 'GET', $request->getUri()));
if (!empty($expected['parameters'])) {
foreach ($expected['parameters'] as $parameter => $expectedValue) {
$actualValue = $queryBuilder->getQuery()->getParameter($parameter)->getValue();
$this->assertEquals($expectedValue, $actualValue, sprintf('Expected `%s` for this `%s %s` request', var_export($expectedValue, true), 'GET', $request->getUri()));
}
}
}
