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

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?

-------------Problems Reply------------

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();

Your query...

$car->getOwner() // "go and fetch this car's owner"

... is in a foreach loop so it will certainly issue the query several times.

If you're writing custom DQL to deal with this, $car->getOwner() shouldn't feature in this at all. This is a function of the Car class. The custom DQL you would write would mimick the exact SQL query you point out and get your join done efficiently.

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)

Category:php Views:0 Time:2010-11-12

Related post

  • How to page Doctrine2 results 2010-08-16

    I cant find Doctrine_Pager in Doctrine2 and really need a way to page my query results. Is there a way to use some alternative pager (Pear, Zend)? Please post some example code as well if solution is available. Google didnt helped me, so hope folks w

  • What is the best MVC, Doctrine2, Datamapper practice? 2009-09-24

    I am looking into using Doctrine2 with my Zend Framework setup. I really like the datamapper pattern, mainly because it seperates my domain models with my database. My question is what is the best practice for using Doctrine and DQL with my controlle

  • Doctrine2: Best way to handle many-to-many with extra columns in reference table 2010-08-22

    I'm wondering what's the best, the cleanest and the most simply way to work with many-to-many relations in Doctrine2. Let's assume that we've got an album like Master of Puppets by Metallica with several tracks. But please note the fact that one trac

  • doctrine2 zend framework namespaceing controllers 2010-09-03

    I'm trying to integrate the doctrine2 sandbox with a default Zend Framework App. When I try to use namespacing in the controller I get an 'Invalid controller class ("IndexController")' error This Works: use Entities\User, Entities\Address; class Inde

  • Custom Collection in Doctrine2 2010-09-11

    Just starting to work with Doctrine2, and am wondering how/if I can use a custom collection class. Searches point me to this part of the documentation: Collection-valued persistent fields and properties must be defined in terms of the Doctrine\Common

  • Is Doctrine2 compatible with PHP 5.3 up? 2010-09-20

    It seems doctrine 2 is compatible only with PHP 5.3 and up, and is NOT compatible with php versions before 5.3. Can someone confirm if this is true? --------------Solutions------------- Doctrine 2 heavily uses PHP namespaces. So, yes, you won't have

  • doctrine2 dql, use setParameter with % wildcard when doing a like comparison 2010-09-20

    I want to use the parameter place holder - e.g. ?1 - with the % wild cards. that is, something like: "u.name LIKE %?1%" (though this throws an error). The docs have the following two examples: 1. // Example - $qb->expr()->like('u.firstname', $q

  • Can a Discriminator Column be part of the Primary Key in Doctrine2? 2010-09-26

    I'm using Single Table Inheritance in Doctrine2 to store OAuth credentials for multiple services. I'd like to use the service's id as the primary key; however, that's not unique across all services. I've setup the database to use the discriminator co

  • How to get an id without join in doctrine2? 2010-10-12

    I have entity like this: /** * * @Table(name="table") * @Entity */ class Table { /** * @Column(type="integer") * @Id * @GeneratedValue(strategy="IDENTITY") */ private $id; /** * @ManyToOne(targetEntity="Entities\Users") * @joinColumn(name="userId", r

  • Multiple discrimination levels while using Doctrine2 2010-10-14

    I'm using Doctrine2 to manage my model below: There's an abstract concept Content with a Composite pattern in Gallery, also an abstract concept Media from which Video and Image inherits. My choice was to add discriminators to Content and Media tables

  • generate annotated doctrine2 entites from db schema 2010-10-31

    Is it possible to generate Doctrine 2 entities, with the relevant docblock annotations, from an existing database schema? --------------Solutions------------- I had to made these changes for the above code to work.. <?php use Doctrine\ORM\Tools\En

  • Is there named scope's analog for Doctrine2? 2010-11-14

    Does Doctrine2 have a features similar to ActiveRecord's named scopes? --------------Solutions------------- There isn't one baked into D2, but it probably wouldn't be too much of a stretch to implement a system similar to Yii's using Doctrine 2's Que

  • Can Doctrine2 load calculated values? 2010-11-17

    Is there a way to load calculated values into an entity using Doctrine2? For example, if a question has votes (the votes table having a foreign key from the question table), using SQL you could select the question and calculate a vote total. Is somet

  • Doctrine2 + join conditions 2010-11-25

    First of all I want to say big thanks to Doctrine developers, guys you are rock! My problem is the following: I have three tables: person (id, name, isActive) email (id, address, isActive) personEmailRel(id, personId, emaiId, isActive) And I want to

  • What are the differences between Doctrine1 and Doctrine2? 2010-12-09

    I have only started discovering Doctrine and noticed the official documentation points to 1.2 as this seems to be the current stable version. I also noticed that a lot of people seem to mention Doctrine 2 is a "world of difference" compared to Doctri

  • Doctrine2 Best Practice, Should Entities use Services? 2010-12-16

    I asked a similar question a while back: Using the Data Mapper Pattern, Should the Entities (Domain Objects) know about the Mapper? However, it was generic and I'm really interested in how to accomplish a few things with Doctrine2 specifically. Here'

  • Does retrieving an object from Doctrine2 cause __construct() of the model class to run? 2010-12-24

    When I retrieve an object say by $em->find('Application\Models\User', 1); or other methods like DQL, findBy*() cause the __construct() of the model class to run? I am having a problem where I set variables there like reference to EntityManager and

  • doctrine2 and codeigniter2, right approach for parent class to interact with child properties 2011-01-06

    I'm searching for this for quite some time now. I saw few similar questions but not sure if they apply with doctrine2 and this problem. I'm trying to get started with CodeIgniter2 and Doctrine2, and I'm having some problems, I'm not OOP guru in php.

  • Zend & Doctrine2: Error with proxies from command line scripts 2011-01-11

    I'm working with Zend and Doctrine2 and I needed to create some cronjobs using the same structure. the problem is, when I tried to get the repository from a Model with relationship with another Model, I got an error (only in command-line, in the webs

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.166 (s). 11 q(s)