surda/doctrine-queries

Doctrine queries

v2.0.0 2023-12-14 09:01 UTC

Requires

• php: >=7.1
• doctrine/orm: ^2.6
• nette/utils: ^3.0 || ^4.0

Requires (Dev)

• nette/tester: ^2.0
• ninjify/nunjuck: ^0.3
• phpstan/phpstan: ^0.12
• phpstan/phpstan-deprecation-rules: ^0.12
• phpstan/phpstan-nette: ^0.12
• phpstan/phpstan-strict-rules: ^0.12

MIT

• Radek Šuráni

• doctrine
• nette

README

This repository is inspired by Kdyby/Doctrine.

Installation

The recommended way to is via Composer:

composer require surda/doctrine-queries

Usage

Entity repository

use Surda\Doctrine\Queries\QueryableRepository;

class UserRepository extends QueryableRepository
{
   // ...
}

QueryObject

use Doctrine\ORM;
use Surda\Doctrine\Queries\IQueryable;
use Surda\Doctrine\Queries\QueryObject;

class QuestionsQuery extends QueryObject
{
    /** @var array|\Closure[] */
    private $filter = [];

    /** @var array|\Closure[] */
    private $select = [];

    public function inCategory(Category $category = NULL)
    {
        $this->filter[] = function (QueryBuilder $qb) use ($category) {
            $qb->andWhere('q.category = :category')->setParameter('category', $category->getId());
        };
        return $this;
    }
    
    public function byUser($user)
    {
        if ($user instanceof Identity) {
            $user = $user->getUser();

        } elseif (!$user instanceof User) {
            throw new InvalidArgumentException;
        }

        $this->filter[] = function (QueryBuilder $qb) use ($user) {
            $qb->andWhere('u.id = :user')->setParameter('user', $user->getId());
        };
        return $this;
    }

    public function withLastPost()
    {
        $this->select[] = function (ORM\QueryBuilder $qb) {
            $qb->addSelect('partial lp.{id, createdAt}, partial lpa.{id}, partial lpau.{id, name}')
                ->leftJoin('q.lastPost', 'lp', Join::WITH, 'lp.spam = FALSE AND lp.deleted = FALSE')
                ->leftJoin('lp.author', 'lpa')
                ->leftJoin('lpa.user', 'lpau');
        };
        return $this;
    }

    public function withCategory()
    {
        $this->select[] = function (ORM\QueryBuilder $qb) {
            $qb->addSelect('c, pc')
                ->innerJoin('q.category', 'c')
                ->innerJoin('c.parent', 'pc');
        };
        return $this;
    }

    public function withAnswersCount()
    {
        $this->select[] = function (ORM\QueryBuilder $qb) {
            $subCount = $qb->getEntityManager()->createQueryBuilder()
                ->select('COUNT(a.id)')->from(Answer::class, 'a')
                ->andWhere('a.spam = FALSE AND a.deleted = FALSE')
                ->andWhere('a.question = q');

            $qb->addSelect("($subCount) AS answers_count");
        };
        return $this;
    }

    public function sortByPinned($order = 'ASC')
    {
        $this->select[] = function (ORM\QueryBuilder $qb) use ($order) {
            $qb->addSelect('FIELD(q.pinned, TRUE, FALSE) as HIDDEN isPinned');
            $qb->addOrderBy('isPinned', $order);
        };
        return $this;
    }

    public function sortByHasSolution($order = 'ASC')
    {
        $this->select[] = function (ORM\QueryBuilder $qb) use ($order) {
            $qb->addSelect('FIELD(IsNull(q.solution), TRUE, FALSE) as HIDDEN hasSolution');
            $qb->addOrderBy('hasSolution', $order);
        };
        return $this;
    }

    /**
     * @param IQueryable $repository
     * @return ORM\QueryBuilder
     */
    protected function doCreateQuery(IQueryable $repository)
    {
        $qb = $this->createBasicDql($repository)
            ->addSelect('partial i.{id}, partial u.{id, name}');

        foreach ($this->select as $modifier) {
            $modifier($qb);
        }

        return $qb->addOrderBy('q.createdAt', 'DESC');
    }

    /**
     * @param IQueryable $repository
     * @return ORM\QueryBuilder
     */
    protected function doCreateCountQuery(IQueryable $repository)
    {
        return $this->createBasicDql($repository)->select('COUNT(q.id)');
    }

    /**
     * @param IQueryable $repository
     * @return ORM\QueryBuilder
     */
    private function createBasicDql(IQueryable $repository)
    {
        $qb = $repository->createQueryBuilder()
            ->select('q')->from(Question::class, 'q')
            ->andWhere('q.spam = FALSE AND q.deleted = FALSE')
            ->innerJoin('q.author', 'i')
            ->innerJoin('i.user', 'u');

        foreach ($this->filter as $modifier) {
            $modifier($qb);
        }

        return $qb;
    }
}

$query = (new QuestionsQuery())
	->withLastPost()
	->byUser($user);

$result = $repository->fetch($query);

ResultSet

class Repository extends \Surda\Doctrine\Queries\EntityRepository
{
    /**
     * @return \Surda\Doctrine\Queries\ResultSet
     */
    public function getResultSet(): \Surda\Doctrine\Queries\ResultSet
    {
        $query = $this->createQueryBuilder('q')->addOrderBy('q.id', 'DESC')->getQuery();

        return new \Surda\Doctrine\Queries\ResultSet($query);
    }
}

Presenter

$visualPaginator = $this['vp'];
$paginator = $visualPaginator->getPaginator();
$paginator->setItemsPerPage(20);

$resultSet = $this->repository->getResultSet();
$resultSet->applyPaginator($paginator);

foreach ($resultSet as $entity) {
	// ...
}