Doctrine hydrates relations wrong, but result set after applying SQL directly is right-CodePudding

Basically, I'm trying to select all Users with their Permissions filtered by CurrentCompany, so if we switched CurrentCompany we should have only Users with their Permissions only for that CurrentCompany.

For testing purposes, I have added two Companies, two Users (f.i. A and B), and assigned user A to both Companies, and user B just only to one Company. Also, user A has different permissions set for each company, and user B has permissions just only for one company he added in.

While querying directly MySQL with generated SQL shows the right resultset, but the resulting Collection for User::permissions() has all permissions for all companies User assigned to, but there should be just only permissions related to the CurrentCompany.

Hope I described it well to understand. So, in two words, the issue is that hydrated User->permissions() relation have all permissions assigned for the User for all companies he assigned to, but querying SQL directly shows the right results.

QueryBuilder resulting 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));

DB structure:

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;

Doctrine mapping:

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

CodePudding user response：

Okay, the solution is to use Query::HINT_REFRESH or $em->clear(), because I found there was another query for the User entity in middleware, and Doctrine uses cached results from IdentityMap, if the entity is already present there.