Symfony2 Form > Entity Field Type > query builder > subselect possible?

Question

I have a form with an entity field type.
Inside this entity field type I have a query builder, because I want to select only some of the records.
However I want to do a subselect inside this query builder, and I'm not sure this is the right way to go about it:

'query_builder' => function(EntityRepositorty $er) {
    $subq = $er->createQueryBuilder()        
    //cannot select other entity here?

    return $er->createQueryBuilder('a')
    ->leftJoin($subq)
    ->where('blah blah')
}

Has anyone come across this issue and what was their solution?

The left join looks similar to:

LEFT JOIN (select * from `table_c` order by date desc) as c on c.status_id = a.id

Answer 1

Your description suggests table c is mapped onto table a as a one-to-many relation. You should just use the normal doctrine joins:

'query_builder' => function(EntityRepository $er) {
    return $er->createQueryBuilder('c')
    ->join('c.a', 'a');
}

You need to use a normal join because left join will give you everything in table c, regardless of whether or not it is related to a.