Download the PHP package rugolinifr/enhanced-find-by without Composer

Enhanced findBy() method

This package provides an API similar to the famous Doctrine findBy() method, but offering more capabilities to execute more complex (but still simple) queries.

Context

The original Doctrine findBy() method is pretty limited:

• it provides only two SQL operators: = and IN (),
• it filters only on the properties owned by the target entity,

whereas the enhanced findBy() method:

• offers many SQL operators: =, IN (), !=, NOT IN(), <, <=, >, >=, LIKE, NOT LIKE, IS NULL, IS NOT NULL,
• filters on properties from other entities (see below),
• sorts the result set when needed,
• limits the number of fetched entities on demand.

When the enhanced findBy() method filters on properties from other entities, it handles by itself every mandatory INNER JOIN clause, removing the need for the developer to use the DQL or the QueryBuilder API.

Installation

Run composer require rugolinifr/enhanced-find-by.

Usage

Developers should use only:

• the Rugolinifr\EnhancedFindBy\Factory\EnhancedFindByFactory factory,
• any class from the Rugolinifr\EnhancedFindBy\Contract namespace.

Other classes are considered @internal.

Basic usage examples:

The $from parameter

The $from parameter takes the class name of the entity to fetch as argument. As usual, it may be retrieved from either the get_class() function or the ::class keyword.

The $where parameter

The $where parameter takes an array as argument. This array is a sequence where each key is an existing entity property, followed by a space, terminated by an operator.

On the other hand, the value type is mixed and depends on the operator name and the property type.

The key-value pair represents a boolean expression; an entity matching every expression will be returned by the findBy() method. The expressions are translated into DQL in the order they are declared in the array.

For example, the following PHP code:

produces the following DQL query:

The = operator

Returns true if and only if the property value equals the given value, false otherwise.

This operator accepts the following types: string, int, float, bool, \BackedEnum, \DateTimeInterface, null, any entity type and finally an array of any type mentioned earlier.

Note that this operator handles null values using the IS NULL operator.

Note that comparing nullable properties returns the expected result (the DBMS considers NULL = 'foo' as unknown which is not true and therefore the row is discarded).

Note that an array of values behaves like the IN () operator, but handles null values inside the array with a IS NULL operator.

Note that passing an entity type works on *ToOne associations, but does not with *ToMany associations (as it is using the DQL).

The != operator

Returns true if and only if the property value is different from the given value, false otherwise.

This operator has the same characteristics as the = operator. However, passing a null value (or an array having a null value) as argument creates a comparison using the IS NOT NULL operator.

Note that this operator is not null-safe: when comparing nullable properties, it does not return the expected result (the DBMS considers NULL != 'foo' as unknown which is not true and therefore the row is discarded).

Consider using the !== operator on nullable properties.

The !== operator

Returns true if and only if the property value is different from the given value, false otherwise.

This operator has the same characteristics as the != operator, but forbids null (or an array having null) as given argument.

On the other hand, it is null-safe: when comparing nullable properties, it returns the expected result (NULL != 'foo' is true and therefore the row is returned). Internally, this is done by prepending the DQL condition with property IS NULL OR.

The < operator

Returns true if and only if the property value is strictly less than the given value, false otherwise.

This operator accepts the following types: int, float, string, \BackedEnum and \DateTimeInterface.

The <= operator

Returns true if and only if the property value is less than or equal to the given value, false otherwise.

This operator accepts the same types as the < operator.

The > operator

Returns true if and only if the property value is strictly greater than the given value, false otherwise.

This operator accepts the same types as the < operator.

The >= operator

Returns true if and only if the property value is greater than or equal to the given value, false otherwise.

This operator accepts the same types as the < operator.

The like operator

Returns true if and only if the property value contains the given string pattern, false otherwise.

This operator accepts string and string[] values only.

The % character acts as the .* regex expression. Example:

The not_like operator

Returns true if and only if the property value does not contain the given string pattern, false otherwise.

This operator accepts string and string[] values only.

See the like operator for information about the % character.

Note that this operator is not null-safe: when comparing nullable properties, it does not return the expected result (the DBMS considers NULL NOT LIKE 'foo' as unknown which is not true and therefore the row is discarded).

Consider using the n_not_like operator on nullable properties.

The n_not_like operator

Returns true if and only if the property value is null or does not contain the given string pattern, false otherwise.

This operator has the same characteristics as the not_like operator, except it is null-safe (NULL NOT LIKE 'foo' is true and therefore the row is returned). Internally, this is done by prepending the DQL condition with property IS NULL OR.

The orderBy parameter

The $orderBy parameter takes an array as argument. This array is a sequence where each key is an existing entity property, mapped to either the "ASC" or the "DESC" string.

For example, the following invocation:

is converted to the following DQL query:

The $limit and $offset parameters

These parameters act as the LIMIT SQL clause.

For example, the following PHP code:

produces the following DQL query:

and these two Doctrine methods:

Note that mixing any implicit join with the limit operator leads to the query being wrapped by a Paginator. The latter is mandatory when the join targets a *ToMany relation to fetch consistent data.

The count() method

Since v1.2.*, it is possible to count the number of entities:

Both the $from and $where parameters behave the same as those from the findBy() method.

Known limitations

The enhanced findBy() can't:

• perform OUTER JOIN clause,
• perform OR operator in WHERE clause,
• compare two entity properties.

The QueryBuilder is still needed for the use cases listed above.

For maintainers

To contribute or to inspect the project, docker is required:

The test coverage may be checked with:

The test database may be opened with:

Also note that the Doctrine console tool is located at bin/console: