Carrooi/DoctrineQueries
, (*1)
Builder for doctrine query builders based on kdyby/doctrine, (*2)
Installation
$ composer require carrooi/doctrine-queries
QueryObject
Please, first read documentation of kdyby's QueryObjects., (*3)
class UserQuery extends Carrooi\Doctrine\Queries\QueryObject
{
public function byId($id)
{
$this->addFilter(function(QueryBuilder $qb) use ($id) {
$qb->andWhere('u.id = :id')->setParameter('id', $id);
});
return $this;
}
public function doCreateQuery(Queryable $repository)
{
$qb = $repository->createQueryBuilder()
->select('u')->from('App\User', 'u');
$this
->applyFilters($qb)
->applySelectFilters($qb);
// or just:
// $this->applyAllFilters($qb);
return $qb;
}
}
Select filters
class UserQuery extends Carrooi\Doctrine\Queries\QueryObject
{
public function selectCount()
{
$this->addSelectFilter(function(QueryBuilder $qb) {
$qb->select('COUNT(u)');
});
return $this;
}
}
Selects
If you have more methods which selects different columns, you will run into errors about already selected columns.
You can avoid that by using some helper methods., (*4)
class UserQuery extends Carrooi\Doctrine\Queries\QueryObject
{
public function selectNick()
{
$this->trySelect('u', ['nick']);
return $this;
}
public function selectEmail()
{
$this->trySelect('u', ['email']);
return $this;
}
}
DQL: SELECT PARTIAL u.{id,nick,email} FROM ..., (*5)
With result alias:, (*6)
class UserQuery extends Carrooi\Doctrine\Queries\QueryObject
{
public function selectNickAndEmail()
{
$this->trySelect('u', ['user' => ['nick', 'email']]);
return $this;
}
}
DQL: SELECT PARTIAL u.{id,nick,email} AS user FROM ..., (*7)
Or with distinct:, (*8)
class UserQuery extends Carrooi\Doctrine\Queries\QueryObject
{
public function selectNick()
{
$this->tryDistinctSelect('u', ['nick']);
return $this;
}
}
You can also use classic column selects without partials. That can be useful for example for array hydration., (*9)
class UserQuery extends Carrooi\Doctrine\Queries\QueryObject
{
public function selectNick()
{
$this->trySelect('u', [
'nick' => 'nickAlias', // nickAlias will be name of result key
]);
return $this;
}
public function selectEmail()
{
$this->trySelect('u', ['email']); // you can combine partial and classic column selects
return $this;
}
}
DQL: SELECT u.nick AS nickAlias, PARTIAL u.{id,email} FROM ..., (*10)
Joins
Same problem like with selects is with joins. If you will try to join same relation many times, you will get error.
Again, there are methods for that., (*11)
class UserQuery extends Carrooi\Doctrine\Queries\QueryObject
{
public function byBookName($name)
{
$this->tryJoin('u.books', 'b'); // INNER JOIN
$this->addFilter(function(QueryBuilder $qb) use ($name) {
$qb->andWhere('b.name = :name')->setParameter('name', $name);
});
return $this;
}
}
You can also use tryLeftJoin method., (*12)
Helpers
-
$query->addParameters(QueryBuilder $qb, array $parameters): set parameters without overwriting the old ones
Nested trees searching
If you are using eg. gedmo nested trees, you could also use TNestedTreeQuery trait for simple searching in tree., (*13)
class UserQuery extends Carrooi\Doctrine\Queries\QueryObject
{
use Carrooi\Doctrine\Queries\Tree\TNestedTreeQuery;
public function byTree(array $entities)
{
// ... some joins
$this->addFilter(function(QueryBuilder $qb) use ($entities) {
$condition = $this->createNestedTreeSearchCondition($entities, 'entityAlias');
$qb->andWhere($condition->getCondition());
$query->addParameters($qb, $condition->getParameters());
});
}
}
That example will find all entities in database with at least one entity from given array of entities, even they are
same, in some children entity or some parent entity., (*14)
Search by at least one entity (uses OR) default, (*15)
use Carrooi\Doctrine\Queries\Tree\SearchType;
$query->createNestedTreeSearchCondition($entities, 'entityAlias', SearchType::CONDITION_OR);
Search by all entities (uses AND), (*16)
use Carrooi\Doctrine\Queries\Tree\SearchType;
$query->createNestedTreeSearchCondition($entities, 'entityAlias', SearchType::CONDITION_AND);
Search only for same, in parents and in children default, (*17)
use Carrooi\Doctrine\Queries\Tree\SearchType;
$query->createNestedTreeSearchCondition($entities, 'entityAlias', null, SearchType::SEARCH_EVERYWHERE);
Search only for same, (*18)
use Carrooi\Doctrine\Queries\Tree\SearchType;
$query->createNestedTreeSearchCondition($entities, 'entityAlias', null, SearchType::SEARCH_FOR_SAME);
Search only in parents, (*19)
use Carrooi\Doctrine\Queries\Tree\SearchType;
$query->createNestedTreeSearchCondition($entities, 'entityAlias', null, SearchType::SEARCH_IN_PARENTS);
Search only in children, (*20)
use Carrooi\Doctrine\Queries\Tree\SearchType;
$query->createNestedTreeSearchCondition($entities, 'entityAlias', null, SearchType::SEARCH_IN_CHILDREN);
Combined searching, (*21)
use Carrooi\Doctrine\Queries\Tree\SearchType;
$query->createNestedTreeSearchCondition($entities, 'entityAlias', null, SearchType::SEARCH_IN_PARENTS | SearchType::SEARCH_IN_CHILDREN);
Custom column names, (*22)
TNestedTreeQuery trait will use by default these column names:, (*23)
But if you need, you can use custom names:, (*24)
use Carrooi\Doctrine\Queries\Tree\SearchType;
$query->createNestedTreeSearchCondition($entities, 'entityAlias', null, null, [
'id' => 'id',
'level' => 'lvl',
'root' => 'root',
'left' => 'lft',
'right' => 'rgt',
]);
Getting results
getQueryBuilder()getResultSet()getResult()getPairs()getOneOrNullResult()getSingleScalarResult()
Changelog
-
1.2.1, (*25)
-
1.2.0, (*26)
- Better aliases in tree searching DQLs
- Support for classic custom column selects (not just partials)
- Add option to set result alias for partial selects
-
1.1.0, (*27)
- Add field function for DQL
- Add TNestedTreeQuery trait for searching in nested trees
-
1.0.1, (*28)
- Do not rewrite existing joins
-
1.0.0, (*29)
Wallogit.com
