Symfony2/Doctrine2 innerJoin using QueryBuilder

Question

I'm trying to build a innerJoin query using Doctrine2/QueryBuilder.

$repo =  $this->getDoctrine()
        ->getRepository('MyBundle:Models');
$query = $repo->createQueryBuilder('m')
        ->where('m.id = :id')
        ->setParameter('id', $id);

Doctrine says:

A join always belongs to one part of the from clause. This is why you have to specify the alias of the FROM part the join belongs to as the first argument.

As a second and third argument you can then specify the name and alias of the join-table and the fourth argument contains the ON clause.

Ex.

$queryBuilder
->innerJoin('u', 'phonenumbers', 'p', 'u.id = p.user_id');

What I can't understand is that 'phonenumbers' table is referencing to Entity Name or DB Table Name.

What I actually want is, is there any way to explicitly refer to entity like

innerJoin('u', 'MyBundle:phonenumbers', 'p', 'u.id = p.user_id')?

It's a bit confusing when it joins just like that. Can please someone explain that to me?

Help!!

Solution 1

You are working on a DQL level with tables, which means that you actually joining a table, so you just need the table name, not the entity name. The table "phonenumbers might not even had an entity to begin with, this is why Doctrine requests a table name and not an entity name.

Edit

It is actually possible to work with entity names as well as follows (taken from my own code which is working as a charm):

$builder = $this->createQueryBuilder('m');
$builder->innerJoin(
    'YourBundle:Category',
    'c',
    Join::WITH,
    $builder->expr()->eq('m.id', 'c.mdl_id')
);

To use the constants from Join you should first:

use Doctrine\ORM\Query\Expr\Join;

But this should also work (taken from documentation which means should work like a charm):

$queryBuilder
    ->select('id', 'name')
    ->from('users', 'u')
    ->innerJoin('u', 'phonenumbers', 'p', 'u.id = p.user_id');

This is taken form: http://doctrine-orm.readthedocs.org/projects/doctrine-dbal/en/latest/reference/query-builder.html#join-clauses

Solution 2

In fact, you're already referencing explicitely your entity phonenumbers in your second argument of your innerJoin function.

You read the doc and i'll retake it point by point with your request :

• Argument 1 : alias of the FROM part, here your m (alias of your models)
• Argument 2 : The full name of your entity you want join with your models
• Argument 3 : an alias to access it (just like the 'm' of models)
• Argument 4 : The join condition. (like the ON part of a sql request)

But with symfony and if you have a relation between your two entities like that :

//AppBundle/Entity/Models

    class Models {
        /**
         * ...
        **/
        private $id;

        /**
         * ...
         * @ORM\ManyToOne(targetEntity="\AppBundle\Entity\Phone", inversedBy="models")
         **/
        private $phonenumbers;

        ...
    }

you want to join you just can do :

$repo =  $this->getDoctrine()
        ->getRepository('MyBundle:Models');
$query = $repo->createQueryBuilder('m')
        ->innerJoin('m.phonenumbers', 'p')
        ->where('m.id = :id')
        ->setParameter('id', $id);

To explain that : You just have to pass as first argument, the entity property you want to join (here the phone number of your model) and define it as alias (p for phonenumber to access it in a select)