Doctrine2 - double left join query issue

Question

I am using Symfony v3.4 branch with Doctrine.

I am having trouble translating SQL query to Doctrine ORM query.

I have 3 tables.

• Shop
• Firm
• User

User --> 1:1 --> Firm --> 1:1 --> Shop

(Symfony developer tool bar reports that associations in tables are made correctly).

I want to get Shop data that corresponds to cretain User in one query.

My SQL, that get a result:

SELECT *
FROM mp2_fos_user as u
LEFT JOIN mp2_firm AS f ON u.id = f.firmUserId
LEFT JOIN mp2_shop AS s ON f.id = s.shopFirmId
WHERE u.id = 1

My Doctrine ORM query

$query = $em->createQueryBuilder()
->select('u, f, s')
->from('App:User', 'u')
->leftJoin('u.userFirm WITH u.id = f.firmUserId', 'f')
->leftJoin('f.firmShop WITH f.id = s.shopFirmId', 's')
->where('u.id = :user_id')
->setParameter('user_id', $user_id)
->getQuery();

at the moment running the code results in an error

[Syntax Error] line 0, col 57: Error: Expected end of string, got 'u'

What would be best practice for my issue?

Help would be much appreciated, Thank you!

UPDATE

tried:

$query = $em->createQueryBuilder()
->select('s.id')
->from('App:User', 'u')
->leftJoin('u.userFirm WITH f.firmUser = u', 'f')
->leftJoin('f.firmShop WITH s.shopFirm = f', 's')
->where('u.id = :user_id')
->setParameter('user_id', $user_id)
->getQuery();

got [Syntax Error] line 0, col 54: Error: Expected end of string, got 'f'

Answer 1

There is no need to use WITH clause if you have defined mapping in your entities, WITH clause is used when you want to join your entities with additional matching criteria

class User
{
    /**
     * @ORM\YourRelationShipNature(targetEntity="App\Entity\Firm", mappedBy="user")
     */
    private $userFirm;
}

class Firm
{
    /**
     * @ORM\YourRelationShipNature(targetEntity="App\Entity\Shop", mappedBy="firm")
     */
    private $firmShop;
}


class Shop
{
    //.....
}

And then your could simple use properties to join your entites

$query = $em->createQueryBuilder()
    ->select('u, f, s')
    ->from('App:User', 'u')
    ->leftJoin('u.userFirm', 'f')
    ->leftJoin('f.firmShop', 's')
    ->where('u.id = :user_id')
    ->setParameter('user_id', $user_id)
    ->getQuery();

Answer 2

I was thinking of something more along the lines of this (assuming the entity names are correct):

$query = $em->createQueryBuilder()
    ->select('u, f, s')
    ->from('App:User', 'u')
    ->leftJoin('App:UserFirm f WITH f.firmUser = u')
    ->leftJoin('App:FirmShop s WITH s.shopFirm = f')
    ->where('u.id = :user_id')
    ->setParameter('user_id', $user_id)
    ->getQuery();