如何做左派加入原则？ [英] How to do left join in Doctrine?

问题描述

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

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('Credit\Entity\UserCreditHistory', 'a')
            ->leftJoin('User\Entity\User', 'u', \Doctrine\ORM\Query\Expr\Join::WITH, 'a.user = u.id')
            ->where("a.user = $users ")
            ->orderBy('a.created_at', 'DESC');

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

    return $results;
}

但是，我收到这个错误：

However, I get this error :

[Syntax Error] line 0，col 98：Error：Expected Doctrine\ORM\Query\Lexer :: T_WITH，got'ON'

[Syntax Error] line 0, col 98: Error: Expected Doctrine\ORM\Query\Lexer::T_WITH, got 'ON'

编辑：我在join子句中用'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.

推荐答案

如果您在指向用户的财产上有关联（让我们说 Credit \Entity\UserCreditHistory＃user ，从你的例子中挑选），那么语法很简单：

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

public function getHistory($users) {
    $qb = $this->entityManager->createQueryBuilder();
    $qb
        ->select('a', 'u')
        ->from('Credit\Entity\UserCreditHistory', '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('Credit\Entity\UserCreditHistory', 'a')
        ->leftJoin(
            'User\Entity\User',
            'u',
            \Doctrine\ORM\Query\Expr\Join::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,
    ),
    // ...
)

这篇关于如何做左派加入原则？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！