PHP code example of adt / base-query

    
        
<?php
require_once('vendor/autoload.php');

/* Start to develop here. Best regards https://php-download.com/ */

    

adt / base-query example snippets

/**
 * Annotations "extends" and "implements" and interface "FetchInterface" are used for PhpStorm code completion and PHPStan.
 * 
 * @extends QueryObject<Profile>
 * @implements FetchInterface<Profile>
 */
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;
	}
}

$queryObject = (new ProfileQueryObject($entityManager))->setSecurityUser($securityUser);

// example of Nette framework factory
interface ProfileQueryObjectFactory
{
	/** 
	 * Annotation is used for PhpStorm code completion.
	 * 
	 * @return FetchInterface<Profile> 
	 */
	public function create(): ProfileQueryObject;
}

// 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');

// returns number of all active profiles
$numberOfProfiles = $this->profileQueryObjectFactory->create()->count();

// 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();

// 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();

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;
}

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');
	}
}

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();
	}
}

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;
}

$profiles = $this->profileQueryObjectFactory->create()->orById($id)->fetch();

$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) {
	
}