链接Symfony存储库方法（过滤器）

Question

What is the best practice to chain repository methods to reuse query building logic?

Here is how I did it, but I doubt if this is the right way:

use Doctrine\ORM\Mapping;
use Doctrine\ORM\EntityManager;


class OrderRepository extends \Doctrine\ORM\EntityRepository
{

    private $q;

    public function __construct(EntityManager $em, Mapping\ClassMetadata $class)
    {
        parent::__construct($em, $class);
        $this->q = $this->createQueryBuilder('o');
    }

    public function getOneResult()
    {
        return $this->q->getQuery()->getOneOrNullResult();
    }

    public function getResult()
    {
        return $this->q->getQuery()->getResult();
    }

    public function filterByStatus($status)
    {

        $this->q->andWhere('o.status = :status')->setParameter('status', $status);
        return $this;
    }

    public function findNextForPackaging()
    {

        $this->q->leftjoin('o.orderProducts', 'p')
            ->orderBy('o.deliveryDate', 'ASC')
            ->andHaving('SUM(p.qtePacked) < SUM(p.qte)')
            ->groupBy('o.id')
            ->setMaxResults(1);

        return $this;
    }

}

This allows me to chain method like this:

$order = $em->getRepository('AppBundle:Order')->filterByStatus(10)->findNextForPackaging()->getOneResult();

This is of course just an example. In reality there are many more methods that can be chained.

One big problem with this is the fact that I need a join for some of the "filters", so I have to check if the join has already been set by some method/filter before I add it. ( I did not put it in the example, but I figured it out, but it is not very pretty )

The other problem is that I have to be careful when using the repository, as the query could already be set to something, so I would need to reset the query every time before using it.

I also understand that I could use the doctrine "matching" method with criteria, but as far as I understood, this is rather expensive, and also, I don't know how to solve the "join" Problem with that approach.

Any thoughts?

Answer 1

I made something similar to what you want:

Controller, this is how you use it. I am not returning Response instance but serialize the array in kernel.view listener but it is still valid example:

/**
 * @Route("/root/pending_posts", name="root_pending_posts")
 * @Method("GET")
 *
 * @return Post[]
 */
public function pendingPostsAction(PostRepository $postRepository, ?UserInterface $user): array
{
    if (!$user) {
        return [];
    }

    return $postRepository->begin()
        ->wherePublished(false)
        ->whereCreator($user)
        ->getResults();
}

PostRepository:

class PostRepository extends BaseRepository
{
    public function whereCreator(User $user)
    {
        $this->qb()->andWhere('o.creator = :creator')->setParameter('creator', $user);

        return $this;
    }

    public function leftJoinRecentComments(): self
    {
        $this->qb()
            ->leftJoin('o.recentCommentsReference', 'ref')->addSelect('ref')
                ->leftJoin('ref.comment', 'c')->addSelect('c');

        return $this;
    }

    public function andAfter(\DateTime $after)
    {
        $this->qb()->andWhere('o.createdAt > :after')->setParameter('after', $after);

        return $this;
    }

    public function andBefore(\DateTime $before)
    {
        $this->qb()->andWhere('o.createdAt < :before')->setParameter('before', $before);

        return $this;
    }

    public function wherePublished(bool $bool)
    {
        $this->qb()->andWhere('o.isPending = :is_pending')->setParameter('is_pending', !$bool);

        return $this;
    }
}

and BaseRepository has most used stuff, still work in progress:

namespace wjb\CoreBundle\Model;

use Doctrine\ORM\EntityRepository;
use Doctrine\ORM\QueryBuilder;

abstract class BaseRepository extends EntityRepository
{
    /** @var  QueryBuilder */
    private $qb;

    public function begin()
    {
        $this->qb = $this->createQueryBuilder('o');

        return $this;
    }

    public function qb(): QueryBuilder
    {
        return $this->qb;
    }

    public function none()
    {
        $this->qb()->where('o.id IS NULL');

        return $this;
    }

    public function setMaxResults($maxResults)
    {
        $this->qb()->setMaxResults($maxResults);

        return $this;
    }

    public function addOrderBy($sort, $order = null)
    {
        $this->qb()->addOrderBy($sort, $order);

        return $this;
    }

    public function getResults()
    {
        return $this->qb()->getQuery()->getResult();
    }
}

This helps me a lot in chaining calls like in controller example.