Doctrine DQL with multiple joined tables

Question

i have entities **target,citytarget,city,cityplace,place

citytarget & cityplace are pivot tables that connect t&c and c&p

i need the places for given city name and a target id

i tried the following DQL:

SELECT t,c,p FROM MulticatorBundle:Target t
            join t.cities ct
            join ct.cities c
            join c.places cp
            join cp.places p
            where c.name like '%Stahmeln%'

But i receive:

result: The parent object of entity result with alias 'c' was not found. The parent alias is 'ct'.

i dont know any further....

a plain SQL could be:

select * from target 
left join citytarget on citytarget.target_id = target.id
left join city on citytarget.city_id = city.id 
left join cityplace on cityplace.city_id = city.id 
left join place on cityplace.id = place.id 
where target.id = 1 
and city.name like \'%Stahmeln%\'

Adrian

Jentoft

Answer 1

You need to do

SELECT t,ct, c, cp ,p FROM MulticatorBundle:Target t
            join t.cities ct
            join ct.cities c
            join c.places cp
            join cp.places p
            where c.name like '%Stahmeln%'

Hope this help you

Rollback

hi abdiel. tx for you answer.,i receive the notice 'undefined index city' now ;/ – Jentoft

fixed it. it was a problem with plural and singular in entity variables – Jentoft

Answer 2

You always need your parent entity in your select. In this case:

SELECT ct, c, cp, t, p

is needed

Empedocles

Thank you! This was giving me a headache. – Estaminet