Symfony 4 API中如何通过Doctrine获取用户对账户的操作权限

Hey there! Let's work through this problem step by step—since you're new to Doctrine ORM, we'll focus on clean, maintainable approaches that fit Symfony's best practices.

First, let's validate your initial ideas and then refine them into something more robust:

Your Original Ideas: Pros and Cons #

• Idea 1: Adding getOperations() to the Account entity
  This isn't ideal. Entities should focus on holding data and core business logic, not querying the database. Injecting a repository into an entity breaks the Single Responsibility Principle and leads to messy, hard-to-test code. Skip this approach.

• Idea 2: Injecting OperationRepository into the controller
  This works, but it's better to keep data-fetching logic out of controllers. Controllers should handle request/response flow, not data processing. We'll move this logic to a dedicated repository instead.

Recommended Solution: Custom Repository Method #

Since you already have a working raw SQL query, we can adapt that to Doctrine either using native SQL (closest to your existing code) or the QueryBuilder (more ORM-friendly). Let's cover both options.

Option 1: Use Native SQL in AccountRepository

This is a direct translation of your raw PHP code into Doctrine's repository pattern:

// src/Repository/AccountRepository.php
namespace App\Repository;

use App\Entity\Account;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\Persistence\ManagerRegistry;

class AccountRepository extends ServiceEntityRepository
{
    public function __construct(ManagerRegistry $registry)
    {
        parent::__construct($registry, Account::class);
    }

    public function findUserAccountsWithPermissions(int $userId): array
    {
        $conn = $this->getEntityManager()->getConnection();

        $sql = "
            SELECT a.id, a.name, o.code
            FROM Permission p
            INNER JOIN Account a ON a.id = p.accountId
            INNER JOIN Role_Operation ro ON ro.roleId = p.roleId
            INNER JOIN Operation o ON o.id = ro.operationId
            WHERE p.userId = :userId
        ";

        $stmt = $conn->prepare($sql);
        $stmt->bindValue(':userId', $userId);
        $stmt->execute();

        // Transform results into your desired structure
        $accounts = [];
        while ($row = $stmt->fetchAssociative()) {
            $accountId = (int)$row['id'];
            if (!isset($accounts[$accountId])) {
                $accounts[$accountId] = [
                    'id' => $accountId,
                    'name' => $row['name'],
                    'operations' => [],
                ];
            }
            $accounts[$accountId]['operations'][] = $row['code'];
        }

        return array_values($accounts);
    }
}

Option 2: Use Doctrine QueryBuilder (ORM-Friendly)

If you want to stick closer to Doctrine's ORM features (and avoid raw SQL), use the QueryBuilder. This assumes you've correctly mapped all entity associations (e.g., Account ↔ Permission, Permission ↔ Role, Role ↔ Role_Operation, Role_Operation ↔ Operation):

// src/Repository/AccountRepository.php
public function findUserAccountsWithPermissions(int $userId): array
{
    $qb = $this->createQueryBuilder('a')
        ->select('a.id', 'a.name', 'o.code')
        ->innerJoin('a.permissions', 'p') // Assumes Account has a $permissions property mapped to Permission
        ->innerJoin('p.role', 'r') // Assumes Permission has a $role property mapped to Role
        ->innerJoin('r.roleOperations', 'ro') // Assumes Role has a $roleOperations property mapped to Role_Operation
        ->innerJoin('ro.operation', 'o') // Assumes Role_Operation has a $operation property mapped to Operation
        ->where('p.user = :userId') // Assumes Permission has a $user property mapped to User
        ->setParameter('userId', $userId);

    $results = $qb->getQuery()->getResult();

    // Transform results into your desired structure
    $accounts = [];
    foreach ($results as $row) {
        $accountId = (int)$row['id'];
        if (!isset($accounts[$accountId])) {
            $accounts[$accountId] = [
                'id' => $accountId,
                'name' => $row['name'],
                'operations' => [],
            ];
        }
        $accounts[$accountId]['operations'][] = $row['code'];
    }

    return array_values($accounts);
}

Use the Repository in Your Controller #

Now call this method from your controller and return the JSON response:

// src/Controller/AccountController.php
namespace App\Controller;

use App\Repository\AccountRepository;
use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Symfony\Component\HttpFoundation\JsonResponse;
use Symfony\Component\Routing\Annotation\Route;

class AccountController extends AbstractController
{
    #[Route('/api/accounts/permissions', methods: ['GET'])]
    public function getUserAccountsWithPermissions(AccountRepository $accountRepository): JsonResponse
    {
        // Get the current user's ID (uses Symfony Security)
        $userId = $this->getUser()->getId();
        
        // Fetch the accounts with their operations
        $accounts = $accountRepository->findUserAccountsWithPermissions($userId);

        // Return the JSON response
        return $this->json($accounts);
    }
}

Bonus: Returning Account Entities with Operations (If Needed) #

If you prefer to return full Account entities with an operations field (instead of arrays), add a non-mapped property to your Account entity and use Symfony's Serializer to include it:

// src/Entity/Account.php
use Doctrine\ORM\Mapping as ORM;
use Symfony\Component\Serializer\Annotation\Groups;

#[ORM\Entity(repositoryClass: AccountRepository::class)]
class Account
{
    #[ORM\Id]
    #[ORM\GeneratedValue]
    #[ORM\Column(type: 'integer')]
    private ?int $id = null;

    #[ORM\Column(type: 'string', length: 255)]
    #[Groups(['account:read'])]
    private ?string $name = null;

    // Non-mapped property for operations
    #[Groups(['account:read'])]
    private array $operations = [];

    // ... existing getters/setters

    public function getOperations(): array
    {
        return $this->operations;
    }

    public function setOperations(array $operations): self
    {
        $this->operations = $operations;
        return $this;
    }
}

Then adjust the repository method to populate this property:

// src/Repository/AccountRepository.php
public function findUserAccountsWithPermissions(int $userId): array
{
    $qb = $this->createQueryBuilder('a')
        ->select('a', 'o.code')
        ->innerJoin('a.permissions', 'p')
        ->innerJoin('p.role', 'r')
        ->innerJoin('r.roleOperations', 'ro')
        ->innerJoin('ro.operation', 'o')
        ->where('p.user = :userId')
        ->setParameter('userId', $userId);

    $results = $qb->getQuery()->getResult();

    $accounts = [];
    foreach ($results as $row) {
        /** @var Account $account */
        $account = $row[0];
        $operationCode = $row[1];
        
        $accountId = $account->getId();
        if (!isset($accounts[$accountId])) {
            $accounts[$accountId] = $account;
            $accounts[$accountId]->setOperations([]);
        }
        $accounts[$accountId]->getOperations[] = $operationCode;
    }

    return array_values($accounts);
}

Update the controller to use the serialization group:

return $this->json($accounts, context: ['groups' => ['account:read']]);

This approach keeps your entities clean and leverages Symfony's Serializer to handle the response format.

内容的提问来源于stack exchange，提问作者Albert