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

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