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!!

Daishi · Accepted Answer · 2018-06-11 20:57:00Z

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

edited Jun 11, 2018 at 20:57

Daishi

14.5k1 gold badge23 silver badges24 bronze badges

answered Nov 12, 2015 at 8:22

Thanasis Pap

2,0512 gold badges17 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mehulkumar Over a year ago

How would you write DQL for SELECT m.id, m.name, c.name FROM models AS m INNER JOIN categories AS c ON (m.id = c.mdl_id) WHERE c.id = ? ?

Thanasis Pap Over a year ago

I updated my answer as it was not entirely correct. From within your ModelRepository (strange name tbh) you run it.

Mehulkumar Over a year ago

Thanks. I've already solved it. What I actually was looking for is this: Joins between entities without associations were not possible until version 2.4, where you can generate an arbitrary join with the following syntax: SELECT u FROM User u JOIN Blacklist b WITH u.email = b.email which is also possible in queryBuilder. I had two entities without association and the thing was confusing me was how Doctrine determines which table to join. My bad.

Julien Bourdic · Accepted Answer · 2015-11-12 08:22:13Z

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)

Collectives™ on Stack Overflow

Symfony2/Doctrine2 innerJoin using QueryBuilder

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related