/**
* @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;
}
