Collection is filtered through its findBy()
method;
alternatively, shortcut getBy()
accepts the same filtering
expressions.
The simplest filtering is an array of conditions. These conditions are passed
as the only parameter of the findBy()
method. The associative array
consists of entity property names and and their wanted values. Keys can contain
an optional operator. The default operator is equality. Let's see the
example:
$books = $orm->books->findBy([
'author' => $author->id,
'publishedAt<=' => new DateTimeImmutable(),
]);
Allowed operators are =
, !=
, <=
,
<
, >=
, >
, and ~
(like – see later), append it directly after the property name (without an
space or other white-char).
You can filter the collection by conditions with condition filtering by a
relationships traversing; use a traversing expression: it consists of
the path delimited by ->
– the same arrow you use
in PHP.
// find all books which were authored by Jon Snow
$orm->books->findBy(['author->name' => 'Jon Snow']);
// find all books which were not translated by Jon Snow
$orm->books->findBy(['translator->name!=' => 'Jon Snow']);
The described syntax may be expanded to support a OR
logical
conjunction. Prepend the ICollection::OR
operator as a first value
of the filtering array:
// finds all books which were authored or translated by one specific person
$books = $orm->books->findBy([
ICollection::OR,
'author->name' => 'Jon Snow',
'translator->name' => 'Jon Snow',
]);
This relationship filtering is designed mainly for has-one relationship. Has-many relationship usually utilize an aggregation function, which is covered later in this chapter. Still, this filtering syntax works for has-many relationship. Such expression will select all entries where at least one of the entities in has-many relationship meets the conditions.
You may nest the filtering structure; use the same syntax repeatedly:
// find all man older than 10 years and woman younger than 12 years
$authors = $orm->author->findBy([
ICollection::OR,
[
ICollection::AND,
'age>=' => 10,
'sex' => 'male',
],
[
ICollection::AND,
'age<=' => 12,
'sex' => 'female',
],
]);
The previous example can be shortened because the AND
operator
is the default logical operator.
// find all man older than 10 years and woman younger than 12 years
$authors = $orm->author->findBy([
ICollection::OR,
[
'age>=' => 10,
'gender' => 'male',
],
[
'age<=' => 12,
'gender' => 'female',
],
]);
Filtering over virtual properties is generally unsupported and provides undefined behavior.
LIKE
filtering is supported and directly provided in Nextras
Orm. Use ~
compare operator. The value has to be wrapped as
Nextras\Orm\Collection\Expression\LikeExpression
instance, use its
static builders to create one: choose from startsWith
,
endsWith
or contains
. Alternatively, you may provide
your wildcard expression with raw
method. Be aware, that raw method
expects sanitized input.
// finds all users with email hosted on gmail.com
$users->findBy([
'emails~' => LikeExpression::endsWith('@gmail.com'),
]);
Aggregation functions can be used for both collection filtering and sorting. They are based on collection functions – a general approach for custom collection modification.
Orm brings these prepared aggregation functions:
All those functions are implemented both for Dbal and Array collections and they are registered in repository as commonly provided collection functions.
To use a collection function, pass the function name and then its arguments –- all aggregation functions take only one argument – an expression that should be aggregated. Let’s see an example:
use Nextras\Orm\Collection\Functions\CountAggregateFunction;
$authorsCollection->orderBy(
[CountAggregateFunction::class, 'books->id']
);
In the example we sort the collection of authors by the count of their books, i.e. authors with the least books will be at the beginning. The example allows the same “property expression” you use for filtering. You can reverse the ordering:
use Nextras\Orm\Collection\Functions\CountAggregateFunction;
use Nextras\Orm\Collection\ICollection;
$authorsCollection->orderBy(
[CountAggregateFunction::class, 'books->id'],
ICollection::DESC
);
Filtering by an aggregation requires a little more. Let's filter the
collection by authors who have written more than 2 books. Using
CountAggregationFunction
itself won’t be enough. You need to
compare its result with the wanted number, 2
this time. To do so,
use built-in Compare*Function
. Choose function depending on the
wanted operator. The function takes a property expression on the left, and a
value to compare (on the right).
use Nextras\Orm\Collection\Functions\CompareGreaterThanFunction;
use Nextras\Orm\Collection\Functions\CountAggregateFunction;
// SELECT * FROM authors
// LEFT JOIN books ON (...)
// GROUP BY authors.id
// HAVING COUNT(books.id) > 2
$authorsCollection->findBy(
[
CompareGreaterThanFunction::class,
[CountAggregateFunction::class, 'books->id'],
2,
]
);
You can nest these function calls together. This approach is very powerful and flexible, though, sometimes quite verbose. To ease this issue you may create own wrappers (not included in Orm!).
class Aggregate {
public static function count(string $expression): array {
return [CountAggregateFunction::class, $expression];
}
}
class Compare {
public static function gt(string $expression, $value): array {
return [
CompareGreaterThanFunction::class,
$expression,
$value,
];
}
}
// filters authors who have more than 2 books
// and sorts them by the count of their books descending
$authorsCollection
->findBy(Compare::gt(Aggregate::count('books->id'), 2))
->orderBy(Aggregate::count('books->id'), ICollection::DESC);
Feel free to share a feedback about using aggregation functions.