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

New Issue

admin · 2026-01-22T13:10:46+01:00

admin commented

2026-01-22 13:10:46 +01:00

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:46 +01:00

admin referenced this issue

2026-01-22 16:04:11 +01:00

[PR #1333] Allow DateTimeImmutable as parameter value #9395

admin referenced this issue

2026-01-22 16:09:07 +01:00

[PR #8328] Infer `datetime_immutable` DBAL type for `\DateTimeImmutable` instance parameters #10923

Sign in to join this conversation.