Symfony 2: INNER JOIN on non related table with doctrine query builder

Question

I'm trying to build a query with the doctrine query builder which joins a non related table like this:

$query = $this->createQueryBuilder('gpr')         ->select('gpr, p')         ->innerJoin('TPost', 'p')         ->where('gpr.contentId = p.contentId') 

But this doesn't work. I still get an error:

Error: Identification Variable TPost used in join path expression but was not defined before.

I searched for this error message and everybody answered to use the table alias + attribute like p.someAttribute. But the table I want to join isn't related in the table I start my select from.

As a normal mysql query i would write it like this:

SELECT * FROM t_group_publication_rel gpr  INNER JOIN t_post p  WHERE gpr.content_id = p.content_id 

Any ideas what i'm doing wrong?

Answer 1

Today I was working on similar task and remembered that I opened this issue. I don't know since which doctrine version it's working but right now you can easily join the child classes in inheritance mapping. So a query like this is working without any problem:

$query = $this->createQueryBuilder('c')         ->select('c')         ->leftJoin('MyBundleName:ChildOne', 'co', 'WITH', 'co.id = c.id')         ->leftJoin('MyBundleName:ChildTwo', 'ct', 'WITH', 'ct.id = c.id')         ->orderBy('c.createdAt', 'DESC')         ->where('co.group = :group OR ct.group = :group')         ->setParameter('group', $group)         ->setMaxResults(20); 

I start the query in my parent class which is using inheritance mapping. In my previous post it was a different starting point but the same issue if I remember right.

Because it was a big problem when I started this issue I think it could be also interesting for other people which don't know about it.

Answer 2

Joins between entities without associations were not possible until version 2.4, where you can generate an arbitrary join with the following syntax:

$query = $em->createQuery('SELECT u FROM User u JOIN Blacklist b WITH u.email = b.email'); 

Reference: http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/dql-doctrine-query-language.html