CODEWITHSUNDEEP
phpsymfonymodel-view-controllerstructurequery-builder
Alexandre
Alexandre

Reputation: 3170

Symfony2: Where to put request to db build with QueryBuilder

I am new by using Symfony and I am a bit confused concerning the structure. I made a custom query with the queryBuilder but I don't really know where to put this piece of code. It work inside the controller but I don't like to have interaction with db there. So I though to put it inside an entity repository but it's really limited and I am not able to use it for my case. This query make a request with join and filter by multiple column.

So where could I put this code?

Edit1:

    $repository = $this->getDoctrine()->getRepository('EgCBGEBundle:portfoliohistory_pfh');
    $queryBuilder = $repository->createQueryBuilder('pfh');
    $queryBuilder->innerJoin('pfh.portfolio', 'pfo', \Doctrine\ORM\Query\Expr\Join::WITH, 'pfh.id_pfo = pfo.id_pfo');
    $queryBuilder->where('pfh.scenario = :scenario')
                 ->andWhere('pfh.measure_catalogue = :measureCatalogue')
                 ->andWhere('pfh.portfolio IN ('.$subs.')')
                 ->andWhere('pfh.date = :date')
                 ->setParameter('scenario', $scenario)
                 ->setParameter('measureCatalogue', $type)
                 ->setParameter('date', $date)
                 ->groupBy('pfh.portfolio, pfh.id') // , pfo.id
                 //->orderBy('pfo.id', 'ASC')
                 ->setMaxResults(5);
    return $queryBuilder->getQuery()->getResult();

Upvotes: 0

Views: 3106

Answers (3)

user2094540
user2094540

Reputation:

QueryBuilder is part of Doctrine. You can use it in your controller or in a custom class. Here is a part of the official doc http://doctrine-orm.readthedocs.org/en/latest/reference/query-builder.html

http://www.doctrine-project.org/api/orm/2.4/class-Doctrine.ORM.QueryBuilder.html

And an example : http://drafts.easybib.com/post/44139111915/taiming-repository-classes-in-doctrine-with-the

Upvotes: 0

Pi Wi
Pi Wi

Reputation: 1085

Add it to your custom repository: http://docs.doctrine-project.org/en/2.0.x/reference/working-with-objects.html#custom-repositories

Class YourEntityRepository extends EntityRepository
{
   public function findMyQuery($scenario, $type, $date){

     $queryBuilder = $this->createQueryBuilder('pfh');
     $queryBuilder->innerJoin('pfh.portfolio', 'pfo', \Doctrine\ORM\Query\Expr\Join::WITH, 'pfh.id_pfo = pfo.id_pfo');
     $queryBuilder->where('pfh.scenario = :scenario')
             ->andWhere('pfh.measure_catalogue = :measureCatalogue')
             ->andWhere('pfh.portfolio IN ('.$subs.')')
             ->andWhere('pfh.date = :date')
             ->setParameter('scenario', $scenario)
             ->setParameter('measureCatalogue', $type)
             ->setParameter('date', $date)
             ->groupBy('pfh.portfolio, pfh.id') // , pfo.id
             //->orderBy('pfo.id', 'ASC')
             ->setMaxResults(5);
    return $queryBuilder->getQuery()->getResult();

}

And don't forget to include it in your entity:

/**
 * @ORM\entity(repositoryClass="My\Bundle\Entity\YourEntityRepository ")
 */

In your controller

$results= $em->getRepository('My\Bundle\Entity\YourEntity')->findMyQuery($scenario, $type, $date);

Upvotes: 3

goto
goto

Reputation: 8162

You should put it in your repository

class YourEntityRepository extends EntityRepository
{
   public function findItWithACustomQuery($some_params){

     $queryBuilder = $this->createQueryBuilder('pfh');
     $queryBuilder->innerJoin('pfh.portfolio', 'pfo', \Doctrine\ORM\Query\Expr\Join::WITH, 'pfh.id_pfo = pfo.id_pfo');
     $queryBuilder->where('pfh.scenario = :scenario')
             ->andWhere('pfh.measure_catalogue = :measureCatalogue')
             ->andWhere('pfh.portfolio IN ('.$subs.')')
             ->andWhere('pfh.date = :date')
             ->setParameter('scenario', $scenario)
             ->setParameter('measureCatalogue', $type)
             ->setParameter('date', $date)
             ->groupBy('pfh.portfolio, pfh.id') // , pfo.id
             //->orderBy('pfo.id', 'ASC')
             ->setMaxResults(5);
    return $queryBuilder->getQuery()->getResult();

}

Then you just call it in your controller or service by

$em->getRepository('YourEntityBundle:YourEntity')->findItWithACustomQuery($params);

Upvotes: 1

Related Questions