基本上，我試圖選擇所有用戶的權限由 CurrentCompany 過濾，因此如果我們切換 CurrentCompany，我們應該只有具有該 CurrentCompany 權限的用戶。
出于測驗目的，我添加了兩個公司，兩個用戶（fi A 和 B），并將用戶 A 分配給兩個公司，而用戶 B 只分配給一個公司。此外，用戶 A 為每個公司設定了不同的權限，而用戶 B 僅對他添加的一個公司具有權限。
雖然使用生成的 SQL 直接查詢 MySQL 會顯示正確的結果集，但生成的 User::permissions() 集合具有用戶分配給的所有公司的所有權限，但應該只有與 CurrentCompany 相關的權限。
希望我描述的很好理解。因此，簡而言之，問題在于水合的 User->permissions() 關系為用戶分配給他分配的所有公司的所有權限，但查詢 SQL 直接顯示正確的結果。
QueryBuilder 生成的 DQL/SQL：
$this->createQueryBuilder('u')
->join('u.companies', 'c', Join::WITH, 'c.company = :company')
->leftJoin('u.permissions', 'p', Join::WITH, 'p.company = :company')
// ->andWhere('c.company = :company')
// ->andWhere('p.company = :company')
->setParameter('company', $this->tenant->company()->id()->toBinary())
->getQuery()
->getResult()
);
DQL:
SELECT u FROM App\Identity\Domain\User\User u INNER JOIN u.companies c WITH c.company = :company LEFT JOIN u.permissions p WITH p.company = :company
SQL:
SELECT u0_.id AS id_0, u0_.email AS email_1, u0_.username AS username_2, u0_.password AS password_3, u0_.created AS created_4, u0_.deleted AS deleted_5, u0_.active_company_id AS active_company_id_6 FROM user u0_ INNER JOIN user_company u1_ ON u0_.id = u1_.user_id AND (u1_.company_id = 0x3DF17103A3E14FD09D1CEF98D8318230) LEFT JOIN user_permission u2_ ON u0_.id = u2_.user_id AND (u2_.company_id = 0x3DF17103A3E14FD09D1CEF98D8318230) WHERE ((u0_.deleted IS NULL OR u0_.deleted > CURRENT_TIMESTAMP));
資料庫結構：
CREATE TABLE `user` (
`id` binary(16) NOT NULL,
`email` varchar(180) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
`password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
`created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`deleted` datetime DEFAULT NULL,
`username` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
`active_company_id` binary(16) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `UNIQ_8D93D649E7927C74` (`email`),
KEY `FK_USER_ACTIVE_COMPANY` (`active_company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE `user_company` (
`user_id` binary(16) NOT NULL,
`company_id` binary(16) NOT NULL,
`email` varchar(180) COLLATE utf8mb4_unicode_ci NOT NULL,
`id` binary(16) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `UNIQ_USER_COMPANY` (`user_id`,`company_id`),
KEY `FK_17B21745979B1AD6` (`company_id`),
CONSTRAINT `FK_17B21745979B1AD6` FOREIGN KEY (`company_id`) REFERENCES `Company` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `user_permission` (
`user_id` binary(16) NOT NULL,
`permission_id` binary(16) NOT NULL,
`id` binary(16) NOT NULL,
`company_id` binary(16) NOT NULL,
PRIMARY KEY (`id`),
KEY `FK_USER_PERMISSION` (`user_id`),
KEY `FK_USER_PERMISSION_PERMISSION` (`permission_id`),
KEY `FK_USER_PERMISSION_COMPANY` (`company_id`),
CONSTRAINT `FK_USER_PERMISSION_COMPANY` FOREIGN KEY (`company_id`) REFERENCES `company` (`id`) ON DELETE CASCADE,
CONSTRAINT `FK_USER_PERMISSION_PERMISSION` FOREIGN KEY (`permission_id`) REFERENCES `permission` (`id`) ON DELETE CASCADE,
CONSTRAINT `FK_USER_PERMISSION_USER` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
教義映射：
App\Identity\Domain\User\User:
type: entity
table: user
...
oneToMany:
permissions:
targetEntity: App\Identity\Domain\User\UserPermission
orphanRemoval: true
mappedBy: user
joinTable:
name: user_permission
cascade: [ "all" ]
companies:
targetEntity: App\Identity\Domain\UserCompany\UserCompany
mappedBy: user
joinTable:
name: user_company
cascade: [ "all" ]
App\Identity\Domain\User\UserPermission:
type: entity
table: user_permission
...
manyToOne:
user:
targetEntity: App\Identity\Domain\User\User
inversedBy: permissions
joinTable:
name: user_permission
App\Identity\Domain\UserCompany\UserCompany:
type: entity
table: user_company
...
manyToOne:
company:
targetEntity: App\Identity\Domain\Company\Company
inversedBy: userCompanies
user:
targetEntity: App\Identity\Domain\User\User
inversedBy: companies
uj5u.com熱心網友回復：
好的，解決方案是使用Query::HINT_REFRESHor $em->clear()，因為我發現中間件中有另一個對 User 物體的查詢，如果物體已經存在，Doctrine 使用來自 IdentityMap 的快取結果。
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/442199.html