doctrine dbal每组获取最新的聊天消息 [英] doctrine dbal get latest chat message per group
本文介绍了doctrine dbal每组获取最新的聊天消息的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
在尝试进行多项选择时
$result = $this->qb->select('c.id','c.message','acc.name as chat_from', 'c.chat_to as count')
->addSelect("SELECT * FROM chat ORDER BY date_deliver DESC")
->from($this->table,'c')
->join('c','account','acc', 'c.chat_from = acc.id')
->orderBy('date_sent','DESC')
->groupby('chat_from')
->where('chat_to ='.$id)
->execute();
return $result->fetchAll();
我也尝试过
$result = $this->qb->select('c.id','c.message','acc.name as chat_from', 'c.chat_to as count')
->from("SELECT * FROM chat ORDER BY date_deliver DESC",'c')
->join('c','account','acc', 'c.chat_from = acc.id')
->orderBy('date_sent','DESC')
->groupby('chat_from')
->where('chat_to ='.$id)
->execute();
return $result->fetchAll();
我想按分组依据显示数据,然后在最后一个条目内显示数据。
I want to display the data by group by and then display the data inside the last entry.
我用过DOCTRINE DBAL
i' used DOCTRINE DBAL
请帮助我
推荐答案
由于您的问题不清楚,所以我假设您需要获取每个组的最近聊天/消息,对此的等效SQL将是
As your question is unclear so i am assuming you need to get the recent chat/message per group, the equivalent SQL for this will be
SELECT c.id, c.message, a.name as chat_from, c.chat_to as count
FROM account a
JOIN chat c ON(c.chat_from = a.id )
LEFT JOIN chat cc ON(c.chat_from = cc.chat_from AND c.date_sent < cc.date_sent)
WHERE cc.date_sent IS NULL AND c.chat_to = @id
ORDER BY c.date_sent DESC
因此,使用doctrine dbal可以在上面的查询中编写
So using doctrine dbal you can write above query as
$this->qb->select( 'c.id', 'c.message', 'a.name as chat_from', 'c.chat_to as count' )
->from( 'account', 'a' )
->join( 'a', 'chat', 'c', 'c.chat_from = a.id' )
->leftJoin( 'c', 'chat', 'cc', 'c.chat_from = cc.chat_from AND c.date_sent < cc.date_sent' )
->where( 'cc.date_sent IS NULL' )
->andWhere( 'c.chat_to =' . $id )
->orderBy( 'c.date_sent', 'DESC' )
->execute();
再次不查看示例数据和DDL,这不是一个完整的解决方案。
Again without viewing the sample data and DDL its not a complete solution.
这篇关于doctrine dbal每组获取最新的聊天消息的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文