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 QueryBuilder $queryBuilder
  * @param Parameter $parameters
  * @return QueryBuilder
  */
 protected function generateQueryParamFilter(QueryBuilder $queryBuilder, Parameters $parameters)
 {
     if (isset($parameters['query']) && is_array($parameters['query'])) {
         $i = 1;
         foreach ($parameters['query'] as $query) {
             $type = '=';
             if ($query['type'] == 'like') {
                 $type = 'LIKE';
             }
             $whereString = sprintf('row.%s %s :%sValue%d', $query['field'], $type, $query['field'], $i);
             if (!isset($query['where']) || $query['where'] == 'and') {
                 $queryBuilder->andWhere($whereString);
             } else {
                 if ($query['where'] == 'or') {
                     $queryBuilder->orWhere($whereString);
                 }
             }
             $queryBuilder->setParameter(sprintf('%sValue%d', $query['field'], $i), $query['value']);
             $i++;
         }
     }
     if (isset($parameters['orderBy']) && is_array($parameters['orderBy'])) {
         foreach ($parameters['orderBy'] as $orderKey => $orderDirection) {
             $queryBuilder->addOrderBy('row.' . $orderKey, strtoupper($orderDirection));
         }
     }
     return $queryBuilder;
 }
 /**
  * @param FilterInterface $filter
  * @param FormInterface $form
  * @param QueryBuilder $qb
  * @param string $alias
  */
 public function handleForm(FilterInterface $filter, FormInterface $form, QueryBuilder $qb, $alias)
 {
     $data = $form->getData();
     if (!$data) {
         return;
     }
     $columns = $filter->getFullAttributeReferences($alias);
     if (!empty($data[DateRangeType::START_NAME])) {
         $startDate = $data[DateRangeType::START_NAME];
         $dql = [];
         foreach ($columns as $column) {
             $uid = uniqid('fromDate');
             $dql[] = "{$column} >= :{$uid}";
             $qb->setParameter($uid, $startDate);
         }
         $qb->andWhere(implode(' OR ', $dql));
     }
     if (!empty($data[DateRangeType::END_NAME])) {
         $endDate = $data[DateRangeType::END_NAME];
         $dql = [];
         foreach ($columns as $column) {
             $uid = uniqid('endDate');
             $dql[] = "{$column} <= :{$uid}";
             $qb->setParameter($uid, $endDate);
         }
         $qb->andWhere(implode(' OR ', $dql));
     }
 }
 /**
  * @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;
 }
Beispiel #5
0
 public function alterQueryBuilder(QueryBuilder $queryBuilder)
 {
     if ($this->year !== null) {
         $queryBuilder->andWhere('YEAR(transaction.date) = :year');
         $queryBuilder->setParameter(':year', $this->year);
     }
     if ($this->month && in_array($this->month, array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12))) {
         $queryBuilder->andWhere('MONTH(transaction.date) = :month');
         $queryBuilder->setParameter(':month', $this->month);
     }
     if ($this->processed != null && in_array($this->processed, array(0, 1))) {
         $queryBuilder->andWhere('transaction.is_processed = :processed');
         $queryBuilder->setParameter(':processed', $this->processed ? true : false);
     }
     if ($this->tag != null) {
         $queryBuilder->leftJoin('transaction.tags', 'tag');
         $queryBuilder->andWhere('tag.slug = :tag');
         $queryBuilder->setParameter(':tag', $this->tag);
     }
     if ($this->category !== null) {
         if ($this->category == 'empty') {
             $queryBuilder->andWhere('transaction.category IS NULL');
         } else {
             $queryBuilder->leftJoin('transaction.category', 'category');
             $queryBuilder->leftJoin('category.parent', 'parent');
             $queryBuilder->andWhere('category.slug = :category or parent.slug = :category');
             $queryBuilder->setParameter(':category', $this->category);
         }
     }
     return $queryBuilder;
 }
Beispiel #6
0
 /**
  * @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);
     }
 }
Beispiel #7
0
 /**
  * @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;
 }
 /**
  *  Returns a statement for an expression.
  *
  * @param QueryBuilder $queryBuilder
  *
  * @return string
  */
 public function getStatement(QueryBuilder $queryBuilder)
 {
     $paramName1 = $this->getFieldName() . $this->getUniqueId();
     $paramName2 = $this->getFieldName() . $this->getUniqueId();
     $queryBuilder->setParameter($paramName1, $this->getStart());
     $queryBuilder->setParameter($paramName2, $this->getEnd());
     return $this->field->getSelect() . ' BETWEEN :' . $paramName1 . ' AND :' . $paramName2;
 }
 private function whereToPublish(QueryBuilder $builder)
 {
     $builder->where('r.published = :published');
     $builder->setParameter('published', false);
     $builder->orWhere('p.modified = :modified');
     $builder->setParameter('modified', true);
     return $this;
 }
 /**
  * 
  * @param \Doctrine\ORM\QueryBuilder $qb
  * @return \Doctrine\ORM\QueryBuilder
  * 
  * filtre les résultats pour l'année en cours
  */
 public function whereAnneeEnCours(\Doctrine\ORM\QueryBuilder $qb)
 {
     $debut = new \DateTime(date('Y') . '-01-01');
     $fin = new \DateTime(date('Y') . '-12-31');
     $qb->andWhere('a.date BETWEEN :debut AND :fin');
     $qb->setParameter('debut', $debut);
     $qb->setParameter('fin', $fin);
     return $qb;
 }
Beispiel #11
0
 /**
  * Fonction applicant les filtres de type text
  * à notre querybuilder
  *
  * @param object $qb -- Doctrine\ORM\QueryBuilder
  * @param string $tableAlias -- l'alias de la table
  * @param string $field -- le champ cible
  * @param mixed $value -- la valeur a testé
  */
 public function matchFilter(QueryBuilder $qb, $tableAlias, $field, $operator, $value)
 {
     $qb->andWhere($tableAlias . '.' . $field . ' ' . $operator . ' :' . $tableAlias . '_' . $field);
     switch ($operator) {
         case 'LIKE':
             $qb->setParameter($tableAlias . '_' . $field, '%' . $value . '%');
             break;
         case '=':
             $qb->setParameter($tableAlias . '_' . $field, $value);
     }
 }
 public function buildQuery(\Doctrine\ORM\QueryBuilder $qb, $field, $parameter, $value)
 {
     if (null !== $value) {
         if (true === $this->options['multiple']) {
             $qb->andWhere($field . ' in (' . $parameter . ')');
             $qb->setParameter($parameter, explode(',', $value));
         } else {
             $qb->andWhere($field . ' = ' . $parameter);
             $qb->setParameter($parameter, $value);
         }
     }
 }
 /**
  * {@inheritdoc}
  */
 public function addAndExpression(Andx $andExpr, QueryBuilder $pivot, $searchField, $searchValue, &$i)
 {
     switch ($this->getSearchType()) {
         case 'like':
             $andExpr->add($pivot->expr()->like($searchField, '?' . $i));
             $pivot->setParameter($i, '%' . $searchValue . '%');
             break;
         case 'notLike':
             $andExpr->add($pivot->expr()->notLike($searchField, '?' . $i));
             $pivot->setParameter($i, '%' . $searchValue . '%');
             break;
         case 'eq':
             $andExpr->add($pivot->expr()->eq($searchField, '?' . $i));
             $pivot->setParameter($i, $searchValue);
             break;
         case 'neq':
             $andExpr->add($pivot->expr()->neq($searchField, '?' . $i));
             $pivot->setParameter($i, $searchValue);
             break;
         case 'lt':
             $andExpr->add($pivot->expr()->lt($searchField, '?' . $i));
             $pivot->setParameter($i, $searchValue);
             break;
         case 'lte':
             $andExpr->add($pivot->expr()->lte($searchField, '?' . $i));
             $pivot->setParameter($i, $searchValue);
             break;
         case 'gt':
             $andExpr->add($pivot->expr()->gt($searchField, '?' . $i));
             $pivot->setParameter($i, $searchValue);
             break;
         case 'gte':
             $andExpr->add($pivot->expr()->gte($searchField, '?' . $i));
             $pivot->setParameter($i, $searchValue);
             break;
         case 'in':
             $andExpr->add($pivot->expr()->in($searchField, '?' . $i));
             $pivot->setParameter($i, explode(',', $searchValue));
             break;
         case 'notIn':
             $andExpr->add($pivot->expr()->notIn($searchField, '?' . $i));
             $pivot->setParameter($i, explode(",", $searchValue));
             break;
         case 'isNull':
             $andExpr->add($pivot->expr()->isNull($searchField));
             break;
         case 'isNotNull':
             $andExpr->add($pivot->expr()->isNotNull($searchField));
             break;
     }
     $i++;
     return $andExpr;
 }
 /**
  * {@inheritdoc}
  */
 public function getStatement(QueryBuilder $queryBuilder)
 {
     $paramName = $this->getFieldName() . $this->getUniqueId();
     if ($this->getValue() === null) {
         return $this->field->getSelect() . ' ' . $this->convertNullComparator($this->getComparator());
     } elseif ($this->getComparator() === 'LIKE') {
         $queryBuilder->setParameter($paramName, '%' . $this->getValue() . '%');
     } else {
         $queryBuilder->setParameter($paramName, $this->getValue());
     }
     return $this->field->getSelect() . ' ' . $this->getComparator() . ' :' . $paramName;
 }
 /**
  * @param $filters
  * @throws \Exception
  */
 public function process(QueryBuilder $qb, Filters $filters)
 {
     $this->qb = $qb;
     foreach ($filters->toArray() as $key => $data) {
         if (array_key_exists($key, $this->accepted)) {
             $this->processFilter($key, $data);
         }
     }
     foreach ($this->getParams() as $key => $value) {
         $this->qb->setParameter($key, $value);
     }
     return $this->qb;
 }
 /**
  * {@inheritdoc}
  */
 public function addAndExpression(Andx $andExpr, QueryBuilder $pivot, $searchField, $searchValue, &$i)
 {
     list($_dateStart, $_dateEnd) = explode(' - ', $searchValue);
     $dateStart = new \DateTime($_dateStart);
     $dateEnd = new \DateTime($_dateEnd);
     $dateEnd->setTime(23, 59, 59);
     $k = $i + 1;
     $andExpr->add($pivot->expr()->between($searchField, '?' . $i, '?' . $k));
     $pivot->setParameter($i, $dateStart->format('Y-m-d H:i:s'));
     $pivot->setParameter($k, $dateEnd->format('Y-m-d H:i:s'));
     $i += 2;
     return $andExpr;
 }
 /**
  * @param QueryBuilder $queryBuilder
  */
 public function configureResultQueryBuilder(QueryBuilder $queryBuilder)
 {
     $title = $this->getTitle();
     if ($title !== null) {
         $queryBuilder->andWhere('entity.title LIKE :like_title');
         $queryBuilder->setParameter('like_title', '%' . $title . '%');
     }
     $createdAfter = $this->getCreatedAfter();
     if ($createdAfter !== null) {
         $queryBuilder->andWhere('entity.created >= :created_after');
         $queryBuilder->setParameter('created_after', $createdAfter);
     }
 }
 /**
  *
  * @return Array
  */
 protected function getSingleInstances(QueryBuilder $qb, \DateTime $dateFrom, \DateTime $dateTo)
 {
     $singleDateConditions = array();
     // case 1: event starts between selected dates
     $singleDateConditions[] = call_user_func_array(array($qb->expr(), 'andX'), array($qb->expr()->lte(':dateFrom', $qb->getRootAliases()[0] . '.dtStart'), $qb->expr()->lt($qb->getRootAliases()[0] . '.dtStart', ':dateTo')));
     // case 2: event starts before selected dateFrom but ends after selected dateFrom
     $singleDateConditions[] = call_user_func_array(array($qb->expr(), 'andX'), array($qb->expr()->lt($qb->getRootAliases()[0] . '.dtStart', ':dateFrom'), $qb->expr()->lt(':dateFrom', $qb->getRootAliases()[0] . '.dtEnd')));
     // or-combine the two cases for valid results
     $singleDateCondition = call_user_func_array(array($qb->expr(), 'orX'), $singleDateConditions);
     $qb->andWhere($qb->expr()->isNull($qb->getRootAliases()[0] . '.recurrenceRule'))->andWhere($singleDateCondition);
     $qb->setParameter('dateFrom', $dateFrom);
     $qb->setParameter('dateTo', $dateTo);
     return $qb->getQuery()->getResult();
 }
 /**
  * Adds joins and conditions to the QueryBuilder in order to only return entities the given user is allowed to see.
  *
  * @param QueryBuilder $queryBuilder The instance of the QueryBuilder to adjust
  * @param UserInterface $user The user for which the access control is checked
  * @param int $permission The permission mask for which is checked
  * @param string $entityClass The class of the entity of which the access control is checked
  * @param string $entityAlias The alias of the entity used in the query builder
  */
 protected function addAccessControl(QueryBuilder $queryBuilder, UserInterface $user, $permission, $entityClass, $entityAlias)
 {
     $queryBuilder->leftJoin(AccessControl::class, 'accessControl', 'WITH', 'accessControl.entityClass = :entityClass AND accessControl.entityId = ' . $entityAlias . '.id');
     $queryBuilder->leftJoin('accessControl.role', 'role');
     $queryBuilder->andWhere('BIT_AND(accessControl.permissions, :permission) = :permission OR accessControl.permissions IS NULL');
     $roleIds = [];
     foreach ($user->getRoleObjects() as $role) {
         $roleIds[] = $role->getId();
     }
     $queryBuilder->andWhere('role.id IN(:roleIds) OR role.id IS NULL');
     $queryBuilder->setParameter('roleIds', $roleIds);
     $queryBuilder->setParameter('entityClass', $entityClass);
     $queryBuilder->setParameter('permission', $permission);
 }
Beispiel #20
0
 /**
  * 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 PersonCriteria $criteria
  */
 private function checkDob(PersonCriteria $criteria)
 {
     if ($criteria->hasDob()) {
         $this->qb->andWhere('p.dob = :dob');
         $this->qb->setParameter('dob', $criteria->getDob());
     }
 }
Beispiel #22
0
 /**
  * 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());
     }
 }
Beispiel #23
0
 /**
  * @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);
 }
 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");
     }
 }
 protected function filterBy($columnName, $value)
 {
     $column = $this->parseToQueryBuilderFormat($columnName);
     $paramName = str_replace('.', '_', $column);
     if (is_array($value)) {
         if (!array_key_exists('value', $value) || !isset($value['comparision'])) {
             return;
         }
         if ($value['comparision'] === 'like') {
             $this->queryBuilder->andWhere($column . ' like :' . $paramName);
             $this->queryBuilder->setParameter($paramName, '%' . $value['value'] . '%');
             return;
         }
         if ($value['comparision'] === 'not') {
             if ($value['value'] === null) {
                 $this->queryBuilder->andWhere($column . ' is not null');
             } else {
                 $this->queryBuilder->andWhere($column . ' != :' . $paramName);
                 $this->queryBuilder->setParameter($paramName, '%' . $value['value'] . '%');
             }
             return;
         }
     }
     if ($value === null) {
         $this->queryBuilder->andWhere($column . ' IS NULL');
         return;
     }
     $this->queryBuilder->andWhere($column . ' = :' . $paramName);
     $this->queryBuilder->setParameter($paramName, $value);
 }
Beispiel #26
0
 /**
  * @param EntryCriteria $criteria
  */
 private function checkNote(EntryCriteria $criteria)
 {
     if ($criteria->hasNote()) {
         $this->qb->andWhere('e.note = :note');
         $this->qb->setParameter('note', $criteria->getNote());
     }
 }
 /**
  * Build where clause
  * 
  * @param FilterContainer $filters Available filters
  * @param string          $value   Value
  * @param string          $field   Field name
  * 
  * @return void
  */
 protected function buildWhere(FilterContainer $filters, $value, $field = null)
 {
     $where = '';
     foreach ($filters->all() as $key => $filter) {
         if (null !== $field && $field != $filter['field']) {
             continue;
         }
         if (null === $field) {
             $key .= '_';
         }
         switch ($filter['type']) {
             case Paginator::FILTER_TYPE_STARTS_WITH:
                 $comparisonOperator = 'LIKE';
                 $this->qb->setParameter($key, $value . '%');
                 break;
             case Paginator::FILTER_TYPE_ENDS_WITH:
                 $comparisonOperator = 'LIKE';
                 $this->qb->setParameter($key, '%' . $value);
                 break;
             case Paginator::FILTER_TYPE_CONTAINS:
                 $comparisonOperator = 'LIKE';
                 $this->qb->setParameter($key, '%' . $value . '%');
                 break;
             case Paginator::FILTER_TYPE_EQUALS:
                 // no break
             // no break
             default:
                 $comparisonOperator = '=';
                 $this->qb->setParameter($key, $value);
                 break;
         }
         $where .= ' OR ' . $filter['field'] . ' ' . $comparisonOperator . ' :' . $key;
     }
     return substr($where, 4);
 }
 /**
  * @param array        $extra
  * @param QueryBuilder $builder
  */
 protected function queryExtraConditions(array $extra, QueryBuilder $builder)
 {
     foreach ($extra as $key => $extraValue) {
         $builder->andWhere("e.{$key} = :" . $this->prepareParam($key));
         $builder->setParameter($this->prepareParam($key), $extraValue);
     }
 }
 /**
  * @param array        $extra
  * @param QueryBuilder $builder
  */
 protected function queryExtraConditions(array $extra, QueryBuilder $builder)
 {
     foreach ($extra as $key => $extraValue) {
         $builder->andWhere("e.{$key} = :{$key}");
         $builder->setParameter($key, $extraValue);
     }
 }
 /**
  * Adds additional category-related conditions to query
  *
  * @param QueryBuilder $queryBuilder
  */
 private function addCategoryConditions(QueryBuilder $queryBuilder)
 {
     // show products from enabled categories
     $expression = $queryBuilder->expr()->eq('categories.enabled', ':enabled2');
     $queryBuilder->andWhere($expression);
     $queryBuilder->setParameter('enabled2', true);
 }