Doctrine2 many-to-one association won't use JOIN query

Question

I have a Car entity with a many-to-one relationship with an entity Owner. If I select all cars, Doctrine does one query on the Car table, and subsequently one query on the Owner table for each car. So fetching N cars becomes N+1 queries instead of a single JOIN query between the Car and Owner tables.

My entities are as follows:

/** @Entity */
class Car {

  /** @Id @Column(type="smallint") */
  private $id;

  /** @ManyToOne(targetEntity="Owner", fetch="EAGER")
      @JoinColumn(name="owner", referencedColumnName="id") */
  private $owner;

  public function getId()    { return $this->id; }
  public function getOwner() { return $this->owner; }
}

/** @Entity */
class Owner {

  /** @Id @Column(type="smallint") */
  private $id;

  /** @Column(type="string") */
  private $name;

  public function getName() { return $this->name; }
}

If I want to list the cars with their owners, I do:

$repo = $em->getRepository('Car');
$cars = $repo->findAll();

foreach($cars as $car) 
  echo 'Car no. ' . $car->getId() . 
       ' owned by ' . $car->getOwner()->getName() . '\n';

Now this all works very well, apart from the fact that Doctrine issues a query for each car.

SELECT * FROM Car;
SELECT * FROM Owner WHERE id = 1;
SELECT * FROM Owner WHERE id = 2;
SELECT * FROM Owner WHERE id = 3;
....

Of course I'd want my query log to look like this:

SELECT * FROM Car JOIN Owner ON Car.owner = Owner.id;

Whether I have fetch="EAGER" or fetch="LAZY" doesn't matter, and even if I make a custom DQL query with JOIN between the two entities, $car->getOwner() still causes Doctrine to query the database (unless I use EAGER, in which case $repo->findAll() causes all of them).

Am I just too tired here, and this is the way it is supposed to work - or is there a clever way to force Doctrine to do the JOIN query instead?

Answer 1

At least in 1.x Doctrine if you wanted to query for the related objects, you had to use DQL. For your case, the DQL query would look something like this:

//Assuming $em is EntityManager
$query = $em->createQuery('SELECT c, o FROM Car c JOIN c.owner o');
$cars = $query->execute();

Answer 2

Run first a DQL query where you select all the cars joined (DQL JOIN) with the owner. Put the owner in the select().

// preload cars
$qb = $em->createQueryBuilder()
        ->select('car, owner')
        ->from('\Entity\Car', 'car')
        ->leftJoin('c.owner',  'owner');

    $query = $qb->getQuery();

    // the following seems not needed, but I think it depends on the conf
    $query->setFetchMode("\Entity\Car", "owner", "EAGER");

    $query->execute(); //you don't have to use this result here, Doctrine will keep it

Doctrine 2 will then perform a JOIN (normally faster as it requires less db queries depending on the number of records). Now launch your foreach, Doctrine will find the entities internally and it won't run single queries when you need the owner.

Monitor the number of queries first/after each change (eg. mysql general log)