doctrine join manytomany twice does not work

Question

I need to load some permission data from a legacy application, which has entities user, group, permission joined with 2 ManyToMany associations

However, when I either create a query with

• User, then join Group then Permission or
• Permission, then join group then User

I get SQL errors, showing Doctrine using the wrong table to join the 2nd relation.

When I start from User

$qb = $this->getEm()->getRepository('AppBundle:User')
    ->createQueryBuilder('u')
    ->addSelect('p.file')
    ->innerJoin('u.groups', 'g')
    ->innerJoin('g.permissions', 'p')
    ->andWhere('u.user_id = :user_id')
    ->setParameter('user_id', $loginRow->getUserId());

the created query is:

SELECT u0_.user_id AS user_id_0, u0_.real_name AS real_name_1,
u0_.email_address AS email_address_2, u0_.password AS password_3,
u0_.login_active AS login_active_4, p1_.file AS file_5 
FROM users u0_ INNER JOIN groups_members g3_ ON u0_.group_id = g3_.group_id 
INNER JOIN groups g2_ ON g2_.user_id = g3_.user_id 
INNER JOIN permissions_groups p4_ ON g2_.group_id = p4_.group_id 
INNER JOIN permissions p1_ ON p1_.permission_id = p4_.permission_id 
WHERE u0_.user_id = ?' with params [10]:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'u0_.group_id' in 'on clause'

When I start from Permission

$qb = $this->getEm()->getRepository('AppBundle:Permission')
    ->createQueryBuilder('p')
    ->addSelect('u.user_id')
    ->innerJoin('p.groups', 'g')
    ->innerJoin('g.users', 'u')
    ->andWhere('u.user_id = :user_id')
    ->setParameter('user_id', $loginRow->getUserId());
$rolesData = $qb->getQuery()->getResult();

I get:

SELECT p0_.permission_id AS permission_id_0, p0_.file AS file_1,
p0_.function AS function_2, p0_.system AS system_3, p0_.description AS description_4, 
p0_.permission_depends_on_id AS permission_depends_on_id_5, p0_.helpinfo AS helpinfo_6,
p0_.active AS active_7, u1_.user_id AS user_id_8 
FROM permissions p0_ 
INNER JOIN permissions_groups p3_ ON p0_.permission_id = p3_.permission_id 
INNER JOIN groups g2_ ON g2_.group_id = p3_.group_id 
INNER JOIN groups_members g4_ ON g2_.user_id = g4_.user_id 
INNER JOIN users u1_ ON u1_.group_id = g4_.group_id WHERE u1_.user_id = ?' with params [10]:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'g2_.user_id' in 'on clause'

My entity configuration is below

AppBundle\Entity\User:
    type: entity
    table: users
    id:
        user_id:
            type: integer
            generator: { strategy: AUTO }
    manyToMany:
        groups:
            targetEntity: Group
            inversedBy: users
            joinTable:
                name: groups_members
                joinColumns:
                    group_id:
                        referencedColumnName: group_id
                inverseJoinColumns:
                    user_id:
                        referencedColumnName: user_id
    fields:
        real_name:
            type: string
            length: 255
        email_address:
            type: string
            length: 255
        password:
            type: string
            length: 50
        login_active:
            type: integer

AppBundle\Entity\Group:
    type: entity
    table: groups
    id:
        group_id:
            type: integer
            generator: { strategy: AUTO }
    manyToMany:
        users:
            targetEntity: User
            mappedBy: groups
        permissions:
            targetEntity: Permission
            inversedBy: groups
            joinTable:
                name: permissions_groups
                joinColumns:
                    group_id:
                        referencedColumnName: group_id
                inverseJoinColumns:
                    permission_id:
                        referencedColumnName: permission_id
    fields:
        group_name:
            type: string
            length: 255
        group_description:
            type: string
            length: 255
        max_booking_slots:
            type: integer

AppBundle\Entity\Permission:
    type: entity
    table: permissions
    id:
        permission_id:
            type: integer
            generator: { strategy: AUTO }
    manyToMany:
        groups:
            targetEntity: Group
            mappedBy: permissions
    fields:
        file:
            type: string
            length: 255
        function:
            type: string
            length: 255
        system:
            type: integer
        description:
            type: string
            length: 255
        permission_depends_on_id:
            type: integer
        helpinfo:
            type: string
            length: 50
        active:
            type: integer

This is Symfony 3.1.9 with Doctrine 1.6.7

Answer 1

You had inverted joinColumns and inverseJoinColumns in "User to Group" mapping.

So fix this, like that:

AppBundle\Entity\User:
    manyToMany:
        groups:
            targetEntity: Group
            inversedBy: users
            joinTable:
                name: groups_members
                joinColumns:
                    user_id:
                        referencedColumnName: user_id                   
                inverseJoinColumns:
                    group_id:
                        referencedColumnName: group_id