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;
}
