我正在创建一个消息包,其中消息按联系人分组.在我的索引页面上,我显示不同的线程.当您在一个线程上,它显示您和您的联系人之间交换的所有消息.我使用查询生成器来显示索引页面上的线程:
$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个条目,例如:
+----+------+----+ | 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使用相同的别名:
SELECT id,from_id as c,to_id as c FROM Message WHERE c = 1 GROUP BY from_id,to_id
但是我不知道怎么用原则去做.
编辑:
直到我得到一个更好的主意,我使用一个键轻松地“分组”.
// entity /** * @ORM\Column(name="key",type="string",length=40) */ private $key; /** * @ORM\PrePersist() */ 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',$user->getId()) ->setMaxResults($pagination) // limit ->setFirstResult($pagination * $page) // offset ; return $qb->getQuery()->getResult();