Download the PHP package adt/base-query without Composer

Doctrine Components

Install

Creating a QueryObject class

Method getEntityClass

The getEntityClass method must be specified and return your entity class.

Method setDefaultOrder

The setDefaultOrder method must be specified and set the default order.

Method init

The init method is used to specify default filters and order. You have to always call parent::init() when you use it.

Callback array filter

filter is array of callbacks which will be applied on QueryBuilder when created.

Similarly you can use order callback for setting the query object order.

Method by and orderBy

Method by is a shortcut for creating filter callbacks. It offers some useful features:

• When there are more columns, orWhere is used among them.

• If a $value is type of 'string', LIKE %$value% is used. You can change it by parameter filterType with value FilterTypeEnum::STRICT.

• If you would like get all value in certain range, you can use parameter filterType with value FilterTypeEnum::RANGE.

Method orderBy is a shortcut for setting order callback.

• You can use a column name as first parameter and ASC/DESC as a second parameter instead of an array, if you need to sort only by one column.

You can use dot notation to auto join other entities (left join is used).

Basic usage

Creating an instance

or better with the use of a factory:

`

together with neon:

Fetch results

Count results

Disable default filters

Pagination

`

Advanced features

Manul joins

For manual joins you should use innerJoin and leftJoin methods:

Unlike QueryBuilder::innerJoin and QueryBuilder::leftJoin, this ensures that same joins are not used multiple times and don't throw an error.

More columns

Don't use addSelect inside a filter callback. Use initSelect method instead:

Or create your own fetching method:

When there are more columns specified, default fetch* method won't work.

More complex sorting

You can create your own sorting callback instead of using orderBy method:

Don't forget to use AS HIDDEN in your addSelect method, otherwise the fetch* methods won't work.

Fulltext search

For a fulltext search over a column with a MySQL FULLTEXT index implement SearchFulltextFilter and use SearchFulltextFilterTrait:

It generates MATCH ... AGAINST (... IN BOOLEAN MODE) > 0, so the match_against DQL function has to be registered in your application (the package doesn't do it for you):

The value is split into words on every non-alphanumeric character (so boolean mode operators can't break the query), all words have to match (AND) and each of them matches from the beginning of an indexed word - Ariola Martin becomes +Ariola* +Martin*. Searching inside a word (rtin) never matches, that's how a FULLTEXT index works.

Words shorter than innodb_ft_min_token_size are not in the index at all, those are searched with LIKE '%word%' instead, otherwise a two letter code would never be found. The default is 3 (MySQL InnoDB default), override it if your server is configured differently:

Diacritics are handled by the collation of the column, use an accent insensitive one (eg. utf8mb4_0900_ai_ci).

A column path over relations works as well, the join is added automatically:

Method orById

If you want to get all active records plus a specific one, you can use orById method to bypass default filters:

It's especially useful for <select>.

Use in batch processing

If you want to iterate large number of records, you can use https://github.com/Ocramius/DoctrineBatchUtils together with query object:

You should always use new EntityManager instance, not the default one (because of EntityManager::clear).

Tips

• Always have all logic inside a filter or order callback. This will ensure that all dependencies (like a logged user etc.) are already set.

• Always use and* methods (andWhere, andSelect, ...) on QueryBuilder (instead of where, select, ...).

• Don't use QueryBuilder::resetDQLPart method, because it's againt basic idea of QueryObject

• Parameters in andWhere method should by named by method name and parametr name to avoid collision.

• Methods by and orderBy are public methods, but it's always better to create own by* or orderBy* methods.

• You should always specify a deterministic order, ideally with usage of primary key.