php - 选择多个实体学说

Question

我正在尝试执行以下查询:

$qb = $this->getEntityManager()->createQueryBuilder();
$referredUsers = $qb->select(['referred', 'referral', 'match'])
    ->from('ProjectContestBundle:UserParticipation', 'up')
    ->innerJoin('ProjectUserBundle:User', 'referred', 'WITH', 'up.user = referred')
    ->innerJoin('ProjectUserBundle:User', 'referral', 'WITH', 'up.referral = referral.referralCode')
    ->innerJoin('ProjectContestBundle:UserParticipation', 'upr', 'WITH', 'upr.user = referral')
    ->innerJoin('ProjectMatchBundle:Match', 'match', 'WITH', 'match.owner = referred')
    ->where('up.contest = :contest')->andWhere('upr.contest = :contest')
    ->setParameter('contest', $contest)
    ->getQuery()->getResult();

我期待结果是一个行数组，其中每一行都是 [User, User, Match] 类型，然而 Doctrine 返回一个包含实体混合内容的普通数组。

然后我尝试通过以下方式使其更简单:

$qb = $this->getEntityManager()->createQueryBuilder();
$referredUsers = $qb->select(['referred.id', 'referral.id', 'match.id'])
    ->from('ProjectContestBundle:UserParticipation', 'up')
    ->innerJoin('ProjectUserBundle:User', 'referred', 'WITH', 'up.user = referred')
    ->innerJoin('ProjectUserBundle:User', 'referral', 'WITH', 'up.referral = referral.referralCode')
    ->innerJoin('ProjectContestBundle:UserParticipation', 'upr', 'WITH', 'upr.user = referral')
    ->innerJoin('ProjectMatchBundle:Match', 'match', 'WITH', 'match.owner = referred')
    ->where('up.contest = :contest')->andWhere('upr.contest = :contest')
    ->setParameter('contest', $contest)
    ->getQuery()->getResult();

结果为 [{"id":1},{"id":2},{"id":3},{"id":4}]。如果我在数据库上运行查询，它工作正常:

SELECT u0_.ID AS ID0, u1_.ID AS ID1, m2_.ID AS ID2 FROM UserParticipation u3_ INNER JOIN users u0_ ON (u3_.user_id = u0_.ID) INNER JOIN users u1_ ON (u3_.l = u1_.referralCode) INNER JOIN UserParticipation u4_ ON (u4_.user_id = u1_.ID) INNER JOIN matches m2_ ON (m2_.ownerID = u0_.ID) AND (m2_.deletedAt IS NULL) WHERE u3_.contest_id = 1 AND u4_.contest_id = 1;

返回:

+-----+-----+-----+
| ID0 | ID1 | ID2 |
+-----+-----+-----+
|   1 |   9 |   1 |
|   1 |   9 |   2 |
|   1 |   9 |   3 |
|  10 |   9 |   4 |
+-----+-----+-----+

所以我尝试添加一些 as 和:

$referredUsers = $qb->select(['referred.id as r1', 'referral.id as r2', 'match.id as m'])
    ->from('ProjectContestBundle:UserParticipation', 'up')
    ->innerJoin('ProjectUserBundle:User', 'referred', 'WITH', 'up.user = referred')
    ->innerJoin('ProjectUserBundle:User', 'referral', 'WITH', 'up.referral = referral.referralCode')
    ->innerJoin('ProjectContestBundle:UserParticipation', 'upr', 'WITH', 'upr.user = referral')
    ->innerJoin('ProjectMatchBundle:Match', 'match', 'WITH', 'match.owner = referred')
    ->where('up.contest = :contest')->andWhere('upr.contest = :contest')
    ->setParameter('contest', $contest)
    ->getQuery()->getArrayResult();

结果是正确的!

[{"r1":1,"r2":9,"m":1},{"r1":1,"r2":9,"m":2},{"r1":1,"r2":9,"m":3},{"r1":10,"r2":9,"m":4}]

如果我尝试使用非 id 版本返回，保留 as 我仍然会返回一个行数组，其中每一行都是一个不同的实体，但是我想要一行[User, User, Match] 如选择所示(与 MySql 原始结果相同，只是带有实体)。

Answer 1

简答

学说仅在关系中选择水合物对象。

长答案

Doctrine 默认返回“来自实体”作为查询的所有者。我的意思是，它会返回这个实体的数组，并且还会根据您的映射填充该实体内的剩余实体。

我建议您将 up.referral 和 user.referralCode 更改为关系而不是列。另一个更改是映射 User 以访问 Match(如果它是一对一关系)。

让我们稍微简化一下代码:

$qb = $this->getEntityManager()->createQueryBuilder();

//it will return array of UserParticipation
$userParticipations = $qb->select('up, referred, match')
    ->from('ProjectContestBundle:UserParticipation', 'up')
    ->innerJoin('up.user','referred')
    ->innerJoin('up.referralUser', 'referral')
    ->innerJoin('referred.match', 'match')
    ->where('up.contest = :contest')
    ->setParameter('contest', $contest)
    ->getQuery()->getResult();

$userParticipations 数组中的每一项都将是一个UserParticipation。从它你可以访问被推荐的($userParticipations[0]->getUser())，被推荐的($userParticipations[0]->getReferralUser())，匹配 ($userParticipations[0]->getUser()->getMatch())，等等。

这几乎是您的第一次尝试。

注意以上代码仅在您对映射和数据库进行一些更改时才有效。