将MySQL查询端口

我正在尝试使用学说来设置一对多数据库。一个用户可以具有多个角色，当我获得用户时，我想获得关联的角色。我有一个可以做到的原始mysql查询。

SELECT 
    u.*, 
    GROUP_CONCAT(r.role) as roles 
FROM 
    users u 
    INNER JOIN user_roles ur ON ur.user_id = u.id 
    INNER JOIN roles r ON r.id = ur.role_id
GROUP BY id;
+----+----------+------------------------+--------------------------------------------------------------+-----------+----------------------+
| id | username | email                  | password                                                     | is_active | GROUP_CONCAT(r.role) |
+----+----------+------------------------+--------------------------------------------------------------+-----------+----------------------+
|  1 | admin    | my@email.address       | $2a$08$jHZj/wJfcVKlIwr5AvR78euJxYK7Ku5kURNhNx.7.CSIJ3Pq6LEPC |         1 | ROLE_USER,ROLE_ADMIN |
+----+----------+------------------------+--------------------------------------------------------------+-----------+----------------------+

这是我实际上可以自动在学说中复制的东西吗？如果是这样，我会在哪里出错？这是我当前的（破碎的）类（对代码转储表示歉意）。

当前记录的错误是：

[2016-12-20 12:01:35] security.INFO: Authentication request failed. {"exception":"[object] (Symfony\Component\Security\Core\Exception\Auth
enticationServiceException(code: 0): Notice: Undefined index: user at /var/www/vendor/symfony/symfony/src/Symfony/Component/Security/Core/Authe
ntication/Provider/DaoAuthenticationProvider.php:94, Symfony\Component\Debug\Exception\ContextErrorException(code: 0): Notice: Undefined in
dex: user at /var/www/vendor/doctrine/orm/lib/Doctrine/ORM/Persisters/Entity/BasicEntityPersister.php:1768)"} []

user.php

<?php
namespace AppBundleEntity;
use DoctrineORMMapping as ORM;
use SymfonyComponentSecurityCoreUserUserInterface;
use DoctrineCommonCollectionsArrayCollection;
/**
 * @ORMTable(name="users")
 * @ORMEntity(repositoryClass="AppBundleRepositoryUserRepository")
 */
class User implements UserInterface, Serializable
{
    ...
    /**
     * @var DoctrineCommonCollectionsArrayCollection
     *
     * @ORMOneToMany(targetEntity="UserRoles", mappedBy="user", fetch="EAGER")
     */
    private $roles;

    public function __construct()
    {
        $this->isActive = true;
        // may not be needed, see section on salt below
        // $this->salt = md5(uniqid(null, true));
        $this->roles = new ArrayCollection();
    }
    ...
    public function getRoles()
    {
        return $this->roles;
    }
    /**
     * Add role
     *
     * @param AppBundleEntityUserRoles $role
     *
     * @return User
     */
    public function addRole(AppBundleEntityUserRoles $role)
    {
        $this->roles[] = $role;
        return $this;
    }
    /**
     * Remove role
     *
     * @param AppBundleEntityUserRoles $role
     */
    public function removeRole(AppBundleEntityUserRoles $role)
    {
        $this->roles->removeElement($role);
    }
}

角色.php

<?php
namespace AppBundleEntity;
use DoctrineORMMapping as ORM;
/**
 * Role
 *
 * @ORMTable(name="roles")
 * @ORMEntity(repositoryClass="AppBundleRepositoryRoleRepository")
 */
class Role
{
    /**
     * @var int
     *
     * @ORMColumn(name="id", type="integer")
     * @ORMId
     * @ORMGeneratedValue(strategy="AUTO")
     */
    private $id;
    /**
     * @var string
     *
     * @ORMColumn(name="role", type="string", length=255, unique=true)
     */
    private $role;

    /**
     * Get id
     *
     * @return int
     */
    public function getId()
    {
        return $this->id;
    }
    /**
     * Set role
     *
     * @param string $role
     *
     * @return Role
     */
    public function setRole($role)
    {
        $this->role = $role;
        return $this;
    }
    /**
     * Get role
     *
     * @return string
     */
    public function getRole()
    {
        return $this->role;
    }
}

userroles.php

<?php
namespace AppBundleEntity;
use DoctrineORMMapping as ORM;
/**
 * UserRoles
 *
 * @ORMTable(name="user_roles")
 * @ORMEntity(repositoryClass="AppBundleRepositoryUserRolesRepository")
 */
class UserRoles
{
    /**
     * @ORMColumn(type="integer")
     * @ORMId
     * @ORMGeneratedValue(strategy="AUTO")
     */
    private $id;

    /**
     * @var int
     *
     * @ORMManyToOne(targetEntity="AppBundleEntityUser", inversedBy="userroles")
     * @ORMJoinColumn(name="user_id", referencedColumnName="id")
     */
    private $userId;
    /**
     * @var int
     *
     * @ORMManyToOne(targetEntity="AppBundleEntityRole")
     * @ORMJoinColumn(name="role_id", referencedColumnName="id")
     */
    private $roleId;

    /**
     * Set userId
     *
     * @param integer $userId
     *
     * @return UserRoles
     */
    public function setUserId($userId)
    {
        $this->userId = $userId;
        return $this;
    }
    /**
     * Get userId
     *
     * @return int
     */
    public function getUserId()
    {
        return $this->userId;
    }
    /**
     * Set roleId
     *
     * @param integer $roleId
     *
     * @return UserRoles
     */
    public function setRoleId($roleId)
    {
        $this->roleId = $roleId;
        return $this;
    }
    /**
     * Get roleId
     *
     * @return int
     */
    public function getRoleId()
    {
        return $this->roleId;
    }
    /**
     * Get id
     *
     * @return integer
     */
    public function getId()
    {
        return $this->id;
    }
}

在您的$roles联想中，您的User实体内部将mappedBy属性设置为user，但是您的UserRoles实体没有这样的字段。相反，它具有称为userId的字段。学说试图使用您的映射找到协会。来自@mappedBy属性的user值在数据库中找不到，因此错误消息：

未定义的索引...

由于您正在映射对象，因此应重新考虑UserRoles实体内的命名约定。因此，请勿使用$userId和$roleId，而只需使用User和Role，并且还设置了依赖项注入和返回值，以匹配User和Role类。

由于UserRoles是一个实体（而不是集合），所以我还建议将其重命名为UserRole（单数）。它将帮助您了解自己拥有的东西。

从这样的更改UserRoles开始：

<?php
namespace AppBundleEntity;
use DoctrineORMMapping as ORM;
use AppBundleEntityUser;
use AppBundleEntityRole;
/**
 * UserRole
 *
 * @ORMTable(name="user_roles")
 * @ORMEntity(repositoryClass="AppBundleRepositoryUserRoleRepository")
 */
class UserRole
{
    /**
     * @ORMColumn(type="integer")
     * @ORMId
     * @ORMGeneratedValue(strategy="AUTO")
     */
    private $id;
    /**
     * @var User
     * @ORMManyToOne(targetEntity="AppBundleEntityUser", inversedBy="userRoles")
     * @ORMJoinColumn(name="user_id", referencedColumnName="id")
     */
    private $user;
    /**
     * @var Role
     * @ORMManyToOne(targetEntity="AppBundleEntityRole")
     * @ORMJoinColumn(name="role_id", referencedColumnName="id")
     */
    private $role;
    /**
     * Set user
     *
     * @param User $user
     * @return UserRole
     */
    public function setUser(User $user)
    {
        $this->user = $user;
        return $this;
    }
    /**
     * Get user
     *
     * @return User
     */
    public function getUser()
    {
        return $this->user;
    }
    /**
     * Set role
     *
     * @param Role $role
     * @return UserRole
     */
    public function setRole(Role $role)
    {
        $this->role = $role;
        return $this;
    }
    /**
     * Get role
     *
     * @return Role
     */
    public function getRole()
    {
        return $this->role;
    }
    /**
     * Get id
     *
     * @return integer
     */
    public function getId()
    {
        return $this->id;
    }
}

并更改您的User实体以相应地匹配这些更改：

<?php
namespace AppBundleEntity;
use DoctrineORMMapping as ORM;
use SymfonyComponentSecurityCoreUserUserInterface;
use DoctrineCommonCollectionsArrayCollection;
use AppBundleEntityUserRole;
use Serializable;
/**
 * @ORMTable(name="users")
 * @ORMEntity(repositoryClass="AppBundleRepositoryUserRepository")
 */
class User implements UserInterface, Serializable
{  
    ...
    /**
     * @var ArrayCollection
     *
     * @ORMOneToMany(targetEntity="UserRole", mappedBy="user", fetch="EAGER")
     */
    private $userRoles;
    public function __construct()
    {
        $this->isActive = true;
        // may not be needed, see section on salt below
        // $this->salt = md5(uniqid(null, true));
        $this->userRoles = new ArrayCollection();
    }
    ...
    /**
     * @return Collection
     */
    public function getUserRoles()
    {
        return $this->userRoles;
    }
    /**
     * Add user role
     *
     * @param UserRole $userRole
     *
     * @return User
     */
    public function addUserRole(UserRole $userRole)
    {
        $this->userRoles[] = $userRole;
        return $this;
    }
    /**
     * Remove role
     *
     * @param UserRole $userRole
     */
    public function removeUserRole(UserRole $userRole)
    {
        $this->userRoles->removeElement($userRole);
    }
}

由于您的User类的存储库称为UserRepository，我还将UserRolesRepository重命名为UserRoleRepository（单数）。

相关内容

最新更新

热门标签：