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?

We can use DQL to perform a join with unrelated Objects? I do not know. If possible, it0s interesting =). — sensorario, Jun 20 '12 at 09:41
Why don't you just *build* a relation between these two, if you want to join them? — dan-lee, Jun 20 '12 at 09:45
In this case a relation wouldn't be enough. I would need relations to 3 different tables and any record could only set a reference to 1 of these 3. — 0s1r1s, Jun 20 '12 at 10:06
Is it slower if I use the find methods in my controller instead of building a query? I would say yes because there are much more queries in the background right? — 0s1r1s, Jun 20 '12 at 10:08
So I mean not only 1 find method. The combination of 2 different finds in a loop. — 0s1r1s, Jun 20 '12 at 10:08

score 98 · Accepted Answer · answered May 28 '13 at 11:06

98

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.

answered May 28 '13 at 11:06

0s1r1s

1,723
1
13
15

Thank you ! This really helped, I've been searching for hours. – Assil Mar 27 '14 at 22:47
if i dont have `MyBundleName:ChildTwo` repository, i want to write query with table name then what is the solution.? – Snopzer Jul 11 '16 at 12:22
1

@MohammadFareed The reference to the entity is not a repository, it's either a string in ':' form OR the FQCN of the entity class, e.g. \AppBundle\Entity\ChildTwo::class – Levente Pánczél Oct 08 '16 at 17:40
What's the difference between using WITH or ON? – Mollo Aug 27 '21 at 02:26

score 12 · Answer 2 · edited Sep 05 '16 at 01:04

12

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

edited Sep 05 '16 at 01:04

techdreams

5,371
7
42
63

answered Sep 04 '16 at 23:09

Yamir

151
1
3

1

This is awesome! Note that the `WITH` clause is required in this case, otherwise it results in a syntax error. However you can just use a dummy condition and essentially obtain a real cross join, which DQL doesn't otherwise support. For example: `SELECT u FROM User u JOIN Items i WITH 0 = 0`. This can be useful for complex statistics. – jlh Jan 25 '18 at 09:03

score 3 · Answer 3 · edited Jun 13 '14 at 18:06

3

$dql = "SELECT 
    a, md.fisrtName , md.LastName, mj
    FROM MembersBundle:Memberdata md
        INNER JOIN MembersBundle:Address a WITH md = a.empID
        INNER JOIN MembersBundle:Memberjob mj WITH md = mj.memberData
            ...
    WHERE
        a.dateOfChange IS NULL
    AND WHERE
        md.someField = 'SomeValue'";

return $em->createQuery( $dql )->getResult();

edited Jun 13 '14 at 18:06

Gordon

312,688
75
539
559

answered Oct 20 '13 at 14:56

Jzapata

2,442
1
12
9

1

While this answer doesn't provide required details, there's nothing to cite as Doctrine's documentation completely lacks coverage of these types of joins. This worked for me. I know enough sql to parse what was intended and not enough doctrine to connect the dots. – eggmatters Jul 27 '16 at 16:31

score 1 · Answer 4 · answered Jun 27 '12 at 19:41

1

A DQL join only works if an association is defined in your mapping. In your case, I'd say it's much easier to do a native query and use ResultSetMapping to populate your objects.

answered Jun 27 '12 at 19:41

Peter Kruithof

10,584
6
29
42

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

4 Answers4

Linked