KatsuoRyuu KatsuoRyuu - 1 month ago 11
PHP Question

Joining Entities on query in Doctrine 2

I am trying to join two entities in one advanced query in Doctrine 2. I am working with Zend Framework 2.

Unfortunately I have a hard time to find heads and tails in what I'm doing right now.

Here is what I have got in ZF2:


  • I have build a module that is called
    KryuuCategorizer
    , that I am going to use for categorize all kinds of contents on the site.

  • It is attached to a event called categorize, which will get a entity type



Categorizer has 2 Entities:

Category
var id
var name
var objects

Object
var id
var entityName
var module
var entityId
var category


Category->objects
is bound by ManyToOne to
Object->category


Then I need to bind the entity manually to the Object->entityId, I think I should be able to do this by using join in Doctrine 2.
But I am not sure where to start or how to get a grip on it.
I hope somebody can get me started and show me in the right direction, I looked at the Doctrine documentation, but got more confused than I should.

Answer

There are several ways to achieve what you want.

1. Add a fetch="EAGER to the relation definition

To do this add a fetch="EAGER" to the entity definition in your Object.

/** MANY-TO-ONE, OWNING SIDE
 * @var Category
 * @ORM\ManyToOne(targetEntity="Application\Entity\Category", inversedBy="objects", fetch="EAGER")
 * @ORM\JoinColumn(name="category_id", referencedColumnName="id", nullable=true)
 */
protected $category;

Eager loading is mentioned in the documentation here and here.


2. Write a custom DQL query in your Object repository and do a fetch join:

/**
 * Find object using DQL with a category joined.
 *
 * @param int $id
 * @return Building|null
 */
public function findObject($id)
{
    $entityManager = $this->getEntityManager();
    $query = $entityManager->createQuery(
        "SELECT o, c FROM Object o JOIN o.category c WHERE o.id = :id"
    );
    $query->setParameter('id', $id);
    $query->getOneOrNullResult();
}

These kind of joins are explained in the documentation here. Watch the difference between fetch-join and regular joins.


3. Or use Doctrine QueryBuilder in your Object repository to get the same result:

/**
 * Find object using query builder with a category joined.
 *
 * @param int $id
 * @return Building|null
 */
public function findObject($id)
{
    $queryBuilder = $this->createQueryBuilder('o')
        ->addSelect('c')
        ->leftJoin('o.category', 'c')
    }
    return = $queryBuilder->getQuery()->getOneOrNullResult();
}

Be aware that If you want to fetch-join you also need to add the addSelect condition to the query, otherwise it is a regular join.