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.
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.
I got the problem. Doctrine queries to Entity class rather than the table. It expects an entity class after
select tc_result1.id as id ,tc_result1.displayId as displayId,tc_result1.activeFlag,tc_result1.hash
from
(
A solution to this problem is using ResultSetMappingBuilder object , using which we can write the query in native sql and map the result to doctrine object