How to join two entities with doctrine 2 and zf2?

Question

I have two entities, Carros and Msg, i am looking to get Carros that have Msg messages

$query = $entityManager->createQuery("
    SELECT u 
    FROM Auto\Entity\Carros u
    JOIN Auto\Entity\Msg m WITH m.idautoad=u.idcarros
    WHERE u.identidade='".$emailidentidade."'
    ORDER BY u.datadoanuncio DESC
    ");

I'm using the paginator:

    // Create the paginator itself
$paginator = new Paginator(
        new DoctrinePaginator(new ORMPaginator($query))
);

and i am getting the following errors i have zend 2.3.9 and doctrine 2.4

Arquivo: C:\websites\auto\vendor\zendframework\zendframework\library\Zend\Paginator\Paginator.php:637

Mensagem: Error producing an iterator

C:\websites\auto\vendor\doctrine\orm\lib\Doctrine\ORM\Tools\Pagination\WhereInWalker.php:85

Mensagem:

Cannot count query which selects two FROM components, cannot make distinction

its generating the error when i try to do this :

foreach ($paginator as $carro)
{}

The error disappears when getting the results like this :

$fi = $query->getResult();

and then

  $paginator = new \Zend\Paginator\Paginator(new
                \Zend\Paginator\Adapter\ArrayAdapter($fi)
          );

Have you look stackoverflow.com/questions/12785143/… that? — hkulekci
Do you have ManyToOne relationship between Carros and Msg ? If you do, check this : docs.doctrine-project.org/projects/doctrine-orm/en/latest/… At the end with this mapping bidirectional, you don't need to make a join, in your query and you can solve your problem. — Greco Jonathan

Greco Jonathan Greco Jonathan · Accepted Answer · 2015-07-02T08:11:47

In case you have a ManyToOne relationship you can do like this (be sure to see this link before to make your mapping correct : Bidirectionnal ManyToOne relation

 public function getInvoices($idProformaGroup, $paginate = false)
    {
        $query = $this->getEntityManager()->createQueryBuilder();
        $query->select('po', 'i')
            ->from('Invoice\Entity\Proforma', 'po')
            ->innerJoin('po.idInvoice', 'i')
            ->andWhere("po.idProformaGroup = :idProformaGroup")
            ->orderBy('po.idProforma', 'ASC')
            ->setParameter('idProformaGroup', $idProformaGroup);
        if ($paginate) {
            $doctrineAdapter = new DoctrineAdapter(new ORMPaginator($query, true));
            return new Paginator($doctrineAdapter);
        } else {
            return $query->getQuery()->getResult();
        }
    }

Like you see, my join use the foreign key po.idInvoice to join the two tables. And because of this my Paginator don't show me your error.

EDIT : With a param I can decide to paginate or not. Don't use it if you don't need it.

EDIT 2 : From the link to the other question join before Doctrine : Pagination with left Joins The futurereal's answer is the same point I tried to explain to you.

How to join two entities with doctrine 2 and zf2?

2 Answers