如何在 Doctrine 中做 left join?

2022-01-03 00:00:00 php doctrine-orm zend-framework2

这是我尝试显示用户历史记录的功能.为此，我需要显示用户的当前信用以及他的信用历史.

This is my function where I'm trying to show the User history. For this I need to display the user's current credits along with his credit history.

这就是我想要做的:

 public function getHistory($users) {
    $qb = $this->entityManager->createQueryBuilder();
    $qb->select(array('a','u'))
            ->from('CreditEntityUserCreditHistory', 'a')
            ->leftJoin('UserEntityUser', 'u', DoctrineORMQueryExprJoin::WITH, 'a.user = u.id')
            ->where("a.user = $users ")
            ->orderBy('a.created_at', 'DESC');

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

    return $results;
}

但是，我收到此错误:

[语法错误] 第 0 行，第 98 行:错误:预期的 DoctrineORMQueryLexer::T_WITH，得到 'ON'

[Syntax Error] line 0, col 98: Error: Expected DoctrineORMQueryLexer::T_WITH, got 'ON'

编辑:我在连接子句中用WITH"替换了ON"，现在我看到的只有来自连接列的 1 个值.

Edit: I replaced 'ON' with 'WITH' in the join clause and now what I see is only 1 value from the joined column.

推荐答案

如果你有一个指向用户的属性的关联(比方说 CreditEntityUserCreditHistory#user，从你的例子)，那么语法就很简单了:

If you have an association on a property pointing to the user (let's say CreditEntityUserCreditHistory#user, picked from your example), then the syntax is quite simple:

public function getHistory($users) {
    $qb = $this->entityManager->createQueryBuilder();
    $qb
        ->select('a', 'u')
        ->from('CreditEntityUserCreditHistory', 'a')
        ->leftJoin('a.user', 'u')
        ->where('u = :user')
        ->setParameter('user', $users)
        ->orderBy('a.created_at', 'DESC');

    return $qb->getQuery()->getResult();
}

由于您在此处对连接结果应用条件，因此使用 LEFT JOIN 或仅使用 JOIN 是相同的.

Since you are applying a condition on the joined result here, using a LEFT JOIN or simply JOIN is the same.

如果没有可用的关联，则查询如下所示

If no association is available, then the query looks like following

public function getHistory($users) {
    $qb = $this->entityManager->createQueryBuilder();
    $qb
        ->select('a', 'u')
        ->from('CreditEntityUserCreditHistory', 'a')
        ->leftJoin(
            'UserEntityUser',
            'u',
            DoctrineORMQueryExprJoin::WITH,
            'a.user = u.id'
        )
        ->where('u = :user')
        ->setParameter('user', $users)
        ->orderBy('a.created_at', 'DESC');

    return $qb->getQuery()->getResult();
}

这将产生如下所示的结果集:

This will produce a resultset that looks like following:

array(
    array(
        0 => UserCreditHistory instance,
        1 => Userinstance,
    ),
    array(
        0 => UserCreditHistory instance,
        1 => Userinstance,
    ),
    // ...
)

相关文章