PHPackages adt/base-query - PHPackages - PHPackages [Skip to content](#main-content)[PHPackages](/)[Directory](/)[Categories](/categories)[Trending](/trending)[Leaderboard](/leaderboard)[Changelog](/changelog)[Analyze](/analyze)[Collections](/collections)[Log in](/login)[Sign up](/register) 1. [Directory](/) 2. / 3. [Database & ORM](/categories/database) 4. / 5. adt/base-query ActiveLibrary[Database & ORM](/categories/database) adt/base-query ============== Doctrine components v3.3.2(2mo ago)017.3k↓39.3%3[4 PRs](https://github.com/AppsDevTeam/doctrine-components/pulls)MITPHPPHP >=8.4 Since Oct 14Pushed 2mo ago12 watchersCompare [ Source](https://github.com/AppsDevTeam/doctrine-components)[ Packagist](https://packagist.org/packages/adt/base-query)[ Docs](https://www.appsdevteam.com)[ RSS](/packages/adt-base-query/feed)WikiDiscussions master Synced 1mo ago READMEChangelog (10)Dependencies (10)Versions (93)Used By (0) Doctrine Components =================== [](#doctrine-components) Install ------- [](#install) ``` composer require adt/doctrine-components ``` Creating a QueryObject class ---------------------------- [](#creating-a-queryobject-class) ``` /** * Annotations "extends" and "implements" and interface "FetchInterface" are used for PhpStorm code completion and PHPStan. * * @extends QueryObject * @implements FetchInterface */ class ProfileQueryObject extends QueryObject implements FetchInterface { const FILTER_SECURITY = 'filter_security'; const FILTER_IS_ACTIVE = 'filter_is_active'; private SecurityUser $securityUser; protected function getEntityClass(): string { return Profile::class; } protected function init(): void { parent::init(); $this->filter[self::FILTER_SECURITY] = function (QueryBuilder $qb) { if (!$this->securityUser->isAllowed('global.users')) { $qb->andWhere('e.id = :init_id') ->setParameter('id', $this->securityUser->getId()) } }; $this->byIsActive(true); } protected function setDefaultOrder(): void { $this->orderBy(['identity.lastName' => 'ASC', 'identity.firstName' => 'ASC', 'id' => 'ASC']); } public function byIsActive(bool $isActive): static { $this->filter[self::FILTER_IS_ACTIVE] = function(QueryBuilder $qb) use ($isActive) { $qb->andWhere('e.isActive = :isActive') ->setParameter('isActive', $isActive); }; return $this; } public function byQuery(string $query): static { $this->by(['identity.firstName', 'identity.lastName', 'identity.email', 'identity.phone'], $query); return $this; } public function setSecurityUser(SecurityUser $securityUser): static { $this->securityUser = $securityUser; return $this; } } ``` ### Method `getEntityClass` [](#method-getentityclass) The `getEntityClass` method must be specified and return your entity class. ### Method `setDefaultOrder` [](#method-setdefaultorder) The `setDefaultOrder` method must be specified and set the default order. ### Method `init` [](#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` [](#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-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 ----------- [](#basic-usage) ### Creating an instance [](#creating-an-instance) ``` $queryObject = (new ProfileQueryObject($entityManager))->setSecurityUser($securityUser); ``` or better with the use of a factory: ``` // example of Nette framework factory interface ProfileQueryObjectFactory { /** * Annotation is used for PhpStorm code completion. * * @return FetchInterface */ public function create(): ProfileQueryObject; } ``` together with neon: ``` decorator: ADT\DoctrineObjects\QueryObject: setup: - setEntityManager(@App\Model\Doctrine\EntityManager) - setSecurityUser(@security.user) ``` ### Fetch results [](#fetch-results) ``` // returns all active profiles $profiles = $this->profileQueryObjectFactory->create()->fetch(); // returns all active profiles with name, email or phone containing "Doe" $profiles = $this->profileQueryObjectFactory->create()->search('Doe')->fetch(); // returns all disabled profiles $profiles = $this->profileQueryObjectFactory->create()->byIsActive(false)->fetch(); // returns first 10 active profiles $profiles = $this->profileQueryObjectFactory->create()->fetch(limit: 10); ``` ``` // returns an active profile by ID or throws your own error when a profile does not exist if (!$profile = $this->profileQueryObjectFactory->create()->byId($id)->fetchOneOrNull()) { return new \Exception('Profile not found.'); } // returns first active profile with name, name, email or phone containing "Doe", "strict: false" has to be specified, // otherwise NonUniqueResultException may be thrown $profile = $this->profileQueryObjectFactory->create()->search('Doe')->fetchOneOrNull(strict: false); ``` ``` // returns an active profile by ID or throws NoResultException when profile does not exist $profile = $this->profileQueryObjectFactory->create()->byId(self::ADMIN_PROFILE_ID)->fetchOne(); ``` ``` // returns an active profile as an array of {Profile::getId(): Profile::getName()} $profiles = $this->profileQueryObjectFactory->create()->fetchPairs('name', 'id'); ``` ``` // returns array of active profile ids $profileIds = $this->profileQueryObjectFactory->create()->fetchField('id'); ``` ### Count results [](#count-results) ``` // returns number of all active profiles $numberOfProfiles = $this->profileQueryObjectFactory->create()->count(); ``` ### Disable default filters [](#disable-default-filters) ``` // returns both active and disabled profiles $profiles = $this->profileQueryObjectFactory->create()->disableFilter(ProfileQueryObject::FILTER_IS_ACTIVE)->fetch(); // returns all profiles without applying a default security filter, for example in console $profiles = $this->profileQueryObjectFactory->create()->disableFilter(ProfileQueryObject::FILTER_SECURITY)->fetch(); // disable both filters $profiles = $this->profileQueryObjectFactory->create()->disableFilter([ProfileQueryObject::FILTER_IS_ACTIVE, ProfileQueryObject::FILTER_SECURITY])->fetch(); ``` ### Pagination [](#pagination) ``` // returns ResultSet, suitable for pagination and for using in templates $profileResultSet = $this->profileQueryObjectFactory->create()->getResultSet(page: 1, itemsPerPage: 10); // ResultSet implements IteratorAggregate, so you can use it in foreach foreach ($profileResultSet as $_profile) { echo $_profile->getId(); } // or call getIterator $profiles = $profileResultSet->getIterator(); // returns Nette\Utils\Paginator $paginator = $profileResultSet->getPaginator(); // returns total count of profiles $numberOfProfiles = $profileResultSet->count(); ``` Advanced features ----------------- [](#advanced-features) ### Manul joins [](#manul-joins) For manual joins you should use `innerJoin` and `leftJoin` methods: ``` public function joinArtificialConsultant(QueryBuilder $qb) { $this->leftJoin($qb, 'e.artificialConsultant', 'e_ac'); } public function byShowOnWeb(): static { $this->filter[] = function (QueryBuilder $qb) { $this->joinArtificialConsultant($qb); $qb->andWhere('e.showOnWeb = TRUE OR (e.artificialConsultant IS NOT NULL AND e_ac.showOnWeb = TRUE)'); }; return $this; } ``` Unlike `QueryBuilder::innerJoin` and `QueryBuilder::leftJoin`, this ensures that same joins are not used multiple times and don't throw an error. ### More columns [](#more-columns) Don't use `addSelect` inside a `filter` callback. Use `initSelect` method instead: ``` class OfficeMessageGridQuery extends OfficeMessageQuery { protected function initSelect(QueryBuilder $qb): void { parent::initSelect($qb); $qb->addSelect('e.id'); $adSub = $qb->getEntityManager() ->getRepository(Entity\MessageRecipient::class) ->createQueryBuilder('mr_read') ->select('COUNT(1)') ->where('e = mr_read.officeMessage AND mr_read.readAt IS NOT NULL'); $qb->addSelect('(' . $adSub->getDQL() . ') read'); } } ``` Or create your own fetching method: ``` class IdentityStatisticsQueryObject extends IdentityQueryObject { /** * @return array{'LT25': int, 'BT25ND35': int, 'BT35N45': int, 'BT45N55': int, 'BT55N65': int, 'GT65': int} */ public function getAgeRange(): array { $qb = $this->createQueryBuilder(withSelectAndOrder: false); $qb->addSelect(' SUM(CASE WHEN TIMESTAMPDIFF(YEAR, ei.birthDate, CURRENT_DATE()) < 25 THEN 1 ELSE 0 END) AS ' . UserEnum::AGE_RANGE_LT25 . ', SUM(CASE WHEN TIMESTAMPDIFF(YEAR, ei.birthDate, CURRENT_DATE()) >= 25 AND TIMESTAMPDIFF(YEAR, ei.birthDate, CURRENT_DATE()) < 35 THEN 1 ELSE 0 END) AS ' . UserEnum::AGE_RANGE_BETWEEN_25N35 . ', SUM(CASE WHEN TIMESTAMPDIFF(YEAR, ei.birthDate, CURRENT_DATE()) >= 35 AND TIMESTAMPDIFF(YEAR, ei.birthDate, CURRENT_DATE()) < 45 THEN 1 ELSE 0 END) AS ' . UserEnum::AGE_RANGE_BETWEEN_35N45 . ', SUM(CASE WHEN TIMESTAMPDIFF(YEAR, ei.birthDate, CURRENT_DATE()) >= 45 AND TIMESTAMPDIFF(YEAR, ei.birthDate, CURRENT_DATE()) < 55 THEN 1 ELSE 0 END) AS ' . UserEnum::AGE_RANGE_BETWEEN_45N55 . ', SUM(CASE WHEN TIMESTAMPDIFF(YEAR, ei.birthDate, CURRENT_DATE()) >= 55 AND TIMESTAMPDIFF(YEAR, ei.birthDate, CURRENT_DATE()) < 65 THEN 1 ELSE 0 END) AS ' . UserEnum::AGE_RANGE_BETWEEN_55N65 . ', SUM(CASE WHEN TIMESTAMPDIFF(YEAR, ei.birthDate, CURRENT_DATE()) > 65 THEN 1 ELSE 0 END) AS ' . UserEnum::AGE_RANGE_GT_65 . ' '); return $this->getQuery($qb)->getSingleResult(); } } ``` When there are more columns specified, default `fetch*` method won't work. ### More complex sorting [](#more-complex-sorting) You can create your own sorting callback instead of using `orderBy` method: ``` public function orderByClosestDistance($customerLongitude, $customerLatitude): static { $this->order = function (QueryBuilder $qb) use ($customerLongitude, $customerLatitude) { $qb->addSelect(' ( 6373 * acos( cos( radians(:obcd_latitude) ) * cos( radians( e.latitude ) ) * cos( radians( e.longitude ) - radians(:obcd_longitude) ) + sin( radians(:obcd_latitude) ) * sin( radians( e.latitude ) ) ) ) AS HIDDEN distance' ) ->addOrderBy('distance', 'ASC') ->setParameter('obcd_latitude', $customerLatitude) ->setParameter('obcd_longitude', $customerLongitude); }; return $this; } ``` Don't forget to use `AS HIDDEN` in your `addSelect` method, otherwise the `fetch*` methods won't work. ### Method `orById` [](#method-orbyid) If you want to get all active records plus a specific one, you can use `orById` method to bypass default filters: ``` $profiles = $this->profileQueryObjectFactory->create()->orById($id)->fetch(); ``` It's especially useful for ``. ### Use in batch processing [](#use-in-batch-processing) If you want to iterate large number of records, you can use together with query object: ``` $em = EntityManager::create($this->connection, $this->config); $profile = SimpleBatchIteratorAggregate::fromTraversableResult( $this->profileQueryObjectFactory->create()->setEntityManager($em)->fetchIterable(), $em, 100 // flush/clear after 100 iterations ); foreach ($profiles as $_profile) { } ``` You should always use new `EntityManager` instance, not the default one (because of `EntityManager::clear`). Tips ---- [](#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. ### Health Score 60 — FairBetter than 99% of packages Maintenance88 Actively maintained with recent releases Popularity28 Limited adoption so far Community18 Small or concentrated contributor base Maturity88 Battle-tested with a long release history Bus Factor1 Top contributor holds 81.3% of commits — single point of failure How is this calculated?**Maintenance (25%)** — Last commit recency, latest release date, and issue-to-star ratio. Uses a 2-year decay window. **Popularity (30%)** — Total and monthly downloads, GitHub stars, and forks. Logarithmic scaling prevents top-heavy scores. **Community (15%)** — Contributors, dependents, forks, watchers, and maintainers. Measures real ecosystem engagement. **Maturity (30%)** — Project age, version count, PHP version support, and release stability. ### Release Activity Cadence Every ~23 days Total 87 Last Release 60d ago Major Versions v1.11 → v2.02022-05-15 v2.13.1 → v3.02025-05-11 PHP version history (7 changes)v1.0PHP >=7.2 v2.0PHP >=7.4 v2.1PHP >=8.0 v2.3PHP >=8.1 v2.12.1PHP >=8.2 v3.0.7PHP >=8.3 v3.1PHP >=8.4 ### Community Maintainers ![](https://www.gravatar.com/avatar/6ab62e3b1b51d124387f69d423b6de47d0006c75007b97f530ee87397291730e?d=identicon)[thorewi](/maintainers/thorewi) --- Top Contributors [![thorewi](https://avatars.githubusercontent.com/u/605858?v=4)](https://github.com/thorewi "thorewi (148 commits)")[![masicek](https://avatars.githubusercontent.com/u/1160736?v=4)](https://github.com/masicek "masicek (16 commits)")[![vpalousek](https://avatars.githubusercontent.com/u/108008045?v=4)](https://github.com/vpalousek "vpalousek (10 commits)")[![Walusyak](https://avatars.githubusercontent.com/u/20837611?v=4)](https://github.com/Walusyak "Walusyak (5 commits)")[![michallohnisky](https://avatars.githubusercontent.com/u/4747059?v=4)](https://github.com/michallohnisky "michallohnisky (3 commits)") --- Tags doctrineappsdevteam ### Embed Badge ![Health badge](/badges/adt-base-query/health.svg) ``` [![Health](https://phpackages.com/badges/adt-base-query/health.svg)](https://phpackages.com/packages/adt-base-query) ``` ### Alternatives [knplabs/doctrine-behaviors Doctrine Behavior Traits 92212.7M64](/packages/knplabs-doctrine-behaviors)[scienta/doctrine-json-functions A set of extensions to Doctrine that add support for json query functions. 58723.9M36](/packages/scienta-doctrine-json-functions)[laravel-doctrine/orm An integration library for Laravel and Doctrine ORM 8425.3M87](/packages/laravel-doctrine-orm)[damienharper/auditor-bundle Integrate auditor library in your Symfony projects. 4542.8M](/packages/damienharper-auditor-bundle)[nettrine/orm Doctrine ORM for Nette Framework 581.9M37](/packages/nettrine-orm)[kdyby/doctrine Doctrine integration into Nette Framework 1091.0M86](/packages/kdyby-doctrine) PHPackages © 2026 [Directory](/)[Categories](/categories)[Trending](/trending)[Changelog](/changelog)[Analyze](/analyze)