public function filters(QueryBuilder $qb, $key, $val) { switch ($key) { case 'history': if ($val) { $orx = $qb->expr()->orX(); $orx->add('s.fk = :fk'); $orx->add('t.fk = :fk'); $qb->andWhere($orx); $qb->setParameter('fk', intval($val)); } break; case 'class': $orx = $qb->expr()->orX(); $orx->add('s.class = :class'); $orx->add('t.class = :class'); $qb->andWhere($orx); $qb->setParameter('class', $val); break; case 'blamed': if ($val === 'null') { $qb->andWhere($qb->expr()->isNull('a.blame')); } else { // this allows us to safely ignore empty values // otherwise if $qb is not changed, it would add where the string is empty statement. $qb->andWhere($qb->expr()->eq('b.fk', ':blame')); $qb->setParameter('blame', $val); } break; default: // if user attemps to filter by other fields, we restrict it throw new \Exception("filter not allowed"); } }
/** * @param array $ids * @param QueryBuilder $qbr * @return Query\Expr\Comparison|Query\Expr\Orx */ private function buildIdsCondition(array $ids, QueryBuilder $qbr) { $cond = $qbr->expr()->eq(1, 0); foreach ($ids as $row) { $cond = $qbr->expr()->orX($cond, sprintf('%s.id=\'%s\'', static::TABLE_ALIAS, (string) $row)); } return $cond; }
/** * {@inheritdoc} */ public function expr() { if ($this->expressionBuilder === null) { $this->expressionBuilder = new OrmExpressionBuilder($this->qb->expr()); } return $this->expressionBuilder; }
/** * @param QueryBuilder $qb * @param string $field * @param string $operator * @param string $value * @param int $i * * @throws WebServerLogException * * @return \Doctrine\ORM\Query\Expr\Andx|\Doctrine\ORM\Query\Expr\Comparison|\Doctrine\ORM\Query\Expr\Func */ private function matchOperator(QueryBuilder $qb, $field, $operator, $value, $i = 0) { // TODO expose operators logic into methods, or use appropriate design pattern switch ($operator) { case 'eq': case 'gt': case 'lt': $placeholder = ':' . $this->fieldName . '_' . $i; $qb->setParameter($placeholder, $value); return $qb->expr()->{$operator}($field, $placeholder); case 'regex': $placeholder = ':regexp_' . $this->fieldName . '_' . $i; $qb->setParameter($placeholder, $value); return $qb->expr()->andX("REGEXP({$field}, {$placeholder}) = true"); case 'like': $placeholder = ':like_' . $this->fieldName . '_' . $i; $qb->setParameter($placeholder, '%' . $value . '%'); return $qb->expr()->like($field, $placeholder); case 'between': list($from, $to) = explode(',', $value); $qb->setParameter('from_' . $i, $from); $qb->setParameter('to_' . $i, $to); return $qb->expr()->between($field, ':from_' . $i, ':to_' . $i); default: throw WebServerLogException::unknownFilterOperator($operator); } }
public function buildCount(QueryBuilder $queryBuilder) : Query { if (!empty($this->name)) { $queryBuilder->where($queryBuilder->expr()->like('o.name.name', ':name'))->setParameter('name', '%' . $this->name . '%'); } return $queryBuilder->select($queryBuilder->expr()->count('o.id'))->from(Organization::class, 'o')->leftJoin('o.organizationMembers', 'om')->leftJoin('o.owners', 'ow')->andWhere($queryBuilder->expr()->orX($queryBuilder->expr()->eq('om.userId', ':userId'), $queryBuilder->expr()->eq('ow.userId', ':userId')))->setParameter('userId', $this->userId->id())->getQuery(); }
/** * @param QueryBuilder $qb * @param array $criteria * @param array $orderBy * @param int $limit * @param int $offset * * @return QueryBuilder */ protected function attachCriteriaToQueryBuilder(QueryBuilder $qb, $criteria, $orderBy, $limit, $offset) { if (array_key_exists('alerts', $criteria)) { $ids = is_array($criteria['alerts']) ? $criteria['alerts'] : [$criteria['alerts']]; $qb->join('x.alerts', 'al'); $qb->andWhere($qb->expr()->in('al.id', ':alerts')); $qb->setParameter(':alerts', $ids); } //cleanup all the possible relationship filters unset($criteria['alerts']); if (count($criteria)) { foreach ($criteria as $key => $value) { $values = is_array($value) ? $value : [$value]; $qb->andWhere($qb->expr()->in("x.{$key}", ":{$key}")); $qb->setParameter(":{$key}", $values); } } if (empty($orderBy)) { $orderBy = ['id' => 'ASC']; } if (is_array($orderBy)) { foreach ($orderBy as $sort => $order) { $qb->addOrderBy('x.' . $sort, $order); } } if ($offset) { $qb->setFirstResult($offset); } if ($limit) { $qb->setMaxResults($limit); } return $qb; }
/** * Extend a query with a where constraint on the id of an entity. * * @param QueryBuilder $queryBuilder * @param mixed $id * @param EntityInterface $entity * @param bool $standalone * @param \ReflectionClass $entityClass * * @throws \RuntimeException */ public static function extendQueryWhereId(QueryBuilder $queryBuilder, EntityInterface $entity, $standalone = false, \ReflectionClass $entityClass = null) { /** @var EntityAccessor $entityAccessor */ $entityAccessor = $GLOBALS['container']['doctrine.orm.entityAccessor']; if (!$entityClass) { $entityClass = new \ReflectionClass($entity); } if ($entityClass->isSubclassOf('Contao\\Doctrine\\ORM\\EntityInterface')) { $keys = $entityClass->getMethod('entityPrimaryKeyNames')->invoke(null); } else { $keys = array('id'); } foreach ($keys as $index => $key) { $value = $entityAccessor->getProperty($entity, $key); if ($value !== null) { $where = $queryBuilder->expr()->neq('e.' . $key, ':key' . $index); $queryBuilder->setParameter(':key' . $index, $value); } else { $where = $queryBuilder->expr()->isNotNull('e.' . $key); } if ($index > 0 || !$standalone) { $queryBuilder->andWhere($where); } else { $queryBuilder->where($where); } } }
/** * @param string $column * @param mixed $value * @return void */ public function whereRootFieldEq($column, $value) { $qualified = $this->aliases->get($this->getRootId()) . '.' . $column; $param = ':' . $this->aliases->get($this->getRootId()) . $column; $this->builder->andWhere($this->builder->expr()->eq($qualified, $param)); $this->builder->setParameter($param, $value); }
/** * @param QueryBuilder $qb * @param array $criteria * @param array $orderBy * @param int $limit * @param int $offset * * @return QueryBuilder */ protected function attachCriteriaToQueryBuilder(QueryBuilder $qb, $criteria, $orderBy, $limit, $offset) { if (array_key_exists('courses', $criteria)) { $ids = is_array($criteria['courses']) ? $criteria['courses'] : [$criteria['courses']]; $qb->join('p.programYears', 'c_programYear'); $qb->join('c_programYear.cohort', 'c_cohort'); $qb->join('c_cohort.courses', 'c_course'); $qb->andWhere($qb->expr()->in('c_course.id', ':courses')); $qb->setParameter(':courses', $ids); } if (array_key_exists('sessions', $criteria)) { $ids = is_array($criteria['sessions']) ? $criteria['sessions'] : [$criteria['sessions']]; $qb->join('p.programYears', 'se_programYear'); $qb->join('se_programYear.cohort', 'se_cohort'); $qb->join('se_cohort.courses', 'se_course'); $qb->join('se_course.sessions', 'se_session'); $qb->andWhere($qb->expr()->in('se_session.id', ':sessions')); $qb->setParameter(':sessions', $ids); } if (array_key_exists('terms', $criteria)) { $ids = is_array($criteria['terms']) ? $criteria['terms'] : [$criteria['terms']]; $qb->join('p.programYears', 't_programYear'); $qb->join('t_programYear.terms', 't_term'); $qb->andWhere($qb->expr()->in('t_term.id', ':terms')); $qb->setParameter(':terms', $ids); } if (array_key_exists('schools', $criteria)) { $ids = is_array($criteria['schools']) ? $criteria['schools'] : [$criteria['schools']]; $qb->join('p.school', 'sc_school'); $qb->andWhere($qb->expr()->in('sc_school.id', ':schools')); $qb->setParameter(':schools', $ids); } unset($criteria['schools']); unset($criteria['courses']); unset($criteria['sessions']); unset($criteria['terms']); if (count($criteria)) { foreach ($criteria as $key => $value) { $values = is_array($value) ? $value : [$value]; $qb->andWhere($qb->expr()->in("p.{$key}", ":{$key}")); $qb->setParameter(":{$key}", $values); } } if (empty($orderBy)) { $orderBy = ['id' => 'ASC']; } if (is_array($orderBy)) { foreach ($orderBy as $sort => $order) { $qb->addOrderBy('p.' . $sort, $order); } } if ($offset) { $qb->setFirstResult($offset); } if ($limit) { $qb->setMaxResults($limit); } return $qb; }
/** * 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(); }
protected function applyCriteria(QueryBuilder $queryBuilder, array $criteria = null) { if (isset($criteria['groups'])) { $queryBuilder->innerJoin($this->getAlias() . '.machine', 'm', 'WITH', $this->getAlias() . '.machine = m.id')->innerJoin('m.groups', 'g', 'WITH', $queryBuilder->expr()->andX($queryBuilder->expr()->in('g.id', $criteria['groups']))); unset($criteria['groups']); } parent::applyCriteria($queryBuilder, $criteria); }
/** * @param QueryBuilder $query * @param $source * @param $locale * @param bool $resetJoin */ public function addQueryTranslationSupport(QueryBuilder $query, $source, $locale, $resetJoin = true) { if ($resetJoin) { $query->resetDQLPart('join'); } $query->leftJoin($source->getTableAlias() . '.translations', '_translations', 'WITH', $query->expr()->orX($query->expr()->eq('_translations.locale', ':locale'), $query->expr()->isNull('_translations.id'))); $query->setParameter('locale', $locale); }
private function addQueryFilter(QueryBuilder $query, $filter) { $expr = $query->expr(); /*$query->orWhere($expr->eq('entity.id', ':filter_int'));*/ $query->andWhere($query->expr()->orX($expr->like('entity.code', ':filter_str'), $expr->like('entity.manufacturer', ':filter_str'), $expr->like('entity.model', ':filter_str'))); /*$query->setParameter('filter_int', $filter);*/ $query->setParameter('filter_str', '%' . $filter . '%'); return $query; }
public function setUp() { $entityManager = $this->getMockForAbstractClass(EntityManagerInterface::class); $expressionBuilder = new Expr(); $entityManager->expects($this->any())->method('getExpressionBuilder')->willReturn($expressionBuilder); $this->queryBuilder = new QueryBuilder($entityManager); /** \Doctrine\ORM\Query\Expr */ $expression = $this->queryBuilder->expr(); $this->userSpecificationFactory = new UserSpecificationFactory($expression); }
protected function prepareEmailCondition(QueryBuilder $qb, PropertyEmailInterface $condition, $alias) { if ($condition->getEmail()) { $qb->andWhere($qb->expr()->eq($alias . '.email', ':email'))->setParameter('email', $condition->getEmail()); } else { if ($condition->getEmailNotSpecified()) { $qb->andWhere($qb->expr()->isNull($alias . '.email')); } } }
/** * @param QueryBuilder $qb * @param string $qbFieldName * @param string $qbParameterName * @param array $configuration * @return \Doctrine\ORM\Query\Expr\Base */ protected function getFieldExpr(QueryBuilder $qb, $qbFieldName, $qbParameterName, array $configuration) { $fieldExpr = $qb->expr()->eq($qbFieldName, $qbParameterName); $options = $configuration[Configuration::DISCOVERY_OPTIONS_KEY]; if (!empty($options[Configuration::DISCOVERY_EMPTY_KEY])) { $fieldExpr = $qb->expr()->orX($fieldExpr, $qb->expr()->eq($qbFieldName, ':emptyValue'), $qb->expr()->isNull($qbFieldName)); $qb->setParameter('emptyValue', ''); } return $fieldExpr; }
/** * Add new condition to remove ace by share scope * * @param QueryBuilder $qb * @param Composite $expr * @param string $scope * * @throws UnknownShareScopeException */ protected function addExprByShareScope(QueryBuilder $qb, Composite $expr, $scope) { if ($scope === Share::SHARE_SCOPE_USER) { $expr->add($qb->expr()->eq('asid.username', 'true')); } elseif ($scope === Share::SHARE_SCOPE_BUSINESS_UNIT) { $expr->add($qb->expr()->like('asid.identifier', $qb->expr()->literal('Oro\\\\Bundle\\\\OrganizationBundle\\\\Entity\\\\BusinessUnit%'))); } else { throw new UnknownShareScopeException($scope); } }
public function projectFilters(QueryBuilder $qb, $key, $val) { switch ($key) { case 'p.name': if ($val) { $qb->andWhere($qb->expr()->like('p.name', "'%{$val}%'")); } break; case 'p.hoursSpent': switch ($val) { case 'lessThan10': $qb->andWhere($qb->expr()->lt('p.hoursSpent', $qb->expr()->literal(10))); break; case 'upTo20': $qb->andWhere($qb->expr()->lte('p.hoursSpent', $qb->expr()->literal(20))); break; case 'moreThan2weeks': $qb->andWhere($qb->expr()->gte('p.hoursSpent', $qb->expr()->literal(80))); break; case 'overDeadline': $qb->andWhere($qb->expr()->gt('p.hoursSpent', 'p.deadline')); break; } break; case 'l.code': $qb->andWhere($qb->expr()->eq('l.code', ':code')); $qb->setParameter('code', $val); break; default: // if user attemps to filter by other fields, we restrict it throw new \Exception("filter not allowed"); } }
/** * Prepare join to attribute condition with current locale and scope criterias * * @param AbstractAttribute $attribute the attribute * @param string $joinAlias the value join alias * * @return string */ public function prepareCondition(AbstractAttribute $attribute, $joinAlias) { $condition = $joinAlias . '.attribute = ' . $attribute->getId(); if ($attribute->isLocalizable()) { $condition .= ' AND ' . $joinAlias . '.locale = ' . $this->qb->expr()->literal($this->context->getLocaleCode()); } if ($attribute->isScopable()) { $condition .= ' AND ' . $joinAlias . '.scope = ' . $this->qb->expr()->literal($this->context->getScopeCode()); } return $condition; }
public function addFilter(QueryBuilder &$qb, $id, $data) { switch ($data['comparator']) { case self::COMP_BEFORE: $qb->andWhere($qb->expr()->lte($this->identifier . '.' . $this->field, ':var_' . $id))->setParameter('var_' . $id, $data['value']); break; case self::COMP_AFTER: $qb->andWhere($qb->expr()->gte($this->identifier . '.' . $this->field, ':var_' . $id))->setParameter('var_' . $id, $data['value']); break; } }
/** * Quick search * * @return \ZfTable\Source\DoctrineQueryBuilder */ protected function search() { if ($search = $this->getParamAdapter()->getQuickSearch()) { foreach ($this->getTable()->getHeaders() as $k => $v) { $column = isset($v['column']) ? $v['column'] : $k; if (isset($v['tableAlias'])) { $this->query->orWhere($this->query->expr()->like($v['tableAlias'] . '.' . $column, "'%" . $search . "%'")); } } } return $this; }
/** * Apply to given query builder object additional conditions * for integrate activity lists from inheritance targets * * @param QueryBuilder $qb * @param string $entityClass * @param integer $entityId */ public function applyInheritanceActivity(QueryBuilder $qb, $entityClass, $entityId) { if (!$this->hasInheritances($entityClass)) { return; } $inheritanceTargets = $this->getInheritanceTargetsRelations($entityClass); foreach ($inheritanceTargets as $key => $inheritanceTarget) { $alias = 'ta_' . $key; $qb->leftJoin('activity.' . $inheritanceTarget['targetClassAlias'], $alias); $qb->orWhere($qb->expr()->andX($qb->expr()->andX($qb->expr()->in($alias . '.id', $this->getSubQuery($inheritanceTarget['targetClass'], $inheritanceTarget['path'], $entityId, $key)->getDQL())))); } }
/** * * @return boolean */ public function process() { $this->form->submit($this->request); if ($this->form->isValid()) { $formData = $this->form->getData(); if ($name = (string) $formData->name) { $this->qb->andWhere($this->qb->expr()->like('t.name', ':name'))->setParameter('name', '%' . $name . '%'); } return true; } return false; }
/** * @param QueryBuilder $queryBuilder * @param array $criteria */ protected function applyCriteria(QueryBuilder $queryBuilder, array $criteria = []) { foreach ($criteria as $property => $value) { $name = $this->getPropertyName($property); if (null === $value) { $queryBuilder->andWhere($queryBuilder->expr()->isNull($name)); } elseif (is_array($value)) { $queryBuilder->andWhere($queryBuilder->expr()->in($name, $value)); } elseif ('' !== $value) { $parameter = str_replace('.', '_', $property); $queryBuilder->andWhere($queryBuilder->expr()->eq($name, ':' . $parameter))->setParameter($parameter, $value); } } }
public function areImagesValid(ExecutionContext $context) { $captured_ids = array_map(function ($image) { return $image->getId(); }, $this->images->toArray()); $property_path = $context->getPropertyPath() . '.images'; if (!count($captured_ids)) { $context->addViolationAt($property_path, 'Please select at least one image!', array(), null); return; } $count = $this->query_builder->andWhere($this->query_builder->expr()->in('i.id', $captured_ids))->select('COUNT(i.id)')->getQuery()->getSingleScalarResult(); if (!$count) { $context->addViolation('Please select images from the list!', array(), null); } }
protected function applyCriteria(QueryBuilder $queryBuilder, array $criteria = null) { if (null === $criteria) { return; } foreach ($criteria as $property => $value) { if (null === $value) { $queryBuilder->andWhere($queryBuilder->expr()->isNull($this->getPropertyName($property))); } elseif (!is_array($value)) { $queryBuilder->andWhere($queryBuilder->expr()->eq($this->getPropertyName($property), ':' . $property))->setParameter($property, $value); } else { $queryBuilder->andWhere($queryBuilder->expr()->in($this->getPropertyName($property), $value)); } } }
/** * Adds the where clause accordingly to the choosed null management. * * @param QueryBuilder $queryBuilder * @param string $property * @param string $parameter * @param string $value * @param int|null $nullManagement */ private function addWhere(QueryBuilder $queryBuilder, $property, $parameter, $value, $nullManagement) { $queryParameter = sprintf('date_%s_%s', $parameter, $property); $where = sprintf('o.%s %s= :%s', $property, self::PARAMETER_BEFORE === $parameter ? '<' : '>', $queryParameter); $queryBuilder->setParameter($queryParameter, new \DateTime($value)); if (null === $nullManagement || self::EXCLUDE_NULL === $nullManagement) { $queryBuilder->andWhere($where); return; } if (self::PARAMETER_BEFORE === $parameter && self::INCLUDE_NULL_BEFORE === $nullManagement || self::PARAMETER_AFTER === $parameter && self::INCLUDE_NULL_AFTER === $nullManagement) { $queryBuilder->andWhere($queryBuilder->expr()->orX($where, $queryBuilder->expr()->isNull(sprintf('o.%s', $property)))); return; } $queryBuilder->andWhere($queryBuilder->expr()->andX($where, $queryBuilder->expr()->isNotNull(sprintf('o.%s', $property)))); }
/** * Apply custom ACL checks * * @param QueryBuilder $qb */ public function applyAcl(QueryBuilder $qb) { $user = $this->securityFacade->getLoggedUser(); $organization = $this->securityFacade->getOrganization(); $mailboxIds = $this->doctrine->getRepository('OroEmailBundle:Mailbox')->findAvailableMailboxIds($user, $organization); $uoCheck = $qb->expr()->andX($qb->expr()->eq('eu.owner', ':owner'), $qb->expr()->eq('eu.organization ', ':organization')); if (!empty($mailboxIds)) { $qb->andWhere($qb->expr()->orX($uoCheck, $qb->expr()->in('eu.mailboxOwner', ':mailboxIds'))); $qb->setParameter('mailboxIds', $mailboxIds); } else { $qb->andWhere($uoCheck); } $qb->setParameter('owner', $user->getId()); $qb->setParameter('organization', $organization->getId()); }
/** * @param QueryBuilder $qb * @param bool $includeView Trigger to include the documents with type == view * @param Contact $contact * * @return QueryBuilder $qb */ public function filterForAccess(QueryBuilder $qb, Contact $contact, $includeView = false) { //Filter based on the type access type $subSelect = $this->_em->createQueryBuilder(); $subSelect->select('type'); $subSelect->from('Project\\Entity\\Document\\Type', 'type'); $subSelect->join('type.access', 'access'); if (!$includeView) { $subSelect->andWhere($qb->expr()->in('access.access', array_merge_recursive([strtolower(Access::ACCESS_PUBLIC)], $contact->getRoles()))); } else { $subSelect->andWhere($qb->expr()->orX($qb->expr()->in('access.access', array_merge_recursive([strtolower(Access::ACCESS_PUBLIC)], $contact->getRoles())), $qb->expr()->in('type.view', DocumentType::VIEW))); } $qb->andWhere($qb->expr()->in('d.type', $subSelect->getDQL())); return $qb; }
/** * Build the query. * * @param QueryBuilder &$qb * @param array $parameters * * @return QueryBuilder */ public function buildQuery(QueryBuilder $qb, array $parameters) { //Handle single tag if (!is_array($parameters['tags'])) { $parameters['tags'] = [$parameters['tags']]; } //clean the parameters from the blank value foreach ($parameters['tags'] as $index => $parameter) { //the blank value is removed if ($parameter === '') { unset($parameters['tags'][$index]); } } if (count($parameters['tags']) > 0) { if (array_key_exists('strict', $parameters)) { $repository = $this->getEntityManager()->getRepository('VictoireBlogBundle:Article'); foreach ($parameters['tags'] as $index => $tag) { $parameter = ':tag' . $index; $subquery = $repository->createQueryBuilder('article_' . $index)->join('article_' . $index . '.tags', 'tag_' . $index)->where('tag_' . $index . ' = ' . $parameter); $qb->andWhere($qb->expr()->in('main_item', $subquery->getDql()))->setParameter($parameter, $tag); } } else { $qb = $qb->join('main_item.tags', 't')->andWhere('t.id IN (:tags)')->setParameter('tags', $parameters['tags']); } } return $qb; }