l3pp4rd/DoctrineExtensions — Issue #173: Translatable + QueryBuilder : SQLSTATE[42S22]: Column not found: 1054 Unknown column 'f10_.id' in 'on clause'
Author: oscarballadares
Created: 2011-10-17 21:07:00+00:00
State: open

Hi. I'm having an issue with a querybuilder query in this structure, where $query, $query2, $query3 are instances of QueryBuilder:
$query
->select('f')
->from('HotelBundle:Foto', 'f')
->where($query->expr()->notIn('f.id',
$query2
->select('fo.id')
->from('HotelBundle:Foto', 'fo')
->join('fo.habitaciones', 'hf')
->getDql()
)
)
->andWhere($query->expr()->notIn('f.id',
$query3
->select('fot.id')
->from('HotelBundle:Foto', 'fot')
->join('fot.testimonios', 't')
->getDql()
)
)

return $query->getQuery()->setHint(
\Doctrine\ORM\Query::HINT_CUSTOM_OUTPUT_WALKER,
'Gedmo\\Translatable\\Query\\TreeWalker\\TranslationWalker'
)->getResult();

I get the above result: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'f10_.id' in 'on clause' (The query output is at the bottom)

My entities are: Foto (photo) oneToMany habitaciones (bedrooms). Foto (photo) oneToMany Testimonio (testimonial)

If I ommit one 'where' clause (any of them), It will work. If I ommit one $query->exrpr() (say I put where('f.title = :title) instead) It will work. So I guess it is failing because there is two 'where' clauses and two 'expr()' in each clause. If this kind of 'rule' is not follow, then it will work.

I'm working on a PR; So I'll have the certain if this is a local issue, or a real issue.

The raw sql query is:

(The f10_ is not defindend anywhere )

SELECT f0_.id AS id0, COALESCE(t1_.content, t2_.content) AS titulo1, COALESCE(t3_.content, t4_.content) AS descripcion2, f0_.prioridad AS prioridad3, f0_.titulo AS titulo_fallback4, f0_.descripcion AS descripcion_fallback5 FROM foto f0_ LEFT JOIN ext_translations t1_ ON t1_.locale = 'es' AND t1_.object_class = 'Oscar\\HotelBundle\\Entity\\Foto' AND t1_.field = 'titulo' AND t1_.foreign_key = f0_.id LEFT JOIN ext_translations t2_ ON t2_.locale = 'en' AND t2_.object_class = 'Oscar\\HotelBundle\\Entity\\Foto' AND t2_.field = 'titulo' AND t2_.foreign_key = f0_.id LEFT JOIN ext_translations t3_ ON t3_.locale = 'es' AND t3_.object_class = 'Oscar\\HotelBundle\\Entity\\Foto' AND t3_.field = 'descripcion' AND t3_.foreign_key = f0_.id LEFT JOIN ext_translations t4_ ON t4_.locale = 'en' AND t4_.object_class = 'Oscar\\HotelBundle\\Entity\\Foto' AND t4_.field = 'descripcion' AND t4_.foreign_key = f0_.id WHERE f0_.id NOT IN (SELECT f5_.id FROM foto f5_ INNER JOIN habitacion_foto h20_ ON f5_.id = h20_.foto_id LEFT JOIN ext_translations t6_ ON t6_.locale = 'es' AND t6_.object_class = 'Oscar\\HotelBundle\\Entity\\Foto' AND t6_.field = 'titulo' AND t6_.foreign_key = f5_.id LEFT JOIN ext_translations t7_ ON t7_.locale = 'en' AND t7_.object_class = 'Oscar\\HotelBundle\\Entity\\Foto' AND t7_.field = 'titulo' AND t7_.foreign_key = f5_.id LEFT JOIN ext_translations t8_ ON t8_.locale = 'es' AND t8_.object_class = 'Oscar\\HotelBundle\\Entity\\Foto' AND t8_.field = 'descripcion' AND t8_.foreign_key = f5_.id LEFT JOIN ext_translations t9_ ON t9_.locale = 'en' AND t9_.object_class = 'Oscar\\HotelBundle\\Entity\\Foto' AND t9_.field = 'descripcion' AND t9_.foreign_key = f5_.id LEFT JOIN ext_translations t11_ ON t11_.locale = 'es' AND t11_.object_class = 'Oscar\\HotelBundle\\Entity\\Foto' AND t11_.field = 'titulo' AND t11_.foreign_key = f10_.id LEFT JOIN ext_translations t12_ ON t12_.locale = 'en' AND t12_.object_class = 'Oscar\\HotelBundle\\Entity\\Foto' AND t12_.field = 'titulo' AND t12_.foreign_key = f10_.id LEFT JOIN ext_translations t13_ ON t13_.locale = 'es' AND t13_.object_class = 'Oscar\\HotelBundle\\Entity\\Foto' AND t13_.field = 'descripcion' AND t13_.foreign_key = f10_.id LEFT JOIN ext_translations t14_ ON t14_.locale = 'en' AND t14_.object_class = 'Oscar\\HotelBundle\\Entity\\Foto' AND t14_.field = 'descripcion' AND t14_.foreign_key = f10_.id LEFT JOIN ext_translations t16_ ON t16_.locale = 'es' AND t16_.object_class = 'Oscar\\HotelBundle\\Entity\\Testimonio' AND t16_.field = 'titulo' AND t16_.foreign_key = t15_.id LEFT JOIN ext_translations t17_ ON t17_.locale = 'en' AND t17_.object_class = 'Oscar\\HotelBundle\\Entity\\Testimonio' AND t17_.field = 'titulo' AND t17_.foreign_key = t15_.id LEFT JOIN ext_translations t18_ ON t18_.locale = 'es' AND t18_.object_class = 'Oscar\\HotelBundle\\Entity\\Testimonio' AND t18_.field = 'comentario' AND t18_.foreign_key = t15_.id LEFT JOIN ext_translations t19_ ON t19_.locale = 'en' AND t19_.object_class = 'Oscar\\HotelBundle\\Entity\\Testimonio' AND t19_.field = 'comentario' AND t19_.foreign_key = t15_.id) AND f0_.id NOT IN (SELECT f10_.id FROM foto f10_ INNER JOIN testimonio t15_ ON f10_.id = t15_.foto_id