Symfony 4 Doctrine multiple table joins in one property

Question

Very new to symfony and Doctrine. I have the following tables in my database.

mo_user

id   |    email      |   password
__________________________________
9144 | [email protected]  |   !password!

mo_user_role

user_id| role_id
_________________
9144   | 5

mo_permission

id | namespace            | name | description
______________________________________________
1  | admin                | -    |   -
2  | users                | -    |   -
3  | view_summary_report  | -    |   -
4  | view_user_statement  | -    |   -

mo_role_permission

role_id  | permission_id
________________________
    5    | 3
    5    | 4

I am trying to return an array of the permissions of the current user in this case user with id = 9144 which should be array('view_summary_report','view_user_statement').

I have mapped all the tables to their corresponding entity classes. and in MoUser.php entity class which corresponds to mo_user table, I have a permissions method which should return the array but my join from annotations is failing,

My getPermissions() method in MoUser.php

/**
 * @var Collection|MoPermission[]
 * @ORM\ManyToMany(targetEntity="App\Entity\MoPermission")
 * @ORM\JoinTable(
 *     name="mo_user_role",
 *     joinColumns={@ORM\JoinColumn(name="user_id",referencedColumnName="id")},
 *     inverseJoinColumns={@ORM\JoinColumn(name="role_id",referencedColumnName="id")}
 * )
 */
private $permissions;

 public function getPermissions()
{
    $currentPermissions = array();
    foreach ($this->permissions->toArray() as $index => $permission) {
        $currentPermissions[] = $permission->getNamespace();
    }

    //Return default role if Roles are not assigned to this user.
    if(count($currentPermissions)>0) {
        return $currentPermissions;
    } else {
        return array('DEFAULT_PERMISSION');
    }
}

So I figured out the raw sql to achieve what I wanted which is below, but I would like to know the Symfony/Doctrine annotated way of achieving the following raw SQL.

 SELECT t0.id AS id_1, t0.namespace AS namespace_2, t0.name AS name_3, t0.description AS description_4 

 FROM mo_permission t0 

 LEFT JOIN mo_role_permission ON t0.id = mo_role_permission.permission_id 

 LEFT JOIN mo_user_role ON  mo_role_permission.role_id = mo_user_role.role_id

 WHERE mo_user_role.user_id = 9144;

Aurelien · Accepted Answer · 2018-05-11 14:45:47Z

1

I don't think there is a proper way to achieve what you're trying to do directly through property annotations with your current setup.

You could achieve what you want with one of these solution though :

One of mo_user_role and mo_role_permission is not needed, since none of them have additional field. You should just have a mo_user_permission table generated by a ManyToMany relationship between MoUser and MoPermission, which would grant you direct access to MoPermission from MoUser's getPermissions()
Another way would be to create a service which would have a GetPermissionsFromUser(MoUser $moUser) method (for example), calling the proper query from the entity's repository, which you would call when needed.
You could still achieve what you want in your getPermissions() method with your current setup, but you would have to loop through each relation's items to build your new result array manually.

e.g. for last point :

public function getPermissions() {
    $permissions = [];
    foreach($this->roles as $role) {
        foreach($role->getPermissions() as $permission) {
            permissions[] = $permission->getNamespace();
        }
    }
    return $permissions;
}

This would assume you have a MoRole entity, which would make sense regarding your current setup, but you didn't mention it. Otherwise, same logic could still be applied though, it's just a naming matter.

edited May 11, 2018 at 14:45

answered May 11, 2018 at 14:26

Aurelien

1,5039 silver badges15 bronze badges

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

3 Comments

ShanjayG Over a year ago

Thank you, Ill try It out, I do have a MoRole entity. thank you

ShanjayG Over a year ago

Thank you, for me the last bullet point was the easiest to implement. marked it as a correct answer.

Aurelien Over a year ago

Glad it fixed your problem. Keep in mind other ways for future work.

qooplmao · Accepted Answer · 2018-05-12 01:42:36Z

I'm pretty sure that you could do that query using Doctrine (and a QueryBuilder) like...

use Doctrine\ORM\EntityRepository

class PermissionRepository extends EntityRepository
{
    //....

    /**
     * @param UserInterface $user
     * @return array|Permission[]
     */
    public function getPermissionsForUser(UserInterface $user)
    {
        $queryBuilder = $this->createQueryBuilder('permission');

        /**
         * permissions will be in a multi-dimensional array
         * with a single key per array of 'namespace'
         */
        $permissions = $queryBuilder
            ->select('permission.namespace')
            ->join('permission.role', 'role')
            ->join('role.user', 'user')
            ->where($queryBuilder->expr()->eq('user', ':user'))
            ->setParameter('user', $user)
            ->getQuery()
            ->getArrayResult();

        if (count($permissions) > 0) {
            /**
             * If there are any permissions found just return
             * the 'namespace' property from each "sub-array"
             */
            return array_column($permissions, 'namespace');
        }

        return ['DEFAULT_PERMISSION'];
    }

    //...
}

And then you would call it like..

$permissions = $repository->getPermissionsForUser($user);

Collectives™ on Stack Overflow

Symfony 4 Doctrine multiple table joins in one property

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