private function applyJoins(QueryBuilderFilter $filter, QueryBuilder $queryBuilder) { /** @var Join[] $previousJoins */ $previousJoins = array(); foreach ($filter->getJoins() as $join) { $joinEntity = $join->getEntityClassName(); $joinAlias = $join->getAlias(); if (!array_key_exists($joinEntity, $previousJoins)) { $previousJoins[$joinEntity] = $join; switch (get_class($join)) { case LeftJoinWith::class: /** @var LeftJoinWith $join */ $queryBuilder->leftJoin($joinEntity, $joinAlias, \Doctrine\ORM\Query\Expr\Join::WITH, $join->getWithCondition()); break; case LeftJoin::class: $queryBuilder->leftJoin($joinEntity, $joinAlias); break; case JoinWith::class: /** @var JoinWith $join */ $queryBuilder->join($joinEntity, $joinAlias, \Doctrine\ORM\Query\Expr\Join::WITH, $join->getWithCondition()); break; case Join::class: $queryBuilder->join($joinEntity, $joinAlias); break; default: throw new Exception('Invalid JOIN type: ' . get_class($join)); } } else { if ($previousJoins[$joinEntity]->getAlias() !== $joinAlias) { throw new InconsistentJoinsException($joinEntity, $previousJoins[$joinEntity]->getAlias(), $joinAlias); } } } }
/** * Applies a custom query to the QueryBuilder * * @param QueryBuilder $qb The query builder * @param ManagerFilter $filter The query filter */ protected function applyCustomQuery(QueryBuilder $qb, ManagerFilter $filter) { // Apply special handling for non-direct fields in relations, where the frontend has no idea about. foreach ($filter->getSorters() as $sorter) { switch ($sorter->getSortField()) { case "q.part_name": $qb->join("q.part", "p"); $sorter->setSortField("p.name"); break; case "q.user_id": $qb->leftJoin("q.user", "u"); $sorter->setSortField("u.username"); break; case "q.direction": $sorter->setSortField("q.dateTime"); break; case "q.storageLocation_name": $qb->join("q.part", "p")->join("p.storageLocation", "st"); $sorter->setSortField("st.name"); break; default: break; } } }
/** * @param array $joins * @return AbstractQuery */ public function join(array $joins) { $this->assertCanBeModified(); foreach ($joins as $attribute => $prefix) { $this->queryBuilder->join($attribute, $prefix); } return $this; }
/** * @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('cohort', $criteria)) { $criteria['cohorts'][] = $criteria['cohort']; unset($criteria['cohort']); } if (array_key_exists('cohorts', $criteria)) { $ids = is_array($criteria['cohorts']) ? $criteria['cohorts'] : [$criteria['cohorts']]; $qb->join('l.cohort', 'l_cohort'); $qb->andWhere($qb->expr()->in('l_cohort.id', ':cohorts')); $qb->setParameter(':cohorts', $ids); } if (array_key_exists('parent', $criteria)) { $criteria['parents'][] = $criteria['parent']; unset($criteria['parent']); } if (array_key_exists('parents', $criteria)) { $ids = is_array($criteria['parents']) ? $criteria['parents'] : [$criteria['parents']]; if (in_array(null, $ids)) { $ids = array_diff($ids, [null]); $qb->andWhere('l.parent IS NULL'); } if (count($ids)) { $qb->join('l.parent', 'l_parent'); $qb->andWhere($qb->expr()->in('l_parent.id', ':parents')); $qb->setParameter(':parents', $ids); } } //cleanup all the possible relationship filters unset($criteria['cohorts']); unset($criteria['parents']); if (count($criteria)) { foreach ($criteria as $key => $value) { $values = is_array($value) ? $value : [$value]; $qb->andWhere($qb->expr()->in("l.{$key}", ":{$key}")); $qb->setParameter(":{$key}", $values); } } if (empty($orderBy)) { $orderBy = ['id' => 'ASC']; } if (is_array($orderBy)) { foreach ($orderBy as $sort => $order) { $qb->addOrderBy('l.' . $sort, $order); } } if ($offset) { $qb->setFirstResult($offset); } if ($limit) { $qb->setMaxResults($limit); } return $qb; }
/** * Will do JOIN only if there is no such join already. * * @param QueryBuilder $queryBuilder * @param string $join * @param string $alias * * @return QueryBuilder */ protected function join(QueryBuilder $queryBuilder, $join, $alias) { list($entity) = explode('.', $join); $joinParts = $queryBuilder->getDQLPart('join'); if (!array_key_exists($entity, $joinParts)) { return $queryBuilder->join($join, $alias); } $joinParts = $joinParts[$entity]; $existingJoin = array_filter($joinParts, function (Join $joinObj) use($alias, $join) { return $joinObj->getAlias() == $alias && $joinObj->getJoin() == $join; }); if ([] != $existingJoin) { return $queryBuilder; } return $queryBuilder->join($join, $alias); }
public function implement(QueryBuilder $queryBuilder) { foreach ($this->getValue() as $key => $alias) { $key = Utils::parseKey($key); $queryBuilder->join($key, $alias); } }
/** * {@inheritdoc} * * Orders collection by properties. The order of the ordered properties is the same as the order specified in the * query. * For each property passed, if the resource does not have such property or if the order value is different from * `asc` or `desc` (case insensitive), the property is ignored. */ public function apply(ResourceInterface $resource, QueryBuilder $queryBuilder) { $request = $this->requestStack->getCurrentRequest(); if (null === $request) { return; } $properties = $this->extractProperties($request); foreach ($properties as $property => $order) { if (!$this->isPropertyEnabled($property) || !$this->isPropertyMapped($property, $resource)) { continue; } if (empty($order) && isset($this->properties[$property])) { $order = $this->properties[$property]; } $order = strtoupper($order); if (!in_array($order, ['ASC', 'DESC'])) { continue; } $alias = 'o'; $field = $property; if ($this->isPropertyNested($property)) { $propertyParts = $this->splitPropertyParts($property); $parentAlias = $alias; foreach ($propertyParts['associations'] as $association) { $alias = QueryNameGenerator::generateJoinAlias($association); $queryBuilder->join(sprintf('%s.%s', $parentAlias, $association), $alias); $parentAlias = $alias; } $field = $propertyParts['field']; } $queryBuilder->addOrderBy(sprintf('%s.%s', $alias, $field), $order); } }
/** * @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; }
/** * {@inheritdoc} */ public function apply(ResourceInterface $resource, QueryBuilder $queryBuilder) { $request = $this->requestStack->getCurrentRequest(); if (null === $request) { return; } foreach ($this->extractProperties($request) as $property => $values) { // Expect $values to be an array having the period as keys and the date value as values if (!$this->isPropertyEnabled($property) || !$this->isPropertyMapped($property, $resource) || !$this->isDateField($property, $resource) || !is_array($values)) { continue; } $alias = 'o'; $field = $property; if ($this->isPropertyNested($property)) { $propertyParts = $this->splitPropertyParts($property); $parentAlias = $alias; foreach ($propertyParts['associations'] as $association) { $alias = QueryNameGenerator::generateJoinAlias($association); $queryBuilder->join(sprintf('%s.%s', $parentAlias, $association), $alias); $parentAlias = $alias; } $field = $propertyParts['field']; } $nullManagement = isset($this->properties[$property]) ? $this->properties[$property] : null; if (self::EXCLUDE_NULL === $nullManagement) { $queryBuilder->andWhere($queryBuilder->expr()->isNotNull(sprintf('%s.%s', $alias, $field))); } if (isset($values[self::PARAMETER_BEFORE])) { $this->addWhere($queryBuilder, $alias, $field, self::PARAMETER_BEFORE, $values[self::PARAMETER_BEFORE], $nullManagement); } if (isset($values[self::PARAMETER_AFTER])) { $this->addWhere($queryBuilder, $alias, $field, self::PARAMETER_AFTER, $values[self::PARAMETER_AFTER], $nullManagement); } } }
/** * @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('o.courses', 'course'); $qb->andWhere($qb->expr()->in('course.id', ':courses')); $qb->setParameter(':courses', $ids); } if (array_key_exists('programYears', $criteria)) { $ids = is_array($criteria['programYears']) ? $criteria['programYears'] : [$criteria['programYears']]; $qb->join('o.programYears', 'programYear'); $qb->andWhere($qb->expr()->in('programYear.id', ':programYears')); $qb->setParameter(':programYears', $ids); } if (array_key_exists('sessions', $criteria)) { $ids = is_array($criteria['sessions']) ? $criteria['sessions'] : [$criteria['sessions']]; $qb->join('o.sessions', 'session'); $qb->andWhere($qb->expr()->in('session.id', ':sessions')); $qb->setParameter(':sessions', $ids); } unset($criteria['courses']); unset($criteria['programYears']); unset($criteria['sessions']); if (count($criteria)) { foreach ($criteria as $key => $value) { $values = is_array($value) ? $value : [$value]; $qb->andWhere($qb->expr()->in("o.{$key}", ":{$key}")); $qb->setParameter(":{$key}", $values); } } if (empty($orderBy)) { $orderBy = ['id' => 'ASC']; } if (is_array($orderBy)) { foreach ($orderBy as $sort => $order) { $qb->addOrderBy('o.' . $sort, $order); } } if ($offset) { $qb->setFirstResult($offset); } if ($limit) { $qb->setMaxResults($limit); } return $qb; }
/** * @param QueryBuilder $queryBuilder * @param array $joinParts * @param string $alias * @param string $join * @param string $joinType * * @return QueryBuilder */ protected function filterExistingJoins(QueryBuilder $queryBuilder, $joinParts, $alias, $join, $joinType) { $existingJoin = array_filter($joinParts, function (Join $joinObj) use($alias, $join, $joinType) { return $joinObj->getJoinType() == $joinType && $joinObj->getAlias() == $alias && $joinObj->getJoin() == $join; }); if ([] != $existingJoin) { return $queryBuilder; } return $queryBuilder->join($join, $alias); }
protected function order() { $column = $this->getParamAdapter()->getColumn(); $order = $this->getParamAdapter()->getOrder(); if (!$column) { return; } $header = $this->getTable()->getHeader($column); $tableAlias = $header ? $header->getTableAlias() : 'q'; if (false === strpos($tableAlias, '.')) { $tableAlias = $tableAlias . '.' . $column; } if ($header->getOrderJoin()) { $joinAlias = 'j' . str_replace('.', '_', $tableAlias); $this->query->join($tableAlias, $joinAlias); $this->query->orderBy($joinAlias . '.' . $header->getOrderJoin(), $order); } else { $this->query->orderBy($tableAlias, $order); } }
/** * @param string $field * @param mixed $value * @return array */ protected function setQueryParam($field) { $index = 0; $rootAlias = $this->qb->getRootAliases()[$index]; if (false !== strpos($field, '.')) { list($assoc, $subField) = explode('.', $field); } else { $assoc = $field; } $meta = $this->getClassMetadata($index); if ($meta->hasAssociation($assoc)) { $targetClass = $meta->getAssociationTargetClass($assoc); if ($meta->isCollectionValuedAssociation($assoc)) { $alias = "{$rootAlias}_{$assoc}"; if (!in_array($alias, $this->qb->getAllAliases())) { $this->qb->leftJoin("{$rootAlias}.{$assoc}", $alias); } $assoc = $alias; } else { $alias = "{$rootAlias}_{$assoc}"; if (!in_array($alias, $this->qb->getAllAliases())) { $this->qb->join("{$rootAlias}.{$assoc}", $alias); } $assoc = $alias; } $em = $this->qb->getEntityManager(); $targetMeta = $em->getClassMetadata($targetClass); if (isset($subField) && !$targetMeta->hasField($subField) && $targetMeta->isInheritanceTypeJoined()) { foreach ($targetMeta->discriminatorMap as $alias => $class) { $joinedMeta = $em->getClassMetadata($class); if ($joinedMeta->hasField($subField)) { if (!in_array($alias, $this->qb->getAllAliases())) { $this->qb->leftJoin($joinedMeta->getName(), $alias, 'WITH', "{$alias}.id = {$assoc}.id"); } $assoc = $alias; } } } $alias = isset($subField) ? "{$assoc}.{$subField}" : $assoc; } else { $alias = "{$rootAlias}.{$field}"; } $values = array_slice(func_get_args(), 1); if ($values) { $result = [$alias]; foreach ($values as $value) { $paramName = $this->getParamName($field); $this->qb->setParameter($paramName, $value); $result[] = ":{$paramName}"; } return $result; } return $alias; }
/** * Reapply joins from a set of joins got from getDQLPart('join') * * @param array $joinsSet */ protected function applyJoins($joinsSet) { foreach ($joinsSet as $joins) { foreach ($joins as $join) { if ($join->getJoinType() === Join::LEFT_JOIN) { $this->qb->leftJoin($join->getJoin(), $join->getAlias(), $join->getConditionType(), $join->getCondition(), $join->getIndexBy()); } else { $this->qb->join($join->getJoin(), $join->getAlias(), $join->getConditionType(), $join->getCondition(), $join->getIndexBy()); } } } }
/** * {@inheritdoc} * * @throws InvalidArgumentException When a comparison expression is not supported. */ public function visitComparison(Expr\Comparison $comparison) { $property = $comparison->getProperty(); $path = explode('.', $property->getPath()); $expr = $this->builder->expr(); $aliases = $this->builder->getAllAliases(); $prefix = reset($aliases); foreach (array_slice($path, 0, -1) as $alias) { if (!in_array($alias, $aliases)) { $this->builder->join(sprintf('%s.%s', $prefix, $alias), $alias); } $prefix = $alias; } $field = sprintf('%s.%s', $prefix, end($path)); $value = $comparison->getValue(); switch (true) { case $comparison instanceof Expr\IdenticalTo: case $comparison instanceof Expr\EqualTo: return $expr->eq($field, $expr->literal($value)); case $comparison instanceof Expr\NotIdenticalTo: case $comparison instanceof Expr\NotEqualTo: return $expr->neq($field, $expr->literal($value)); case $comparison instanceof Expr\LessThan: return $expr->lt($field, $expr->literal($value)); case $comparison instanceof Expr\LessThanOrEqualTo: return $expr->lte($field, $expr->literal($value)); case $comparison instanceof Expr\GreaterThan: return $expr->gt($field, $expr->literal($value)); case $comparison instanceof Expr\GreaterThanOrEqualTo: return $expr->gte($field, $expr->literal($value)); case $comparison instanceof Expr\In: return $expr->in($field, $expr->literal($value)); case $comparison instanceof Expr\NotIn: return $expr->notIn($field, $expr->literal($value)); case $comparison instanceof Expr\Contains: return $expr->like(sprintf('LOWER(%s)', $field), $expr->literal('%' . strtolower($value) . '%')); } throw new InvalidArgumentException(sprintf('Unsupported comparison operator "%s".', get_class($comparison))); }
/** * @param QueryBuilder $queryBuilder * * @return QueryBuilder */ public function getQuery(QueryBuilder $queryBuilder) { $action = $this->getRule()->getAction(); $foundType = preg_match('/\\[\\[(.*)\\]\\]/', $action, $matches); if ($foundType) { $type = $matches[1]; $parts = explode(']]', $action); $action = $foundType ? $parts[1] : $action; return $queryBuilder->join('l.resourceType', 'rt')->andWhere('l.action = :action')->andWhere('rt.name = :type')->setParameter('action', $action)->setParameter('type', $type); } else { return $queryBuilder->andWhere('l.action = :action')->setParameter('action', $action); } }
/** * {@inheritDoc} */ public function visitAccess(AST\Bag\Context $element, &$handle = null, $eldnah = null) { $dimensions = $element->getDimensions(); // simple column access if (count($dimensions) === 0) { return sprintf('%s.%s', $this->getRootAlias(), $element->getId()); } // this is the real column that we are trying to access $finalColumn = array_pop($dimensions); // and this is a list of tables that need to be joined $tablesToJoin = array_map(function ($dimension) { return $dimension[1]; }, $dimensions); $tablesToJoin = array_merge([$element->getId()], $tablesToJoin); // check if the first dimension is a known alias if (isset($this->knownAliases[$tablesToJoin[0]])) { $joinTo = $tablesToJoin[0]; array_pop($tablesToJoin); } else { // if not, it's the root table $joinTo = $this->getRootAlias(); } // and here is the auto-join magic foreach ($tablesToJoin as $table) { $joinAlias = 'j_' . $table; $join = sprintf('%s.%s', $joinTo, $table); if (!isset($this->joinMap[$join])) { $this->joinMap[$join] = $joinAlias; $this->qb->join(sprintf('%s.%s', $joinTo, $table), $joinAlias); } else { $joinAlias = $this->joinMap[$join]; } $joinTo = $joinAlias; } return sprintf('%s.%s', $joinTo, $finalColumn[1]); }
/** * {@inheritdoc} */ public function apply(ResourceInterface $resource, QueryBuilder $queryBuilder, Request $request) { $metadata = $this->getClassMetadata($resource); $fieldNames = array_flip($metadata->getFieldNames()); foreach ($this->extractProperties($request) as $property => $value) { if (!is_string($value) || !$this->isPropertyEnabled($property)) { continue; } $partial = null !== $this->properties && self::STRATEGY_PARTIAL === $this->properties[$property]; if (isset($fieldNames[$property])) { if ('id' === $property) { $value = $this->getFilterValueFromUrl($value); } $queryBuilder->andWhere(sprintf('o.%1$s LIKE :%1$s', $property))->setParameter($property, $partial ? sprintf('%%%s%%', $value) : $value); } elseif ($metadata->isSingleValuedAssociation($property) || $metadata->isCollectionValuedAssociation($property)) { $value = $this->getFilterValueFromUrl($value); $queryBuilder->join(sprintf('o.%s', $property), $property)->andWhere(sprintf('%1$s.id = :%1$s', $property))->setParameter($property, $partial ? sprintf('%%%s%%', $value) : $value); } } }
/** * Appends various join tables to the result set * * (non-PHPdoc) * @see PartKeepr\Manager.AbstractManager::applyCustomQuery() */ protected function applyCustomQuery(QueryBuilder $qb, ManagerFilter $filter) { /** * Pull in additional tables */ $qb->join("q.storageLocation", "st")->leftJoin("q.footprint", "f")->join("q.category", "c")->leftJoin("q.partUnit", "pu"); // Apply special handling for non-direct fields in relations, where the frontend has no idea about. foreach ($filter->getSorters() as $sorter) { switch ($sorter->getSortField()) { case "q.categoryPath": $sorter->setSortField("c.categoryPath"); break; case "q.storageLocationName": $sorter->setSortField("st.name"); break; case "q.footprintName": $sorter->setSortField("f.name"); break; default: break; } } }
/** * @see FilterInterface::filter */ public function filter(QueryBuilder $qb, array $filters, $alias = 'e') { $expressions = []; foreach ($filters as $type => $filter) { foreach ($filter as $field => $value) { $holder = ':' . $field; $expr = is_array($value) ? 'in' : 'eq'; $namespace = $alias . '.' . $field; if ('association' == $type) { $qb->join($namespace, $field); $namespace = $field . '.id'; } elseif ('field' != $type) { continue; } $expressions[] = $qb->expr()->{$expr}($namespace, $holder); $qb->setParameter($field, $value); } } if (!empty($expressions)) { $criteria = call_user_func_array([$qb->expr(), 'andX'], $expressions); $qb->andWhere($criteria); } return $qb; }
/** * @param QueryBuilder $queryBuilder * @param Entity\User $user * @return \Doctrine\Common\Collections\ArrayCollection * @throws \InvalidArgumentException */ public function fetchUserGroupsApi(QueryBuilder $queryBuilder, Entity\User $user) { return $queryBuilder->join('row.users', 'u')->where('u.id = :userId')->setParameter('userId', $user->getId()); }
/** * Custom findBy so we can filter by related entities * * @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->leftJoin('t.courses', 'cr_course'); $qb->leftJoin('t.sessions', 'cr_session'); $qb->leftJoin('cr_session.course', 'cr_course2'); $qb->andWhere($qb->expr()->orX($qb->expr()->in('cr_course.id', ':courses'), $qb->expr()->in('cr_course2.id', ':courses'))); $qb->setParameter(':courses', $ids); } if (array_key_exists('sessions', $criteria)) { $ids = is_array($criteria['sessions']) ? $criteria['sessions'] : [$criteria['sessions']]; $qb->join('t.sessions', 'se_session'); $qb->andWhere($qb->expr()->in('se_session.id', ':sessions')); $qb->setParameter(':sessions', $ids); } if (array_key_exists('sessionTypes', $criteria)) { $ids = is_array($criteria['sessionTypes']) ? $criteria['sessionTypes'] : [$criteria['sessionTypes']]; $qb->join('t.sessions', 'st_session'); $qb->join('st_session.sessionType', 'st_sessionType'); $qb->andWhere($qb->expr()->in('st_sessionType.id', ':sessionTypes')); $qb->setParameter(':sessionTypes', $ids); } if (array_key_exists('programs', $criteria)) { $ids = is_array($criteria['programs']) ? $criteria['programs'] : [$criteria['programs']]; $qb->join('t.programYears', 'p_programYear'); $qb->join('p_programYear.program', 'p_program'); $qb->andWhere($qb->expr()->in('p_program.id', ':programs')); $qb->setParameter(':programs', $ids); } if (array_key_exists('instructors', $criteria)) { $ids = is_array($criteria['instructors']) ? $criteria['instructors'] : [$criteria['instructors']]; $qb->join('t.sessions', 'i_session'); $qb->leftJoin('i_session.offerings', 'i_offering'); $qb->leftJoin('i_offering.instructors', 'i_instructor'); $qb->leftJoin('i_offering.instructorGroups', 'i_iGroup'); $qb->leftJoin('i_iGroup.users', 'i_instructor2'); $qb->leftJoin('i_session.ilmSession', 'i_ilm'); $qb->leftJoin('i_ilm.instructors', 'i_instructor3'); $qb->leftJoin('i_ilm.instructorGroups', 'i_iGroup2'); $qb->leftJoin('i_iGroup2.users', 'i_instructor4'); $qb->andWhere($qb->expr()->orX($qb->expr()->in('i_instructor.id', ':instructors'), $qb->expr()->in('i_instructor2.id', ':instructors'), $qb->expr()->in('i_instructor3.id', ':instructors'), $qb->expr()->in('i_instructor4.id', ':instructors'))); $qb->setParameter(':instructors', $ids); } if (array_key_exists('instructorGroups', $criteria)) { $ids = is_array($criteria['instructorGroups']) ? $criteria['instructorGroups'] : [$criteria['instructorGroups']]; $qb->join('t.sessions', 'ig_session'); $qb->leftJoin('ig_session.offerings', 'ig_offering'); $qb->leftJoin('ig_offering.instructorGroups', 'ig_iGroup'); $qb->leftJoin('ig_session.ilmSession', 'ig_ilm'); $qb->leftJoin('ig_ilm.instructorGroups', 'ig_iGroup2'); $qb->andWhere($qb->expr()->orX($qb->expr()->in('ig_iGroup.id', ':iGroups'), $qb->expr()->in('ig_iGroup2.id', ':iGroups'))); $qb->setParameter(':iGroups', $ids); } if (array_key_exists('learningMaterials', $criteria)) { $ids = is_array($criteria['learningMaterials']) ? $criteria['learningMaterials'] : [$criteria['learningMaterials']]; $qb->leftJoin('t.courses', 'lm_course'); $qb->leftJoin('t.sessions', 'lm_session'); $qb->leftJoin('lm_course.learningMaterials', 'lm_clm'); $qb->leftJoin('lm_session.learningMaterials', 'lm_slm'); $qb->andWhere($qb->expr()->orX($qb->expr()->in('lm_slm.id', ':learningMaterials'), $qb->expr()->in('lm_clm.id', ':learningMaterials'))); $qb->setParameter(':learningMaterials', $ids); } if (array_key_exists('competencies', $criteria)) { $ids = is_array($criteria['competencies']) ? $criteria['competencies'] : [$criteria['competencies']]; $qb->leftJoin('t.courses', 'cm_course'); $qb->leftJoin('t.sessions', 'cm_session'); $qb->leftJoin('cm_course.objectives', 'cm_course_objective'); $qb->leftJoin('cm_course_objective.parents', 'cm_program_year_objective'); $qb->leftJoin('cm_program_year_objective.competency', 'cm_competency'); $qb->leftJoin('cm_competency.parent', 'cm_competency2'); $qb->leftJoin('cm_session.objectives', 'cm_session_objective'); $qb->leftJoin('cm_session_objective.parents', 'cm_course_objective2'); $qb->leftJoin('cm_course_objective2.parents', 'cm_program_year_objective2'); $qb->leftJoin('cm_program_year_objective2.competency', 'cm_competency3'); $qb->leftJoin('cm_competency3.parent', 'cm_competency4'); $qb->andWhere($qb->expr()->orX($qb->expr()->in('cm_competency.id', ':competencies'), $qb->expr()->in('cm_competency2.id', ':competencies'), $qb->expr()->in('cm_competency3.id', ':competencies'), $qb->expr()->in('cm_competency4.id', ':competencies'))); $qb->setParameter(':competencies', $ids); } if (array_key_exists('meshDescriptors', $criteria)) { $ids = is_array($criteria['meshDescriptors']) ? $criteria['meshDescriptors'] : [$criteria['meshDescriptors']]; $qb->leftJoin('t.courses', 'm_course'); $qb->leftJoin('t.sessions', 'm_session'); $qb->leftJoin('m_course.meshDescriptors', 'm_meshDescriptor'); $qb->leftJoin('m_session.meshDescriptors', 'm_meshDescriptor2'); $qb->leftJoin('m_session.course', 'm_course2'); $qb->leftJoin('m_course2.meshDescriptors', 'm_meshDescriptor3'); $qb->leftJoin('m_course.learningMaterials', 'm_clm'); $qb->leftJoin('m_clm.meshDescriptors', 'm_meshDescriptor4'); $qb->leftJoin('m_session.learningMaterials', 'm_slm'); $qb->leftJoin('m_slm.meshDescriptors', 'm_meshDescriptor5'); $qb->leftJoin('m_course2.learningMaterials', 'm_clm2'); $qb->leftJoin('m_clm.meshDescriptors', 'm_meshDescriptor6'); $qb->leftJoin('m_course.objectives', 'm_objective'); $qb->leftJoin('m_objective.meshDescriptors', 'm_meshDescriptor7'); $qb->leftJoin('m_session.objectives', 'm_objective2'); $qb->leftJoin('m_objective2.meshDescriptors', 'm_meshDescriptor8'); $qb->leftJoin('m_course2.objectives', 'm_objective3'); $qb->leftJoin('m_objective3.meshDescriptors', 'm_meshDescriptor9'); $qb->andWhere($qb->expr()->orX($qb->expr()->in('m_meshDescriptor.id', ':meshDescriptors'), $qb->expr()->in('m_meshDescriptor2.id', ':meshDescriptors'), $qb->expr()->in('m_meshDescriptor3.id', ':meshDescriptors'), $qb->expr()->in('m_meshDescriptor4.id', ':meshDescriptors'), $qb->expr()->in('m_meshDescriptor5.id', ':meshDescriptors'), $qb->expr()->in('m_meshDescriptor6.id', ':meshDescriptors'), $qb->expr()->in('m_meshDescriptor7.id', ':meshDescriptors'), $qb->expr()->in('m_meshDescriptor8.id', ':meshDescriptors'), $qb->expr()->in('m_meshDescriptor9.id', ':meshDescriptors'))); $qb->setParameter(':meshDescriptors', $ids); } if (array_key_exists('schools', $criteria)) { $ids = is_array($criteria['schools']) ? $criteria['schools'] : [$criteria['schools']]; $qb->join('t.sessions', 'sc_session'); $qb->join('sc_session.course', 'sc_course'); $qb->join('sc_course.school', 'sc_school'); $qb->andWhere($qb->expr()->in('sc_school.id', ':schools')); $qb->setParameter(':schools', $ids); } if (array_key_exists('aamcResourceTypes', $criteria)) { $ids = is_array($criteria['aamcResourceTypes']) ? $criteria['aamcResourceTypes'] : [$criteria['aamcResourceTypes']]; $qb->join('t.aamcResourceTypes', 'art_resourceTypes'); $qb->andWhere($qb->expr()->in('art_resourceTypes.id', ':aamcResourceTypes')); $qb->setParameter(':aamcResourceTypes', $ids); } unset($criteria['schools']); unset($criteria['courses']); unset($criteria['sessions']); unset($criteria['sessionTypes']); unset($criteria['programs']); unset($criteria['instructors']); unset($criteria['instructorGroups']); unset($criteria['learningMaterials']); unset($criteria['competencies']); unset($criteria['meshDescriptors']); unset($criteria['aamcResourceTypes']); if (count($criteria)) { foreach ($criteria as $key => $value) { $values = is_array($value) ? $value : [$value]; $qb->andWhere($qb->expr()->in("t.{$key}", ":{$key}")); $qb->setParameter(":{$key}", $values); } } if (empty($orderBy)) { $orderBy = ['id' => 'ASC']; } if (is_array($orderBy)) { foreach ($orderBy as $sort => $order) { $qb->addOrderBy('t.' . $sort, $order); } } if ($offset) { $qb->setFirstResult($offset); } if ($limit) { $qb->setMaxResults($limit); } return $qb; }
/** * {@inheritdoc} */ public function apply(ResourceInterface $resource, QueryBuilder $queryBuilder) { $request = $this->requestStack->getCurrentRequest(); if (null === $request) { return; } foreach ($this->extractProperties($request) as $property => $value) { if (!$this->isPropertyEnabled($property) || !$this->isPropertyMapped($property, $resource, true) || null === $value) { continue; } $alias = 'o'; $field = $property; if ($this->isPropertyNested($property)) { $propertyParts = $this->splitPropertyParts($property); $parentAlias = $alias; foreach ($propertyParts['associations'] as $association) { $alias = QueryNameGenerator::generateJoinAlias($association); $queryBuilder->join(sprintf('%s.%s', $parentAlias, $association), $alias); $parentAlias = $alias; } $field = $propertyParts['field']; $metadata = $this->getNestedMetadata($resource, $propertyParts['associations']); } else { $metadata = $this->getClassMetadata($resource); } if ($metadata->hasField($field)) { if (!is_string($value)) { continue; } if ('id' === $field) { $value = $this->getFilterValueFromUrl($value); } $strategy = null !== $this->properties ? $this->properties[$property] : self::STRATEGY_EXACT; $this->addWhereByStrategy($strategy, $queryBuilder, $alias, $field, $value); } elseif ($metadata->hasAssociation($field)) { $values = (array) $value; foreach ($values as $k => $v) { if (!is_int($k) || !is_string($v)) { unset($values[$k]); } } $values = array_values($values); if (empty($values)) { continue; } $values = array_map([$this, 'getFilterValueFromUrl'], $values); $association = $field; $associationAlias = QueryNameGenerator::generateJoinAlias($association); $valueParameter = QueryNameGenerator::generateParameterName($association); $queryBuilder->join(sprintf('%s.%s', $alias, $association), $associationAlias); if (1 === count($values)) { $queryBuilder->andWhere(sprintf('%s.id = :%s', $associationAlias, $valueParameter))->setParameter($valueParameter, $values[0]); } else { $queryBuilder->andWhere(sprintf('%s.id IN (:%s)', $associationAlias, $valueParameter))->setParameter($valueParameter, $values); } } } }
/** * Custom findBy so we can filter by related entities * * @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('sessions', $criteria)) { $ids = is_array($criteria['sessions']) ? $criteria['sessions'] : [$criteria['sessions']]; $qb->join('c.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('c.terms', 't_term'); $qb->andWhere($qb->expr()->in('t_term.id', ':terms')); $qb->setParameter(':terms', $ids); } if (array_key_exists('programs', $criteria)) { $ids = is_array($criteria['programs']) ? $criteria['programs'] : [$criteria['programs']]; $qb->join('c.cohorts', 'p_cohort'); $qb->join('p_cohort.programYear', 'p_programYear'); $qb->join('p_programYear.program', 'p_program'); $qb->andWhere($qb->expr()->in('p_program.id', ':programs')); $qb->setParameter(':programs', $ids); } if (array_key_exists('programYears', $criteria)) { $ids = is_array($criteria['programYears']) ? $criteria['programYears'] : [$criteria['programYears']]; $qb->join('c.cohorts', 'py_cohort'); $qb->join('py_cohort.programYear', 'py_programYear'); $qb->andWhere($qb->expr()->in('py_programYear.id', ':programYears')); $qb->setParameter(':programYears', $ids); } if (array_key_exists('instructors', $criteria)) { $ids = is_array($criteria['instructors']) ? $criteria['instructors'] : [$criteria['instructors']]; $qb->leftJoin('c.sessions', 'i_session'); $qb->leftJoin('c.directors', 'i_director'); $qb->leftJoin('i_session.offerings', 'i_offering'); $qb->leftJoin('i_offering.instructors', 'i_user'); $qb->leftJoin('i_offering.instructorGroups', 'i_insGroup'); $qb->leftJoin('i_insGroup.users', 'i_groupUser'); $qb->leftJoin('i_session.ilmSession', 'i_ilmSession'); $qb->leftJoin('i_ilmSession.instructors', 'i_ilmInstructor'); $qb->leftJoin('i_ilmSession.instructorGroups', 'i_ilmInsGroup'); $qb->leftJoin('i_ilmInsGroup.users', 'i_ilmInsGroupUser'); $qb->andWhere($qb->expr()->orX($qb->expr()->in('i_director.id', ':users'), $qb->expr()->in('i_user.id', ':users'), $qb->expr()->in('i_groupUser.id', ':users'), $qb->expr()->in('i_ilmInstructor.id', ':users'), $qb->expr()->in('i_ilmInsGroupUser.id', ':users'))); $qb->setParameter(':users', $ids); } if (array_key_exists('instructorGroups', $criteria)) { $ids = is_array($criteria['instructorGroups']) ? $criteria['instructorGroups'] : [$criteria['instructorGroups']]; $qb->leftJoin('c.sessions', 'ig_session'); $qb->leftJoin('ig_session.offerings', 'ig_offering'); $qb->leftJoin('ig_offering.instructorGroups', 'ig_igroup'); $qb->leftJoin('ig_session.ilmSession', 'ig_ilmSession'); $qb->leftJoin('ig_ilmSession.instructorGroups', 'ig_ilmInsGroup'); $qb->andWhere($qb->expr()->orX($qb->expr()->in('ig_igroup.id', ':igroups'), $qb->expr()->in('ig_ilmInsGroup.id', ':igroups'))); $qb->setParameter(':igroups', $ids); } if (array_key_exists('learningMaterials', $criteria)) { $ids = is_array($criteria['learningMaterials']) ? $criteria['learningMaterials'] : [$criteria['learningMaterials']]; $qb->leftJoin('c.learningMaterials', 'lm_clm'); $qb->leftJoin('lm_clm.learningMaterial', 'lm_lm'); $qb->leftJoin('c.sessions', 'lm_session'); $qb->leftJoin('lm_session.learningMaterials', 'lm_slm'); $qb->leftJoin('lm_slm.learningMaterial', 'lm_lm2'); $qb->andWhere($qb->expr()->orX($qb->expr()->in('lm_lm.id', ':lms'), $qb->expr()->in('lm_lm2.id', ':lms'))); $qb->setParameter(':lms', $ids); } if (array_key_exists('competencies', $criteria)) { $ids = is_array($criteria['competencies']) ? $criteria['competencies'] : [$criteria['competencies']]; $qb->join('c.objectives', 'c_course_objective'); $qb->join('c_course_objective.parents', 'c_program_year_objective'); $qb->leftJoin('c_program_year_objective.competency', 'c_competency'); $qb->leftJoin('c_competency.parent', 'c_competency2'); $qb->andWhere($qb->expr()->orX($qb->expr()->in('c_competency.id', ':competencies'), $qb->expr()->in('c_competency2.id', ':competencies'))); $qb->setParameter(':competencies', $ids); } if (array_key_exists('meshDescriptors', $criteria)) { $ids = is_array($criteria['meshDescriptors']) ? $criteria['meshDescriptors'] : [$criteria['meshDescriptors']]; $qb->leftJoin('c.meshDescriptors', 'm_meshDescriptor'); $qb->leftJoin('c.sessions', 'm_session'); $qb->leftJoin('m_session.meshDescriptors', 'm_sessMeshDescriptor'); $qb->leftJoin('c.objectives', 'm_cObjective'); $qb->leftJoin('m_cObjective.meshDescriptors', 'm_cObjectiveMeshDescriptor'); $qb->leftJoin('m_session.objectives', 'm_sObjective'); $qb->leftJoin('m_sObjective.meshDescriptors', 'm_sObjectiveMeshDescriptors'); $qb->andWhere($qb->expr()->orX($qb->expr()->in('m_meshDescriptor.id', ':meshDescriptors'), $qb->expr()->in('m_sessMeshDescriptor.id', ':meshDescriptors'), $qb->expr()->in('m_cObjectiveMeshDescriptor.id', ':meshDescriptors'), $qb->expr()->in('m_sObjectiveMeshDescriptors.id', ':meshDescriptors'))); $qb->setParameter(':meshDescriptors', $ids); } if (array_key_exists('schools', $criteria)) { $ids = is_array($criteria['schools']) ? $criteria['schools'] : [$criteria['schools']]; $qb->join('c.school', 'sc_school'); $qb->andWhere($qb->expr()->in('sc_school.id', ':schools')); $qb->setParameter(':schools', $ids); } if (array_key_exists('ancestors', $criteria)) { $ids = is_array($criteria['ancestors']) ? $criteria['ancestors'] : [$criteria['ancestors']]; $qb->join('c.ancestor', 'anc_course'); $qb->andWhere($qb->expr()->in('anc_course.id', ':ancestors')); $qb->setParameter(':ancestors', $ids); } //cleanup all the possible relationship filters unset($criteria['schools']); unset($criteria['sessions']); unset($criteria['terms']); unset($criteria['programs']); unset($criteria['programYears']); unset($criteria['instructors']); unset($criteria['instructorGroups']); unset($criteria['learningMaterials']); unset($criteria['competencies']); unset($criteria['meshDescriptors']); unset($criteria['ancestors']); if (count($criteria)) { foreach ($criteria as $key => $value) { $values = is_array($value) ? $value : [$value]; $qb->andWhere($qb->expr()->in("c.{$key}", ":{$key}")); $qb->setParameter(":{$key}", $values); } } if (empty($orderBy)) { $orderBy = ['id' => 'ASC']; } if (is_array($orderBy)) { foreach ($orderBy as $sort => $order) { $qb->addOrderBy('c.' . $sort, $order); } } if ($offset) { $qb->setFirstResult($offset); } if ($limit) { $qb->setMaxResults($limit); } return $qb; }
/** * {@inheritdoc} */ public function apply(ResourceInterface $resource, QueryBuilder $queryBuilder) { $request = $this->requestStack->getCurrentRequest(); if (null === $request) { return; } $metadata = $this->getClassMetadata($resource); $fieldNames = array_flip($metadata->getFieldNames()); foreach ($this->extractProperties($request) as $property => $value) { if (null === $value || !$this->isPropertyEnabled($property)) { continue; } if (isset($fieldNames[$property])) { if (!is_string($value)) { continue; } $partial = null !== $this->properties && self::STRATEGY_PARTIAL === $this->properties[$property]; if ('id' === $property) { $value = $this->getFilterValueFromUrl($value); } $queryBuilder->andWhere(sprintf('o.%1$s LIKE :%1$s', $property))->setParameter($property, $partial ? sprintf('%%%s%%', $value) : $value); } elseif ($metadata->isSingleValuedAssociation($property)) { if (!is_string($value)) { continue; } $value = $this->getFilterValueFromUrl($value); $queryBuilder->join(sprintf('o.%s', $property), $property)->andWhere(sprintf('%1$s.id = :%1$s', $property))->setParameter($property, $value); } elseif ($metadata->isCollectionValuedAssociation($property)) { $values = $value; if (!is_array($values)) { $values = [$value]; } foreach ($values as $k => $v) { if (!is_int($k) || !is_string($v)) { unset($values[$k]); } } if (empty($values)) { continue; } $values = array_map([$this, 'getFilterValueFromUrl'], $values); $queryBuilder->join(sprintf('o.%s', $property), $property)->andWhere(sprintf('%1$s.id IN (:%1$s)', $property))->setParameter($property, $values); } } }
/** * @param QueryBuilder $queryBuilder * @param array $orderBy * @return $this */ public function applyOrderBy($queryBuilder, array $orderBy = array()) { foreach ($orderBy as $field => $dir) { if (is_int($field)) { $field = $dir; $dir = 'ASC'; } $result = $this->resolver->resolveField($field); foreach ($result['joins'] as $join => $alias) { $queryBuilder->join($join, $alias); } $queryBuilder->addOrderBy($result['field'], $dir); } return $this; }
/** * @param QueryBuilder $queryBuilder * @param string $type * @return $this */ protected function applyFolderFilter(QueryBuilder $queryBuilder, $type) { $queryBuilder->join('eu.folder', 'f'); $queryBuilder->andWhere($queryBuilder->expr()->eq('f.type', $queryBuilder->expr()->literal($type))); return $this; }
/** * @param QueryBuilder $queryBuilder * @param string $rootAlias * @param User $user * * @return QueryBuilder */ public function filterByUser(QueryBuilder $queryBuilder, $rootAlias, User $user) { $queryBuilder->join('badge.userBadges', 'ub')->andWhere('ub.user = :user')->setParameter('user', $user); return $queryBuilder; }
/** * @param QueryBuilder $queryBuilder * @param string|string[] $type * @return $this */ protected function applyFolderFilter(QueryBuilder $queryBuilder, $type) { $queryBuilder->join('eu.folders', 'f'); if (!is_array($type)) { $type = [$type]; } $expressions = []; foreach ($type as $folderType) { $expressions[] = $queryBuilder->expr()->eq('f.type', $queryBuilder->expr()->literal($folderType)); } /** * In case of "inbox" type we should include "other" type too. * Case with selective email folder sync, e.g. when syncing some folder different from "Inbox". */ if (in_array(FolderType::INBOX, $type)) { $expressions[] = $queryBuilder->expr()->eq('f.type', $queryBuilder->expr()->literal(FolderType::OTHER)); } $expr = call_user_func_array([$queryBuilder->expr(), 'orX'], $expressions); $queryBuilder->andWhere($expr); return $this; }
/** * Adds all required joins to the queryBuilder. * * @param QueryBuilder $queryBuilder * @param $filter */ private function addJoins(QueryBuilder $queryBuilder, AssociationPropertyInterface $filter) { if (in_array($filter->getAssociation(), $this->joins)) { // Association already added, return return; } $associations = explode('.', $filter->getAssociation()); $fullAssociation = 'o'; foreach ($associations as $key => $association) { if (isset($associations[$key - 1])) { $parent = $associations[$key - 1]; } else { $parent = 'o'; } $fullAssociation .= '.' . $association; $alias = $this->getAlias($fullAssociation); $queryBuilder->join($parent . '.' . $association, $alias); } $this->joins[] = $filter->getAssociation(); }