[solved]Problem using querybuilder

[kukagres]

Hi!
I have a problem. I've got a participant class wich is equivalent to user. The thing is, when I show the list of participants, I do it in a special way, by showing only 20 participants at a time instead of the whole list. I use a variable page that tells me what page is that I have to show, and I use it for calculate the offset from which to show. The problem is that is not showing me the users or just show me some of them. Also I count the total users in the same action but the number doesn't match with the real users. The code that I use in the controller is:

Code: Select all

 
   /**
     * Lists all Participante entities.
     *
     * @Route("/list/{pag}", requirements={"pag" = "\d+"}, defaults={"pag" = 1})", name="participante_list")
     * @Template()
     */
    public function indexAction($pag)
    {
        $repository = $this->getDoctrine()->getRepository('NeodirectorioPhenixBundle:Participante');
        $offset=(($pag-1)*20)+1;
        $qb=$repository->createQueryBuilder('participantes');
        $qb->add('select', 'p')
           ->add('from', 'NeodirectorioPhenixBundle:Participante p')
           ->add('orderBy', 'p.nickname ASC')
           ->setFirstResult( $offset )
           ->setMaxResults(20);           
        $entities=$qb->getQuery()->getResult();
        $qb1 = $repository->createQueryBuilder('cantidad');
        $qb1->select('count(p.id)');
        $qb1->from('NeodirectorioPhenixBundle:Participante','p');
        $count = $qb1->getQuery()->getSingleScalarResult();
        return array(
            'entities' => $entities,
            'cant' => $count
        );
    }


[kukagres]

Hi again!

I have made some modifications to the code, and now i think that the count part is ok, but the list part isn't ok.

Code: Select all

    /**
     * Lists all Participante entities.
     *
     * @Route("/list/{pag}", requirements={"pag" = "\d+"}, defaults={"pag" = 1})", name="participante_list")
     * @Template()
     */
    public function indexAction($pag)
    {
        //$repository = $this->getDoctrine()->getRepository('NeodirectorioPhenixBundle:Participante');
        if ($pag==1)
        {
            $offset=1;
        }
        else
        {
            $offset=(($pag-1)*20)+1;
        }           
        //$qb=$repository->createQueryBuilder('participantes');
        $em = $this->getDoctrine()->getManager();
        $qb = $em->createQueryBuilder();   
        /*$qb->add('select', 'p')
           ->add('from', 'NeodirectorioPhenixBundle:Participante p')
           ->add('orderBy', 'p.nickname ASC')
           ->setFirstResult( $offset )
           ->setMaxResults(20);*/ 
        $qb->select('p');
        $qb->from('NeodirectorioPhenixBundle:Participante','p');
        $qb->orderBy('p.nickname','ASC');
        $qb->setFirstResult($offset);
        $qb->setMaxResults(20);       
        $query = $qb->getQuery();
        $entities=$query->getResult(); 
        //$qb1 = $repository->createQueryBuilder('cuenta');
        $qb1 = $em->createQueryBuilder();
        $qb1->select('count(p.id)');
        $qb1->from('NeodirectorioPhenixBundle:Participante','p');
        $query1 = $qb1->getQuery();
        $count = $query1->getSingleScalarResult();
        return array(
            'entities' => $entities,
            'cant' => $count,
        );
    }


[dreipunktnull]

There is a built-in pagination functionality in Doctrine you should use here. You can't use it in a query builder though but that isn't necessary anyway.

[kukagres]

Hi! I think I've solved the problem. I think the problem was the following statement:

$offset=(($pag-1)*20)+1; //I have changed this, and work --> "($pag-1)*20"

Still, that does not work, but I think the problem stemmed elsewhere. However, this problem using QueryBuilder, is solved. : D