Symfony2/Doctrine 中两列的查询生成器和分组依据生成重复项 [英] Query Builder and Group By on two columns in Symfony2 / Doctrine generate duplicates

问题描述

我正在创建一个消息包，其中的消息按联系人分组.在我的索引页面上，我显示了不同的线程.当您单击一个线程时，它会显示您与联系人之间交换的所有消息.我使用查询生成器在我的索引页面上显示线程:

I'm creating a message bundle where messages are grouped per contacts. On my index page, I display different threads. When you clic on one thread, it show all the messages exchanged between you and your contact. I use a Query Builder to display the threads on my index page:

$qb = $this->createQueryBuilder('m')
    ->where('m.from = ?1 or m.to = ?1')
    ->groupBy('m.to, m.from')
    ->orderBy('m.date', 'DESC')
    ->setParameter(1, $user->getId())
    ->setMaxResults($pagination) // limit
    ->setFirstResult($pagination * $page) // offset
;

如果我有 3 个条目，例如:

If I have 3 entries, for exemple:

+----+------+----+
| id | from | to |
+----+------+----+
| 1  | 1    | 2  |
+----+------+----+
| 2  | 2    | 1  |
+----+------+----+
| 3  | 1    | 2  |
+----+------+----+

我希望:

+----+------+----+
| id | from | to |
+----+------+----+
| 3  | 1    | 2  |
+----+------+----+

但我明白了:

+----+------+----+
| id | from | to |
+----+------+----+
| 2  | 2    | 1  |
+----+------+----+
| 3  | 1    | 2  |
+----+------+----+

我找到了一种用 SQL 实现的方法，对 from_id 和 to_id 使用相同的别名:

I found a way to do it with SQL, using the same alias for from_id and to_id:

SELECT id, from_id as c, to_id as c FROM Message WHERE c = 1 GROUP BY from_id, to_id

但我不知道如何使用 Doctrine.

But I don't know how to do it with Doctrine.

在我有了更好的主意之前，我会使用一个键来轻松地分组".

Until I get a better idea, I use a key to easily "group by".

// entity

/**
* @ORMColumn(name="key", type="string", length=40)
*/
private $key;

/**
 * @ORMPrePersist()
 */
public function setOnPrePersist()
{
    if($this->from < $this->to) {
        $key = $this->from . 't' . $this->to;
    } else {
        $key = $this->to . 't' . $this->from;
    }

    $this->key = $key;
}

// query builder

$qb = $this->createQueryBuilder('m')
    ->where('m.from = ?1 or m.to = ?1')
    ->groupBy('m.key')
    ->orderBy('m.date', 'DESC')
    ->setParameter(1, $user->getId())
    ->setMaxResults($pagination) // limit
    ->setFirstResult($pagination * $page) // offset
;

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

推荐答案

如果'group by'中有很多列，则必须使用 addGroupBy() 方法.

in case that you have many columns in 'group by' you must use addGroupBy() method.

$qb = $this->createQueryBuilder('m')
    ->where('m.from = ?1 or m.to = ?1')
    ->groupBy('m.to')
    ->addGroupBy('m.from')
    ->orderBy('m.date', 'DESC')
    ->setParameter(1, $user->getId())
    ->setMaxResults($pagination) // limit
    ->setFirstResult($pagination * $page) // offset
;

:)

这篇关于Symfony2/Doctrine 中两列的查询生成器和分组依据生成重复项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！