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'

If the mp2_firm entity has a relationship with the mp2_fos_user entity via the property firmUser, then try the WITH statement as WITH f.firmUser = u. Same with idea with firmShop — geoB
– geoB, Commented Jun 9, 2018 at 18:19
@geoB thank you for suggestion. Sadly problem persists. I updated question to reflect that. Although error changed slightly... — Rikijs
– Rikijs, Commented Jun 9, 2018 at 19:39

M Khalid Junaid · Accepted Answer · 2018-06-09 19:54:55Z

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

geoB · Accepted Answer · 2018-06-09 19:51:07Z

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

Collectives™ on Stack Overflow

Doctrine2 - double left join query issue

UPDATE

2 Answers 2

Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

UPDATE

2 Answers 2

Comments

1 Comment

Related