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:
我正在创建一个消息包,其中消息按联系人分组。在我的索引页面上,我显示不同的线程。当您在一个线程上进行clic时,它会显示您和您的联系人之间交换的所有消息。我使用查询生成器在索引页面上显示线程:
$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
;
If I have 3 entries, for exemple:
如果我有3个条目,例如:
+----+------+----+
| id | from | to |
+----+------+----+
| 1 | 1 | 2 |
+----+------+----+
| 2 | 2 | 1 |
+----+------+----+
| 3 | 1 | 2 |
+----+------+----+
I expect:
我预计:
+----+------+----+
| id | from | to |
+----+------+----+
| 3 | 1 | 2 |
+----+------+----+
But I get:
但我得到:
+----+------+----+
| id | from | to |
+----+------+----+
| 2 | 2 | 1 |
+----+------+----+
| 3 | 1 | 2 |
+----+------+----+
I found a way to do it with SQL, using the same alias for from_id and to_id:
我找到了一种方法来使用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
But I don't know how to do it with Doctrine.
但我不知道怎么用Doctrine来做。
EDIT:
编辑:
Until I get a better idea, I use a key to easily "group by".
直到我得到一个更好的主意,我用一把钥匙轻松“分组”。
// 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', 'DESC')
->setParameter(1, $user->getId())
->setMaxResults($pagination) // limit
->setFirstResult($pagination * $page) // offset
;
return $qb->getQuery()->getResult();
2 个解决方案
#1
3
in case that you have many columns in 'group by' you must use addGroupBy() method.
如果'group by'中有许多列,则必须使用addGroupBy()方法。
$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
;
:)
:)
#2
0
Try the following its by using doctrine DQL method -
使用doctrine DQL方法尝试以下方法 -
$query = $em->createQuery("SELECT m.id, m.from_id as c, m.to_id as c FROM AcmeDemoBunlde:Message as m WHERE m.c = 1 GROUP BY m.from_id, m.to_id");
$messageDetails = $query->getResult();
Instead of AcmeDemoBundle replace with your appropriate bundle name.
而不是使用适当的包名称替换AcmeDemoBundle。
#1
3
in case that you have many columns in 'group by' you must use addGroupBy() method.
如果'group by'中有许多列,则必须使用addGroupBy()方法。
$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
;
:)
:)
#2
0
Try the following its by using doctrine DQL method -
使用doctrine DQL方法尝试以下方法 -
$query = $em->createQuery("SELECT m.id, m.from_id as c, m.to_id as c FROM AcmeDemoBunlde:Message as m WHERE m.c = 1 GROUP BY m.from_id, m.to_id");
$messageDetails = $query->getResult();
Instead of AcmeDemoBundle replace with your appropriate bundle name.
而不是使用适当的包名称替换AcmeDemoBundle。