在zf2和Doctrine2中编写连接查询查询

Question

I have a query in zf2 and doctrine orm. I am joining the result of two queries on the same table by its primary_key.

It seems, I am making a syntax error and not able to find out.

$query = $this->getEntityManager()->createQuery(
    "select tc_result1.id as id ,tc_result1.displayId as displayId,tc_result1.activeFlag,tc_result1.hash
        from
        (
            SELECT *
            FROM (
                    SELECT id, display_id,active_flag,hash
                    FROM Test\Entity\TestCase tc_inner1
                    where tc_inner1.activeFlag=0 and tc_inner1.product = :productId
                    ORDER BY tc_inner1.displayId DESC
                 ) a
            GROUP BY hash 
        ) AS tc_result1

        join

        (
            SELECT *
            FROM (
                    SELECT id, displayId,activeFlag,hash
                    FROM Test\Entity\TestCase tc_inner2
                    where tc_inner2.activeFlag=0 and tc_inner2.product = :productId
                    ORDER BY tc_inner2.displayId DESC
                 ) a
            GROUP BY hash 
        ) AS tc_result2
        on
        tc_result1.id = tc_result2.id"
   );
$query->setParameter("productId", $productId);

I got following error:

Additional information: Doctrine\ORM\Query\QueryException File: /var/www/test-suite/vendor/doctrine/orm/lib/Doctrine/ORM/Query/QueryException.php:63

Message:

[Semantical Error] line 0, col 153 near '(': Error: Class '(' is not defined.

Answer 1

The answer I gave to a similar question yesterday on the doctrine-user mailinglist is also applicable here:

What you are doing wrong here is: defining an ON-clause. That is how you'd do it in SQL. In DQL however you define the relationship between your entities in the mapping-information (via annotations, xml, yaml or php) and in the query you use that relationship. So: define a OneToOne, OneToMany, ManyToOne or ManyToMany relationship and use that in the query (and consequently no need to use a ON).

And related (from the same thread):

you are not querying the tables with DQL, but the entities! The entities (objects) form a network, the object graph. You can "walk" that graph. The associations are not defined in the query (with ON or WHERE), but in the mapping. That is the whole point of an ORM; otherwise there is no need to use it and you could better just directly query the database with SQL.