how to implement join to more than one table on same column in doctrine

Question

My scenario is like this.

item_master table

item_id (primary key) | item_name
--------------------------------------
      1               |     Keyboard
--------------------------------------
      2               |     Mouse
--------------------------------------

user_lp_requirement table

id1 |item_id | otherColumns1
-----------------------
1   |  1     | 1
-----------------------
1   |  2     | 3
-----------------------
item_id is foreign key reference to item_master

training_program table

id2 |item_id | otherColumns2
-----------------------
1   |  1     | 1
-----------------------
1   |  2     | 4
-----------------------
item_id is foreign key reference to item_master

edit: In entity I have user_lp_requirement.item_id mapped to item_master.item_id. So if i mention item_id in query builder join, it automatically joins user_lp_requirement to item_master

Now the problem is how to join user_lp_requirement to training_program on "item_id" using doctrine queryBuilder.

FYI->the query will be written in Application\Entity\Repository\UserLpRequirementRepository.

Answer 1

$queryBuilder
    ->select('u', 'i.item_name', 't.otherColumns2')
    ->from('user_lp_requirement', 'u')
    ->innerJoin('u', 'item_master', 'i', 'u.item_id = i.item_id')
    ->innerJoin('u', 'training_program', 't', 'u.item_id = t.item_id');

This assumes that you want the user table mapped to the item table AND the user table mapped to the training table rather than the user table mapped to a join of the training table and the item table.