I have two entities related with a many to many relationship:

/** @Entity */
class Foo {

    /** @Column(type="integer") */
    public int $id;

    /** @ManyToOne(targetEntity="Foo") */    ​
   ​public int $parentId;

   ​/** @ManyToMany(targetEntity="Bar", inversedBy="foos") */
   ​public Collection $bars;

   ​public function __construct() {
      ​$this->bars = new ArrayCollection();
   ​}
}

/** @Entity */
class Bar {

   ​/** @Column(type="integer") */
   ​public int $id;

   ​/** @ManyToMany(targetEntity="Foo", mappedBy="bars") */
   ​public Collection $foos;

   ​public function __construct() {
      ​$this->foos = new ArrayCollection();
   ​}
}

This ends up generating the tables bars, foos, and foos_bars.

Normally, the relationship is easy enough, with FKs on foos_bars.foo_id and foo_bars.bar_id.

But for a special query, I need to get all the Foos that are match either where a Bar is joined to either foo.id or foo.parent_id

On SQL it would be something like this:

SELECT DISTINCT foos.*
FROM foos
   INNER JOIN foos_bars
   ON (foos_bars.foo_id = foo.id or foos_bars.foo_id = foo.parent_id)

... which would get all the Foos that I want.

But would prefer to be able to use the query builder because this query has some optional parts, and going through the QueryBuiler makes constructing the final query much simpler than wrangling with a SQL string.

Is it possible to accomplish that kind of query with the ORM Query Builder?

I get to start with somthing like:

$qb = $this->createQueryBuilder('f');
$qb->select('f')->distinct();
$qb->innerJoin('f.bars', 'b');

But this logically simply joins with the declared FK...

CodePudding user response：

Since you cannot modify the mapping to "expose" the intermediate table as an independent entity, I don't think you'll be able to do this with the ORMs Query Builder.

But, you can always do it with DBAL query builder. You'd keep the convenience to use the query builder, you'd be able to perform the custom join that you need. The only drawback is that you'll need to perform your the object hydration on your own, but that should be minor price to pay to balance the other things.

$qb = $this->getEntityManager()
    ->getConnection()
    ->createQueryBuilder();


$qb->from('foos', 'f');
$qb->select('f.*')
    ->distinct();
    ->innerJoin('f', 'foos_bars', 'fb', 'fb.foo_id = f.id or fb.foo.id = f.parent_id');

$queryResult = $qb->executeQuery();

$repositoryResult = [];

foreach ($queryResult->fetchAllAssociative() as $row) {

    $foo = new Foo();
    // HERE YOU WOULD HYDRATE $foo WITH THE CONTENTS OF $row

    $repositoryResult[] = $foo; 

}

return $repositoryResult;

The ORM is very handy and convenient a lot of the time, but it's usually good to learn when it's useful to leave its confines and do things closer to the persistence layer.