Code Style: Doctrine2 queries from entity repository?

Question

As I understand the best code style is to put the complex SQL/DQL queries to the entity repositories.

For example there is an entity named "News". It has an own entity repository named "NewsRepository".

In the controller there is this code:

/**
 * @Route("/news", name="news")
 */
public function indexAction(Request $request)
{
    $em = $this->getDoctrine()->getManager();
    $paginator = $this->get('knp_paginator');

    $news = $paginator->paginate(
        $em->createQuery('SELECT n FROM AppBundle:News n'),
        $request->query->getInt('page', 1),
        10
    );

    return $this->render('app/news/list.html.twig', array(
        'news' => $news,
    ));
}

Now I like to add further features (filtering, order by, ..). That because I think the query should be moved to any service or the entity repository. But how and what is the best coding style?

(And does anybody have nice generic ideas how to easily add filtering, order by ... ?)

Answer 1

Sure,

Every request should be in the repository if you want use it again. Not only complexiest.

I'll try to answer your first question and after give you a tip that could help you with your "generic filtering"

If you want to put your request in the repo just make in your NewsRepository :

public function findAllOrderedByDate() {
    $qb = $this->createQueryBuilder('n');
    $qb->orderBy('creationDate');

    return $qb->getQuery()->getResult();
}

And in your controller :

public function indexAction(Request $request)
{
    $newsRepo = $this->get('doctrine')->getRepository('AppBundle:News');
    $em = $this->getDoctrine()->getManager();
    $paginator = $this->get('knp_paginator');

    $news = $newsRepo->findAllOrderedByDate();
    $pagination = $paginator->paginate(
        $news,
        $request->query->getInt('page', 1),
        10
    );

    return $this->render('app/news/list.html.twig', array(
        'news' => $pagination,
    ));
}

For the filtering, you have a trick in your repor that consist by returning the qb and not directly results.

As well, you can make a function that add you orderBy with a given parameter (using addOrderBy() or andWhere) and which return the queryBuilder. After all of that you can process.

EDIT :

---

The solution i read on this thread :

public function findAllOrderedByDate(callable $func = null) {
    $qb = $this->createQueryBuilder('n');
    $qb->orderBy('creationDate');

    if (is_callable($func)) {
        return $func($qb);
    }
    return $qb->getQuery()->getResult();
}

and in your controller :

$func = function (QueryBuilder $qb) use ($paginator, $request) {
    return $paginator->paginate($qb, $request->query->getInt('page', 1), 10);
};
$pagination = $em->getRepository('AppBundle:News')->findAllOrderedByDate($func);