DDC-1067: Doctrine 2 QuerryBuilder vs Handcoded DQL - different results #1335

New Issue

Closed

opened 2026-01-22 13:10:48 +01:00 by admin · 6 comments

admin commented 2026-01-22 13:10:48 +01:00

Owner

Copy Link

Originally created by @doctrinebot on GitHub (Mar 11, 2011).

Originally assigned to: @guilhermeblanco on GitHub.

Jira issue originally created by user gdobrowolski:

As per Guilherme Blanco request I'm submitting this weird behaviour I have experienced:

(Original post can be found here: http://stackoverflow.com/questions/5261891/doctrine-2-querrybuilder-vs-handcoded-dql-different-results

My handcrafted query looks like this:

select count(fi.id) 
from Entities\Content\FolderLookup fl 
join fl.site fls 
join fl.folder flf,
Entities\Content\FolderItem fi 
join fi.site fis 
join fi.folder fif 
join fi.item it 
join it.type tp 
join it.content ic 
where fl.namePath = ?1 
and tp.name = ?2 
and fls.id = fis.id 
and flf.id = fif.id

Now, I'm trying to reproduce it like this with QueryBuilder:

$qb->select("count(fi.id)")->from("Entities\Content\FolderLookup", "fl")->join("fl.site","fls")->join("fl.folder", "flf");

$qb->from("Entities\Content\FolderItem","fi")->join("fi.site","fis")->join("fi.folder","fif");
$qb->join("fi.item","it")->join("it.type","tp")->join("it.content","ic");

$wherePart = $qb->expr()->andx();
$wherePart->add($qb->expr()->eq("fl.namePath","?1"));
$wherePart->add($qb->expr()->eq("tp.name","?2"));
$wherePart->add($qb->expr()->eq("fls.id","fis.id"));
$wherePart->add($qb->expr()->eq("flf.id","fif.id"));

$qb->where($wherePart);

This however is producing this DQL query:

SELECT count(fi.id) FROM Entities\Content\FolderLookup fl, 
Entities\Content\FolderItem fi 
INNER JOIN fl.site fls 
INNER JOIN fl.folder flf 
INNER JOIN fi.site fis 
INNER JOIN fi.folder fif 
INNER JOIN fi.item it 
INNER JOIN it.type tp 
INNER JOIN it.content ic 
WHERE (fl.namePath = ?1) 
AND (tp.name = ?2) 
AND (fls.id = fis.id) 
AND (flf.id = fif.id)

As you can see there is part of this missing comapring to handcrafted one (First line):

fl join fl.site fls join fl.folder flf

I'm not sure why these joins are missing as I am defining them here:

$qb->select("count(fi.id)")->from("Entities\Content\FolderLookup", "fl")->join("fl.site","fls")->join("fl.folder", "flf");

Update:

The fun part starts, when DQL gets translated into SQL - in this case MySQL:

Handcrafted one becomes:

SELECT count(f0*.id) AS sclr0 FROM FolderLookup f1_ INNER JOIN Site s2_ ON f1_.site_id = s2_.id INNER JOIN Folder f3_ ON f1_.folder_id = f3_.id, FolderItem f0_ INNER JOIN Site s4_ ON f0_.site_id = s4_.id INNER JOIN Folder f5_ ON f0_.folder_id = f5_.id INNER JOIN Item i6_ ON f0_.item_id = i6_.id INNER JOIN ItemType i7_ ON i6_.type_id = i7_.id INNER JOIN ItemContent i8_ ON i6_.content_id = i8_.id WHERE f1_.namePath = ? AND i7_.name = ? AND s2_.id = s4_.id AND f3_.id = f5*.id

Where generated one looks like this:

SELECT count(f0*.id) AS sclr0 FROM FolderLookup f1_, FolderItem f0_ INNER JOIN Site s2_ ON f1_.site_id = s2_.id INNER JOIN Folder f3_ ON f1_.folder_id = f3_.id INNER JOIN Site s4_ ON f0_.site_id = s4_.id INNER JOIN Folder f5_ ON f0_.folder_id = f5_.id INNER JOIN Item i6_ ON f0_.item_id = i6_.id INNER JOIN ItemType i7_ ON i6_.type_id = i7_.id INNER JOIN ItemContent i8_ ON i6_.content_id = i8_.id WHERE (f1_.namePath = ?) AND (i7_.name = ?) AND (s2_.id = s4_.id) AND (f3_.id = f5*.id)

And this is invalid statement, as database returns with:

Column not found: 1054 Unknown column 'f1_.site_id' in 'on clause'

Originally created by @doctrinebot on GitHub (Mar 11, 2011). Originally assigned to: @guilhermeblanco on GitHub. Jira issue originally created by user gdobrowolski: As per Guilherme Blanco request I'm submitting this weird behaviour I have experienced: (Original post can be found here: http://stackoverflow.com/questions/5261891/doctrine-2-querrybuilder-vs-handcoded-dql-different-results My handcrafted query looks like this: ``` select count(fi.id) from Entities\Content\FolderLookup fl join fl.site fls join fl.folder flf, Entities\Content\FolderItem fi join fi.site fis join fi.folder fif join fi.item it join it.type tp join it.content ic where fl.namePath = ?1 and tp.name = ?2 and fls.id = fis.id and flf.id = fif.id ``` Now, I'm trying to reproduce it like this with QueryBuilder: ``` $qb->select("count(fi.id)")->from("Entities\Content\FolderLookup", "fl")->join("fl.site","fls")->join("fl.folder", "flf"); $qb->from("Entities\Content\FolderItem","fi")->join("fi.site","fis")->join("fi.folder","fif"); $qb->join("fi.item","it")->join("it.type","tp")->join("it.content","ic"); $wherePart = $qb->expr()->andx(); $wherePart->add($qb->expr()->eq("fl.namePath","?1")); $wherePart->add($qb->expr()->eq("tp.name","?2")); $wherePart->add($qb->expr()->eq("fls.id","fis.id")); $wherePart->add($qb->expr()->eq("flf.id","fif.id")); $qb->where($wherePart); ``` This however is producing this DQL query: ``` SELECT count(fi.id) FROM Entities\Content\FolderLookup fl, Entities\Content\FolderItem fi INNER JOIN fl.site fls INNER JOIN fl.folder flf INNER JOIN fi.site fis INNER JOIN fi.folder fif INNER JOIN fi.item it INNER JOIN it.type tp INNER JOIN it.content ic WHERE (fl.namePath = ?1) AND (tp.name = ?2) AND (fls.id = fis.id) AND (flf.id = fif.id) ``` As you can see there is part of this missing comapring to handcrafted one (First line): ``` fl join fl.site fls join fl.folder flf ``` I'm not sure why these joins are missing as I am defining them here: ``` $qb->select("count(fi.id)")->from("Entities\Content\FolderLookup", "fl")->join("fl.site","fls")->join("fl.folder", "flf"); ``` Update: The fun part starts, when DQL gets translated into SQL - in this case MySQL: Handcrafted one becomes: ``` SELECT count(f0*.id) AS sclr0 FROM FolderLookup f1_ INNER JOIN Site s2_ ON f1_.site_id = s2_.id INNER JOIN Folder f3_ ON f1_.folder_id = f3_.id, FolderItem f0_ INNER JOIN Site s4_ ON f0_.site_id = s4_.id INNER JOIN Folder f5_ ON f0_.folder_id = f5_.id INNER JOIN Item i6_ ON f0_.item_id = i6_.id INNER JOIN ItemType i7_ ON i6_.type_id = i7_.id INNER JOIN ItemContent i8_ ON i6_.content_id = i8_.id WHERE f1_.namePath = ? AND i7_.name = ? AND s2_.id = s4_.id AND f3_.id = f5*.id ``` Where generated one looks like this: ``` SELECT count(f0*.id) AS sclr0 FROM FolderLookup f1_, FolderItem f0_ INNER JOIN Site s2_ ON f1_.site_id = s2_.id INNER JOIN Folder f3_ ON f1_.folder_id = f3_.id INNER JOIN Site s4_ ON f0_.site_id = s4_.id INNER JOIN Folder f5_ ON f0_.folder_id = f5_.id INNER JOIN Item i6_ ON f0_.item_id = i6_.id INNER JOIN ItemType i7_ ON i6_.type_id = i7_.id INNER JOIN ItemContent i8_ ON i6_.content_id = i8_.id WHERE (f1_.namePath = ?) AND (i7_.name = ?) AND (s2_.id = s4_.id) AND (f3_.id = f5*.id) ``` And this is invalid statement, as database returns with: Column not found: 1054 Unknown column 'f1_.site_id' in 'on clause'

admin added the Bug label 2026-01-22 13:10:48 +01:00

admin closed this issue 2026-01-22 13:10:50 +01:00

admin commented 2026-01-22 13:10:50 +01:00

Author

Owner

Copy Link

@doctrinebot commented on GitHub (Mar 12, 2011):

Comment created by @beberlei:

This are different DQLs you are generitng with the query builder. You are calling $qb->from() twice, but you only have one FROM clause in your original dql!

Edit: Oh no you have two from classes, this really looks weird.

Edit 2: The SQL Error is to be expected because of the resorting of the DQL parts.

@doctrinebot commented on GitHub (Mar 12, 2011): Comment created by @beberlei: This are different DQLs you are generitng with the query builder. You are calling $qb->from() twice, but you only have one FROM clause in your original dql! Edit: Oh no you have two from classes, this really looks weird. Edit 2: The SQL Error is to be expected because of the resorting of the DQL parts.

admin commented 2026-01-22 13:10:52 +01:00

Author

Owner

Copy Link

@doctrinebot commented on GitHub (Mar 12, 2011):

Comment created by @beberlei:

Fixed formatting

@doctrinebot commented on GitHub (Mar 12, 2011): Comment created by @beberlei: Fixed formatting

admin commented 2026-01-22 13:10:52 +01:00

Author

Owner

Copy Link

@doctrinebot commented on GitHub (Mar 20, 2011):

Comment created by @beberlei:

The issue here is that the QueryBuilder does preserve the order of from and join calls.

@doctrinebot commented on GitHub (Mar 20, 2011): Comment created by @beberlei: The issue here is that the QueryBuilder does preserve the order of from and join calls.

admin commented 2026-01-22 13:10:52 +01:00

Author

Owner

Copy Link

@doctrinebot commented on GitHub (May 12, 2011):

Comment created by @guilhermeblanco:

Fixed on master

@doctrinebot commented on GitHub (May 12, 2011): Comment created by @guilhermeblanco: Fixed on master

admin commented 2026-01-22 13:10:53 +01:00

Author

Owner

Copy Link

@doctrinebot commented on GitHub (May 12, 2011):

Issue was closed with resolution "Fixed"

@doctrinebot commented on GitHub (May 12, 2011): Issue was closed with resolution "Fixed"

admin commented 2026-01-22 13:10:54 +01:00

Author

Owner

Copy Link

@doctrinebot commented on GitHub (May 20, 2011):

Comment created by gdobrowolski:

I have just tried with ORM and DBAL versions 2.0.5 and Common version 2.0.2 (2.0.5 doesn't seem to be available) and the issue is still there.

Has this been released in 2.0.5 or is it going to land in 2.1?

Thanks,
Greg

@doctrinebot commented on GitHub (May 20, 2011): Comment created by gdobrowolski: I have just tried with ORM and DBAL versions 2.0.5 and Common version 2.0.2 (2.0.5 doesn't seem to be available) and the issue is still there. Has this been released in 2.0.5 or is it going to land in 2.1? Thanks, Greg

admin referenced this issue 2026-01-22 16:04:11 +01:00

[PR #1335] Fix AbstractQuery::getParameter() documented return type #9393

Sign in to join this conversation.

No Branch/Tag Specified

Branches Tags

3.6.x

2.20.x

dependabot/github_actions/2.20.x/doctrine/dot-github/dot-github/workflows/composer-lint.yml-14.0.0

3.7.x

4.0.x

2.21.x

old-prototype-3.x

3.6.2

3.6.1

3.6.0

2.20.9

3.5.8

3.5.7

3.5.6

3.5.5

2.20.8

3.5.4

3.5.3

2.20.7

3.5.2

2.20.6

3.5.1

3.5.0

3.4.4

3.4.3

3.4.2

2.20.5

3.4.1

3.4.0

3.3.4

2.20.4

3.3.3

2.20.3

3.3.2

2.20.2

3.3.1

2.20.1

3.3.0

3.2.3

2.20.0

2.19.8

3.2.2

2.19.7

3.2.1

2.19.6

3.2.0

3.1.4

3.1.3

2.19.5

3.1.2

2.19.4

3.1.1

2.19.3

2.19.2

2.19.1

3.1.0

2.19.0

3.0.3

2.18.3

3.0.2

2.18.2

3.0.1

2.18.1

3.0.0

2.18.0

2.17.5

3.0.0-RC1

2.17.4

2.17.3

2.17.2

2.17.1

3.0.0-beta2

2.17.0

2.16.3

3.0.0-beta1

2.16.2

2.16.1

2.16.0

2.15.5

2.15.4

2.15.3

2.15.2

2.15.1

2.15.0

2.14.3

2.14.2

2.14.1

2.14.0

2.13.5

2.13.4

2.13.3

2.13.2

2.13.1

2.13.0

2.12.4

2.12.3

2.12.2

2.12.1

2.12.0

2.11.3

2.11.2

2.11.1

2.11.0

2.10.5

2.10.4

2.10.3

2.10.2

2.10.1

2.10.0

2.9.6

2.9.5

2.9.4

2.9.3

2.9.2

2.9.1

2.9.0

2.8.5

2.8.4

2.8.3

2.8.2

2.8.1

2.8.0

2.7.5

2.7.4

v2.7.3

v2.7.2

v2.7.1

v2.7.0

v2.6.6

v2.6.5

v2.6.4

v2.6.3

v2.6.2

v2.6.1

v2.6.0

v2.5.14

v2.5.13

v2.5.12

v2.5.11

v2.5.10

v2.5.9

v2.5.8

v2.5.7

v2.5.6

v2.5.5

v2.5.4

v2.5.3

v2.5.2

v2.4.8

v2.5.1

v2.5.0

v2.5.0-RC2

v2.5.0-RC1

v2.5.0-beta1

v2.5.0-alpha2

v2.5.0-alpha1

v2.4.7

v2.4.6

v2.4.5

v2.4.4

v2.4.3

v2.3.6

v2.4.2

2.3.5

v2.4.1

v2.4.0

2.4.0-RC2

2.4.0-RC1

2.4.0-BETA2

2.3.4

2.3.3

2.4.0-BETA1

2.3.2

2.3.1

2.3.0

2.3.0-RC4

2.3.0-RC3

2.3.0-RC2

2.2.3

2.3.0-RC1

2.3.0-BETA1

2.1.7

2.2.2

2.2.1

2.1.6

2.2.0

2.2.0-RC1

2.2.0-BETA2

2.2.0-BETA1

2.1.5

2.1.4

2.1.3

2.1.2

2.1.1

2.0.7

2.1.0

2.1.0RC3

2.1.0RC2

2.1.0RC1

2.0.6

2.1.0BETA1

2.0.5

2.0.4

2.0.3

2.0.2

2.0.1

2.0.0

2.0.0RC2

2.0.0RC1

2.0.0-BETA4

2.0.0-BETA3

2.0.0-BETA2

2.0.0-BETA1

Labels

Clear labels

BC Break

Bug

Can't Fix

Deprecation

Documentation

DQL

Duplicate

Failing Test

Hacktoberfest

Improvement

Improvement

Incomplete

Invalid

Missing Tests

New Feature

PR Created

pull-request

Mirrored from GitHub Pull Request

Question

Regression

Status: Waiting feedback

Waiting feedback

WIP

Won't Fix

No Label Bug

Milestone

No items

No Milestone

Projects

Clear projects

No project

Assignees

Clear assignees

admin

No Assignees

1 Participants

Notifications

Subscribe

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: doctrine/archived-orm#1335