如何在 Doctrine 中做 left join?

How to do left join in Doctrine?(如何在 Doctrine 中做 left join?)

问题描述

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

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,
    ),
    // ...
)

这篇关于如何在 Doctrine 中做 left join?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！