/** * @param QueryBuilder $queryBuilder * @param \DateTime|null $date * * @return QueryBuilder */ protected function filterByActive(QueryBuilder $queryBuilder, \DateTime $date = null) { if (null === $date) { $date = new \Datetime(); } return $queryBuilder->where($queryBuilder->expr()->orX($queryBuilder->expr()->isNull($this->getPropertyName('startsAt')), $queryBuilder->expr()->lt($this->getPropertyName('startsAt'), ':date')))->andWhere($queryBuilder->expr()->orX($queryBuilder->expr()->isNull($this->getPropertyName('endsAt')), $queryBuilder->expr()->gt($this->getPropertyName('endsAt'), ':date')))->setParameter('date', $date); }
/** * 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); } } }
/** * Apply the ACL constraints to the specified query builder, using the permission definition * * @param QueryBuilder $queryBuilder The query builder * @param PermissionDefinition $permissionDef The permission definition * * @return Query */ public function apply(QueryBuilder $queryBuilder, PermissionDefinition $permissionDef) { $whereQueryParts = $queryBuilder->getDQLPart('where'); if (empty($whereQueryParts)) { $queryBuilder->where('1 = 1'); // this will help in cases where no where query is specified } $query = $this->cloneQuery($queryBuilder->getQuery()); $builder = new MaskBuilder(); foreach ($permissionDef->getPermissions() as $permission) { $mask = constant(get_class($builder) . '::MASK_' . strtoupper($permission)); $builder->add($mask); } $query->setHint('acl.mask', $builder->get()); $query->setHint(Query::HINT_CUSTOM_OUTPUT_WALKER, 'Kunstmaan\\AdminBundle\\Helper\\Security\\Acl\\AclWalker'); $rootEntity = $permissionDef->getEntity(); $rootAlias = $permissionDef->getAlias(); // If either alias or entity was not specified - use default from QueryBuilder if (empty($rootEntity) || empty($rootAlias)) { $rootEntities = $queryBuilder->getRootEntities(); $rootAliases = $queryBuilder->getRootAliases(); $rootEntity = $rootEntities[0]; $rootAlias = $rootAliases[0]; } $query->setHint('acl.root.entity', $rootEntity); $query->setHint('acl.extra.query', $this->getPermittedAclIdsSQLForUser($query)); $classMeta = $this->em->getClassMetadata($rootEntity); $entityRootTableName = $this->quoteStrategy->getTableName($classMeta, $this->em->getConnection()->getDatabasePlatform()); $query->setHint('acl.entityRootTableName', $entityRootTableName); $query->setHint('acl.entityRootTableDqlAlias', $rootAlias); return $query; }
protected function filterByActive(QueryBuilder $qb, \Datetime $date = null) { if (null === $date) { $date = new \Datetime(); } return $qb->where($qb->expr()->orX($qb->expr()->isNull($this->getAlias() . '.startsAt'), $qb->expr()->lt($this->getAlias() . '.startsAt', ':date')))->andWhere($qb->expr()->orX($qb->expr()->isNull($this->getAlias() . '.endsAt'), $qb->expr()->gt($this->getAlias() . '.endsAt', ':date')))->setParameter('date', $date); }
/** * @param PersonCriteria $criteria */ private function checkLastname(PersonCriteria $criteria) { if ($criteria->hasLastname()) { $this->qb->where('p.lastname = :lastname'); $this->qb->setParameter('lastname', $criteria->getLastname()); } }
/** * 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()); } }
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(); }
/** * {@inheritdoc} */ public function execute() { // emit listbuilder.create event $event = new ListBuilderCreateEvent($this); $this->eventDispatcher->dispatch(ListBuilderEvents::LISTBUILDER_CREATE, $event); $this->expressionFields = $this->getUniqueExpressionFieldDescriptors($this->expressions); // first create simplified id query // select ids with all necessary filter data $ids = $this->findIdsByGivenCriteria(); // if no results are found - return if (count($ids) < 1) { return []; } // now select all data $this->queryBuilder = $this->em->createQueryBuilder()->from($this->entityName, $this->entityName); $this->assignJoins($this->queryBuilder); // Add all select fields foreach ($this->selectFields as $field) { $this->queryBuilder->addSelect($field->getSelect() . ' AS ' . $field->getName()); } // group by $this->assignGroupBy($this->queryBuilder); // assign sort-fields $this->assignSortFields($this->queryBuilder); // use ids previously selected ids for query $this->queryBuilder->where($this->entityName . '.id IN (:ids)')->setParameter('ids', $ids); return $this->queryBuilder->getQuery()->getArrayResult(); }
public function manipulateQuery(QueryBuilder $query, $prefix = 'hv') { $query->where("{$prefix}.createdAt >= :startDate"); $query->andWhere("{$prefix}.createdAt <= :endDate"); $query->setParameters(['startDate' => $this->period->getStartDate(), 'endDate' => $this->period->getEndDate()]); return $query; }
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; }
/** * {@inheritdoc} */ protected function prepareQuery(QueryBuilder $query, $rootAlias, $alias, $itemsType) { $entityId = $this->requestParams->get(self::ENTITY_PARAM, 0); $query->where($rootAlias . '.mode <> :mode'); $query->setParameter('mode', ConfigModelManager::MODE_HIDDEN); $query->innerJoin($rootAlias . '.entity', 'ce', 'WITH', 'ce.id=' . $entityId); $query->addSelect('ce.id as entity_id'); return parent::prepareQuery($query, $rootAlias, $alias, $itemsType); }
/** * @param $columns \Sorien\DataGridBundle\Grid\Column\Column[] * @param $page int Page Number * @param $limit int Rows Per Page * @return \Sorien\DataGridBundle\Grid\Rows */ public function execute($columns, $page = 0, $limit = 0) { $this->query = $this->manager->createQueryBuilder($this->class); $this->query->from($this->class, self::TABLE_ALIAS); $where = $this->query->expr()->andx(); $sorted = false; foreach ($columns as $column) { $this->query->addSelect($this->getFieldName($column)); if ($column->isSorted() && !$column->isDefaultSort()) { $this->query->orderBy($this->getFieldName($column, false), $column->getOrder()); $sorted = true; } elseif (!$sorted && $column->isSorted() && $column->isDefaultSort()) { $this->query->orderBy($this->getFieldName($column, false), $column->getOrder()); } if ($column->isFiltered()) { if ($column->getFiltersConnection() == column::DATA_CONJUNCTION) { foreach ($column->getFilters() as $filter) { $operator = $this->normalizeOperator($filter->getOperator()); $where->add($this->query->expr()->{$operator}($this->getFieldName($column, false), $this->normalizeValue($filter->getOperator(), $filter->getValue()))); } } elseif ($column->getFiltersConnection() == column::DATA_DISJUNCTION) { $sub = $this->query->expr()->orx(); foreach ($column->getFilters() as $filter) { $operator = $this->normalizeOperator($filter->getOperator()); $sub->add($this->query->expr()->{$operator}($this->getFieldName($column, false), $this->normalizeValue($filter->getOperator(), $filter->getValue()))); } $where->add($sub); } $this->query->where($where); } } foreach ($this->joins as $alias => $field) { $this->query->leftJoin($field, $alias); } if ($page > 0) { $this->query->setFirstResult($page * $limit); } if ($limit > 0) { $this->query->setMaxResults($limit); } //call overridden prepareQuery or associated closure $query = $this->prepareQuery(clone $this->query); $items = $query->getQuery()->getResult(); // hydrate result $result = new Rows(); foreach ($items as $item) { $row = new Row(); foreach ($item as $key => $value) { $row->setField($key, $value); } //call overridden prepareRow or associated closure if (($modifiedRow = $this->prepareRow($row)) != null) { $result->addRow($modifiedRow); } } return $result; }
protected function addNameConstraint(QueryBuilder $query, $names) { if ($names) { if (is_array($names)) { $names = $this->slugify($names); $query->where($query->expr()->in('t.slug', $names)); } else { $query->andWhere('t.slug = :slug')->setParameter('slug', Urlizer::urlize($names)); } } return $query; }
/** * @param QueryBuilder $queryBuilder * @param array $criteria * @return $this */ public function applyCriteria($queryBuilder, array $criteria = array()) { if (!count($criteria)) { return $this; } $criteria = $this->resolver->resolveCriteria($criteria); foreach ($criteria['joins'] as $join => $alias) { $queryBuilder->leftJoin($join, $alias); } $queryBuilder->where($this->createQueryExpression($queryBuilder, $criteria['expressions'])); return $this; }
/** * @param QueryBuilder $qb * @return QueryBuilder */ protected function filter(QueryBuilder &$qb) { if (isset($this->conditions['timeToExpire'])) { $currentTimeStamp = time(); $timeToExpire = $this->conditions['timeToExpire']; $qb->where("{$currentTimeStamp} - p.created_date <= {$timeToExpire}"); unset($this->conditions['timeToExpire']); } //apply other filter (where field = value) if (is_array($this->conditions) && count($this->conditions) > 0) { foreach ($this->conditions as $key => $value) { if ($value) { $v = is_int($value) ? (int) $value : "'{$value}'"; $qb->andWhere("p.{$key} = {$v}"); } } } return $qb; }
/** * Recreate query builder and set state again. * * @return void */ public function __wakeup() { if ($this->constraint !== NULL) { $this->queryBuilder->where($this->constraint); } if (is_array($this->orderings)) { foreach ($this->orderings as $propertyName => $order) { $this->queryBuilder->addOrderBy($this->queryBuilder->getRootAlias() . '.' . $propertyName, $order); } } if (is_array($this->joins)) { foreach ($this->joins as $joinAlias => $join) { $this->queryBuilder->leftJoin($join, $joinAlias); } } $this->queryBuilder->setFirstResult($this->offset); $this->queryBuilder->setMaxResults($this->limit); $this->queryBuilder->setParameters($this->parameters); unset($this->parameters); }
public function getResult($itemsPerPage, $page, Result $result) { $parts = $this->query->getDQLParts(); $from = $parts['from'][0]; $itemsOnPage = clone $this->query; $itemsOnPage->select('DISTINCT(' . $from->getAlias() . '.id)')->addSelect('(' . $this->getNumRowsSubQuery() . ') as num_rows')->from($from->getFrom(), 'master')->setMaxResults($itemsPerPage)->setFirstResult($itemsPerPage * ($page - 1)); $itemsOnPage = $itemsOnPage->getQuery()->getScalarResult(); $totalResults = 0; $ids = array(); if ($itemsOnPage) { $totalResults = $itemsOnPage[0]['num_rows']; foreach ($itemsOnPage as $itemOnPage) { $ids[] = $itemOnPage[1]; } } $totalPages = ceil($totalResults / $itemsPerPage); $this->query->resetDQLPart('where'); $this->query->where($from->getAlias() . ' IN (:ids)')->setParameters(array('ids' => $ids)); $results = $this->query->getQuery()->getResult(); return $this->populateResult($result, $totalResults, $totalPages, $page, $results); }
public function buildSelectQuery(QueryBuilder $queryBuilder, $parameters, $class, $sort = array(), $join = "", $joinParams = array()) { if (!empty($joinParams)) { foreach ($joinParams as $key => $joinParam) { unset($parameters[$key]); } } if (!empty($sort)) { unset($parameters[$sort['key']]); } $from = "DraftBundle:" . $class; $queryBuilder->select("c")->from($from, "c"); if (isset($parameters['limit'])) { $limit = $parameters['limit']; $queryBuilder->setMaxResults($limit); if (isset($parameters['page'])) { $page = ($parameters['page'] - 1) * $limit; $queryBuilder->setFirstResult($page); unset($parameters['page']); } unset($parameters['limit']); } $i = 0; foreach ($parameters as $key => $value) { if ($value === "") { continue; } if ($i == 0) { $queryBuilder->where("c.{$key} = :param{$i}")->setParameter(":param{$i}", "{$value}"); } else { $queryBuilder->andWhere("c.{$key} = :param{$i}")->setParameter(":param{$i}", "{$value}"); } $i++; } if (!empty($sort)) { $queryBuilder->orderBy("c." . $sort['key'], $sort['value']); } return $queryBuilder; }
/** * Extend a query with a where constraint on the id of an entity. * * @param QueryBuilder $queryBuilder * @param mixed $id * @param EntityInterface $entity * @param bool $singleton * @param \ReflectionClass $entityClass * * @throws \RuntimeException */ public static function extendQueryWhereId(QueryBuilder $queryBuilder, $id, $entity, $singleton = false, \ReflectionClass $entityClass = null) { if (!$entityClass) { $entityClass = new \ReflectionClass($entity); } if ($entityClass->isSubclassOf('Contao\\Doctrine\\ORM\\EntityInterface')) { $keys = $entityClass->getMethod('entityPrimaryKeyNames')->invoke(null); } else { $keys = array('id'); } $idValues = is_array($id) ? $id : explode('|', $id); if (count($keys) != count($idValues)) { throw new \RuntimeException(sprintf('Key count of %d does not match id values count %d', count($keys), count($idValues))); } foreach ($keys as $index => $key) { $where = $queryBuilder->expr()->neq('e.' . $key, ':key' . $index); if ($index > 0 || !$singleton) { $queryBuilder->andWhere($where); } else { $queryBuilder->where($where); } $queryBuilder->setParameter(':key' . $index, $idValues[$index]); } }
/** * @param QueryBuilder $queryBuilder * @param \Claroline\CoreBundle\Entity\Widget\WidgetInstance[] $configs * * @return mixed */ private function addConfigurationFilterToQueryBuilder(QueryBuilder $queryBuilder, $configs) { $actionIndex = 0; foreach ($configs as $config) { $workspaceId = $config->getWidgetInstance()->getWorkspace()->getId(); $queryBuilder->where('workspace.id = :workspaceId')->setParameter('workspaceId', $workspaceId); if ($config->hasRestriction()) { $queryBuilder->andWhere('log.action IN (:actions)')->setParameter('actions', $config->getRestrictions()); } } return $queryBuilder; }
/** * set query where * * @param string $where * @param array $params * * @return Datatable */ public function setWhere($where, array $params = array()) { $this->queryBuilder->where($where); $this->queryBuilder->setParameters($params); return $this; }
/** * Adds WHERE clause to given query builder. * * @param Doctrine\ORM\QueryBuilder $qb Given query builder instance. * @param string $where The where clause to use when retrieving the collection (optional) (default=''). * * @return Doctrine\ORM\QueryBuilder query builder instance to be further processed */ protected function genericBaseQueryAddWhere(QueryBuilder $qb, $where = '') { if (!empty($where)) { $qb->where($where); } $showOnlyOwnEntries = (int) FormUtil::getPassedValue('own', ModUtil::getVar('MUVideo', 'showOnlyOwnEntries', 0), 'GETPOST'); if ($showOnlyOwnEntries == 1) { $uid = UserUtil::getVar('uid'); $qb->andWhere('tbl.createdUserId = :creator')->setParameter('creator', $uid); } return $qb; }
/** * This will clone the original query and apply acl * * @param QueryBuilder $queryBuilder Builder * @param array $permissions Permission mask * @param UserInterface $user User * * @return Query */ public function apply(QueryBuilder $queryBuilder, array $permissions = array("VIEW"), UserInterface $user = null) { $whereQueryParts = $queryBuilder->getDQLPart('where'); if (empty($whereQueryParts)) { $fromQueryParts = $queryBuilder->getDQLPart('from'); $firstFromQueryAlias = $fromQueryParts[0]->getAlias(); // this will help in cases where no where query is specified, // where query is required to walk in where clause $queryBuilder->where($firstFromQueryAlias . '.id IS NOT NULL'); } $query = $this->cloneQuery($queryBuilder->getQuery()); $builder = new MaskBuilder(); foreach ($permissions as $permission) { $mask = constant(get_class($builder) . '::MASK_' . strtoupper($permission)); $builder->add($mask); } $query->setHint('acl.mask', $builder->get()); $query->setHint(Query::HINT_CUSTOM_OUTPUT_WALKER, $this->aclWalkerClass); $entities = $queryBuilder->getRootEntities(); $query->setHint('acl.root.entities', $entities); $query->setHint('acl.extra.query', $this->getPermittedIdsACLSQLForUser($query, $queryBuilder, $user)); //$class = $this->getEntityManager()->getClassMetadata($entities[0]); //$entityRootTableName = $class->getQuotedTableName($this->getEntityManager()->getConnection()->getDatabasePlatform()); /** @var QuoteStrategy $quoteStrategy */ $quoteStrategy = $this->getEntityManager()->getConfiguration()->getQuoteStrategy(); $entityRootTableName = $quoteStrategy->getTableName($this->getEntityManager()->getClassMetadata($entities[0]), $this->getEntityManager()->getConnection()->getDatabasePlatform()); $entityRootAliases = $queryBuilder->getRootAliases(); $entityRootAlias = $entityRootAliases[0]; $query->setHint('acl.entityRootTableName', $entityRootTableName); $query->setHint('acl.entityRootTableDqlAlias', $entityRootAlias); return $query; }
/** * Build where statement and add to the query builder. * * @param \Doctrine\Orm\QueryBuilder $qb * @param mixed $where * @return $this */ protected function addWhere($qb, $where) { // process the $where if (is_string($where)) { // straight DQL string $qb->andWhere($where); } elseif (is_array($where) && count($where)) { // create where expression $whereExp = $qb->expr()->andx(); $params = array(); // index for the parameters $i = 0; // loop through all the clauses supplied foreach ($where as $col => $val) { if (is_array($val) && (!isset($val['value']) || is_string($val['value']) && strlen($val['value']) == 0) || is_string($val) && (!$val || strlen($val) == 0)) { // skip if invalid value; continue; } // check if we've been provided with an operator as well as a value if (!is_array($val)) { $operator = Expr\Comparison::EQ; $val = $val; } elseif (count($val) == 1) { $operator = Expr\Comparison::EQ; $val = end($val); } else { $operator = isset($val['operator']) ? $val['operator'] : Expr\Comparison::EQ; $val = array_key_exists('value', $val) ? $val['value'] : array(); } // set the alias to the default $alias = $this->alias; // if col relates to a relation i.e. Role.id // then perform a join and set up the alias and column names if (strpos($col, '.') !== false) { $parts = explode('.', $col); $col = array_pop($parts); $par = $this->alias; foreach ($parts as $rel) { $alias = strtolower($rel); $jt = new Expr\Join(Expr\Join::LEFT_JOIN, $par . '.' . $rel, $alias); if (!strpos($qb->getDql(), $jt->__toString()) !== false) { $qb->leftJoin($par . '.' . $rel, $alias); } $par = $alias; } } // process sets a little differently if (!is_array($val)) { $val = array($val); } if ($operator == 'regexp') { $whereExp->add("REGEXP(" . $alias . '.' . $col . ",'" . $val[0] . "') = 1"); } else { if ($operator == 'between') { if (count($val) == 2) { // $value should now be an array with 2 values $expr = new Expr(); $from = is_int($val[0]) ? $val[0] : "'" . $val[0] . "'"; $to = is_int($val[1]) ? $val[1] : "'" . $val[1] . "'"; $stmt = $expr->between($alias . '.' . $col, $from, $to); $whereExp->add($stmt); } } else { if ($operator == 'is') { $expr = new Expr(); $method = 'is' . ucfirst($val[0]); if (method_exists($expr, $method)) { $stmt = $expr->{$method}($alias . '.' . $col); $whereExp->add($stmt); } } else { // this holds the subquery for this field, each component being an OR $subWhereExp = $qb->expr()->orX(); foreach ($val as $value) { if ($value == null) { $cmpValue = 'NULL'; } else { $cmpValue = '?' . $i; // wrap LIKE values if ($operator == 'like') { $value = '%' . trim($value, '%') . '%'; } // add the parameter value into the parameters stack $params[$i] = $value; $i++; } $comparison = new Expr\Comparison($alias . '.' . $col, $operator, $cmpValue); $subWhereExp->add($comparison); } // add in the subquery as an AND $whereExp->add($subWhereExp); } } } } // only add where expression if actually has parts if (count($whereExp->getParts())) { $qb->where($whereExp); } // set the params from the where clause above $qb->setParameters($params); } return $this; }
/** * Searching / Filtering. * Construct the WHERE clause for server-side processing SQL query. * * @param QueryBuilder $qb * * @return $this */ private function setWhere(QueryBuilder $qb) { $globalSearch = $this->requestParams['search']['value']; // global filtering if ('' != $globalSearch) { $orExpr = $qb->expr()->orX(); foreach ($this->columns as $key => $column) { if (true === $this->isSearchColumn($column)) { $searchField = $this->searchColumns[$key]; if (true === $this->isPostgreSQLConnection) { $searchField = $this->cast($searchField, $column); } $orExpr->add($qb->expr()->like($searchField, '?' . $key)); $qb->setParameter($key, '%' . $globalSearch . '%'); } } $qb->where($orExpr); } // individual filtering if (true === $this->individualFiltering) { $andExpr = $qb->expr()->andX(); $i = 100; foreach ($this->columns as $key => $column) { if (true === $this->isSearchColumn($column)) { $filter = $column->getFilter(); $searchField = $this->searchColumns[$key]; $searchValue = $this->requestParams['columns'][$key]['search']['value']; if ('' != $searchValue && 'null' != $searchValue) { if (true === $this->isPostgreSQLConnection) { $searchField = $this->cast($searchField, $column); } $andExpr = $filter->addAndExpression($andExpr, $qb, $searchField, $searchValue, $i); } } } if ($andExpr->count() > 0) { $qb->andWhere($andExpr); } } return $this; }
/** * @param QueryBuilder $queryBuilder */ public function adaptQueryBuilder(QueryBuilder $queryBuilder) { $queryBuilder->where('1=1'); }
/** * @param QueryBuilder $qb * @param string $alias */ protected function applyClosedSecurityModel(QueryBuilder $qb, $alias) { $qb->where("{$alias}.tenantOwnerId = :tenantOwnerId")->andWhere("{$alias}.tenantCreatorId = :tenantCreatorId")->setParameters([':tenantOwnerId' => $this->tenant->getTenantOwnerId(), ':tenantCreatorId' => $this->tenant->getTenantCreatorId()]); }
/** * Configure the WHERE clause for the Doctrine QueryBuilder if any searches are specified * * @param QueryBuilder The Doctrine QueryBuilder object */ public function setWhere(QueryBuilder $qb) { // Global filtering if (isset($this->search) && !empty($this->search)) { $orExpr = $qb->expr()->orX(); for ($i = 0; $i < count($this->parameters); $i++) { if (isset($this->request['bSearchable_' . $i]) && $this->request['bSearchable_' . $i] == "true") { $qbParam = "sSearch_global_{$this->associations[$i]['entityName']}_{$this->associations[$i]['fieldName']}"; $orExpr->add($qb->expr()->like($this->associations[$i]['fullName'], ":{$qbParam}")); $qb->setParameter($qbParam, "%" . $this->request['sSearch'] . "%"); } } $qb->where($orExpr); } // Individual column filtering $andExpr = $qb->expr()->andX(); for ($i = 0; $i < count($this->parameters); $i++) { if (isset($this->request['bSearchable_' . $i]) && $this->request['bSearchable_' . $i] == "true" && !empty($this->request['sSearch_' . $i])) { $qbParam = "sSearch_single_{$this->associations[$i]['entityName']}_{$this->associations[$i]['fieldName']}"; $andExpr->add($qb->expr()->like($this->associations[$i]['fullName'], ":{$qbParam}")); $qb->setParameter($qbParam, "%" . $this->request['sSearch_' . $i] . "%"); } } if ($andExpr->count() > 0) { $qb->andWhere($andExpr); } }
/** * add where to querybuilder. * * @param QueryBuilder $qb * @param array $where * * @return QueryBuilder */ private function addWhere($qb, $where) { $and = $qb->expr()->andX(); foreach ($where as $k => $v) { $and->add($qb->expr()->eq($k, $v)); } $qb->where($and); return $qb; }
/** * Searching / Filtering. * Construct the WHERE clause for server-side processing SQL query. * * @param QueryBuilder $qb * * @return $this */ private function setWhere(QueryBuilder $qb) { $globalSearch = $this->requestParams['search']['value']; // global filtering if ('' != $globalSearch) { $orExpr = $qb->expr()->orX(); foreach ($this->columns as $key => $column) { if (true === $this->isSearchColumn($column)) { $searchField = $this->searchColumns[$key]; $orExpr->add($qb->expr()->like($searchField, '?' . $key)); $qb->setParameter($key, '%' . $globalSearch . '%'); } } $qb->where($orExpr); } // individual filtering if (true === $this->individualFiltering) { $andExpr = $qb->expr()->andX(); $i = 100; foreach ($this->columns as $key => $column) { if (true === $this->isSearchColumn($column)) { $searchType = $column->getSearchType(); $searchField = $this->searchColumns[$key]; $searchValue = $this->requestParams['columns'][$key]['search']['value']; $searchRange = $this->requestParams['columns'][$key]['name'] === 'daterange'; if ('' != $searchValue && 'null' != $searchValue) { if ($searchRange) { list($_dateStart, $_dateEnd) = explode(' - ', $searchValue); $dateStart = new \DateTime($_dateStart); $dateEnd = new \DateTime($_dateEnd); $dateEnd->setTime(23, 59, 59); $k = $i + 1; $andExpr->add($qb->expr()->between($searchField, '?' . $i, '?' . $k)); $qb->setParameter($i, $dateStart->format('Y-m-d H:i:s')); $qb->setParameter($k, $dateEnd->format('Y-m-d H:i:s')); $i += 2; } else { $andExpr = $this->addCondition($andExpr, $qb, $searchType, $searchField, $searchValue, $i); $i++; } } } } if ($andExpr->count() > 0) { $qb->andWhere($andExpr); } } return $this; }