How to fetch remote table ids through an association table with Doctrine2 DQL

Question

I have the following model with 2 tables foo and bar and the foo_has_bar association in between:

I'm trying to get, for a given foo.id, all the corresponding bar.id's.

So far I have been able to retrieve all the entries from foo_has_bar related to a particular foo.id as follows:

$query = $em->createQuery("SELECT h FROM 'FooHasBar' h INNER JOIN h.foo f WHERE f.id = ?1");
$query->setParameter(1, $foo_id);
$results = $query->getArrayResult();

The problem is that $results only contains the id field for foo_has_bar, not the foreign keys foo_id and bar_id, and therefore I can't achieve what I want.

Is there a way, given a particular foo.id to retrieve all the corresponding bar.id's with a single DQL command?

Answer 1

Assuming the entity FooHasBar has a relation foo and a relation bar respectively to Foo and Bar, the query is quite trivial:

$q = $em->createQuery(
    'SELECT 
        b.id as bar_id, f.id as foo_id 
    FROM FooHasBar fb 
    JOIN fb.bar b 
    JOIN fb.foo f 
    WHERE f.id = :fooId'
);
$q->setParameter( 'fooId', $foo->getId() );

$results = $query->getArrayResult();