ManyToMany with extra field, limit results

Showing 1-4 of 4 messages
ManyToMany with extra field, limit results Guga 11/7/12 8:04 AM
Hi

Is there someone what can help me? I have a ManyToMany relationship with extra fields, my schema is this one (I'm using Symfony2):

Articulo -> articles
ArticuloAutor -> Intermediate table, with extra fields
Usuario -> users

++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Articulo:
--------------------------------------------------------------------------------------------------
/**
 * @ORM\OneToMany(targetEntity="Gse\AppBundle\Entity\ArticuloAutor", mappedBy="articulo")
 * @ORM\OrderBy({"orden" = "ASC"})
 */
 protected $autores;


 public function __construct()
 {
     $this->autores = new ArrayCollection();
            ....
   }
--------------------------------------------------------------------------------------------------

++++++++++++++++++++++++++++++++++++++++++++++++++++++++
ArticuloAutor:
--------------------------------------------------------------------------------------------------
    /**
     * @ORM\Id
     * @ORM\ManyToOne(targetEntity="Gse\AppBundle\Entity\Articulo", inversedBy="autores")
     */
    protected $articulo;

    /**
     * @ORM\Id
     * @ORM\ManyToOne(targetEntity="Gse\AppBundle\Entity\Usuario")
     */
    protected $usuario;

    // extras...

    /**
     * @var integer $orden
     *
     * @ORM\Column(type="integer", nullable=true)
     */
    private $orden;
----------------------------------------------------------------------------------------------------

If in my respository ArticuloRepository I do:

$articulos =
            $this->createQueryBuilder('a')
            ->addSelect(array('u', 'au'))
            ->leftJoin('a.autores', 'au')
            ->innerJoin('au.usuario', 'u')
            ->orderBy('a.fecha', 'DESC')
            ->getQuery()
            ->getResult();

All is Perfect!! But, if I set a ->setMaxResult(10) (LIMIT 10), it return me 2 articles, with 5 authors each one, because, is applying the limit to all the registers, and not only to articles, any help?

Thanks a lot, and sorry my english!

Re: [doctrine-user] ManyToMany with extra field, limit results Jàπ (Jasper N. Brouwer) 11/7/12 8:25 AM
When joining 2 (or more) entities, you cannot limit on root-entities only. You will have to do this in 2 steps:

In your case I suspect you want to reduce the number of queries needed to get the results (I don't see a WHERE statement). You could do it this way:

$q = $em->createQuery( 'SELECT a.id FROM Articulo a ORDER BY a.fecha DESC' );
$q->setMaxResult( 10 );

$ids = $q->getArrayResult();

$q = $em->createQuery( 'SELECT a, au, u FROM Articulo a LEFT JOIN a.autores au JOIN au.usuario u WHERE a.id IN :ids ORDER BY a.fecha DESC' );
$q->setParameter( 'ids', $ids );

$result = $q->getResult();


PS: If you are doing this for pagination, take a look at http://docs.doctrine-project.org/en/latest/tutorials/pagination.html

--
Jasper N. Brouwer
Re: [doctrine-user] ManyToMany with extra field, limit results Guga 11/12/12 7:55 AM
Thanks a lot for you reply Jasper!! You are right.

My final code is it:

---------------------
    public function findForTimeLine($fechaDesde, $inicio, $cantidad=5, $criterios=array())
    {
        $qb =
            $this->createQueryBuilder('a')
                ->select('a.id')
                ->where('a.fecha < :fecha')
                ->andWhere('a.es_activo = 1')
                ->orderBy('a.fecha', 'DESC')
                ->setParameter('fecha', $fechaDesde);

        if($criterios['busqueda'])
        {
            $qb = $qb->andWhere(
                $qb->expr()->orx(
                    $qb->expr()->like('a.titulo', ':query'),
                    $qb->expr()->like('a.resumen', ':query')
                )
            )->setParameter('query', "%{$criterios['busqueda']}%");
        }

        $qb = $qb
            ->setFirstResult($inicio)
            ->setMaxResults($cantidad);

        $articulos_ids = $qb->getQuery()->getArrayResult();

        if($articulos_ids)
        {
            $q =
                $this->createQueryBuilder('a')
                    ->addSelect(array('u', 'au'))
                    ->leftJoin('a.autores', 'au')
                    ->leftJoin('au.usuario', 'u')
                    ->where('a.id IN (:ids)')
                    ->setParameter('ids', $articulos_ids)
                    ->orderBy('a.fecha', 'DESC')
                    ->getQuery();

            return $q->getResult();
        }

        return null;
    }
---------------------

Thanks
Re: [doctrine-user] ManyToMany with extra field, limit results Miha Vrhovnik 11/20/12 8:47 AM
Or you could use the Pager component which will do this for you. You can find it in Doctrine\Tools\Pager namespace

Regards,
M