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