我试图在Yii应用程序上显示用户收件箱,但我不能写一个正确的标准。
会话基于(user_id, recipient_id)…所以没有对话表,我的问题是如何排序列表对话没有对话表?!
如果我使用yii关系呢?!
表结构:id (int)
message (text)
user_id (int)
recipient_id (int)
sent_at (int)
,我的标准是:
$criteria = new CDbCriteria();
$criteria->condition = "recipient_id=:user_id OR user_id=:user_id";
// $criteria->group ='user_id';
// $criteria->select ='*';
// $criteria->distinct = true;
$criteria->order = "sent_at ASC";
$criteria->limit = 5;
$criteria->params = array(':user_id' => Yii::app()->user->id);
$model = UserMessage::model()->findAll($criteria);
显示所有消息收件人
您必须按消息本身进行分组。但是,如果这是一个大的文本/字符串字段,那么这将不是一个有效的数据库查询,并且将非常缓慢。我强烈建议你重新审视一下你的数据库结构。
我已经实现了一些非常类似的东西,但是我已经转换了我的表来显示消息之间的关系。
id (int)
message (text)
user_id (int)
recipient_id (int)
sent_at (int)
reply_to (int) default 0 ;;; I added this field
使用这个我可以搜索高层对话
SELECT * from user_message where reply_to is NULL or reply_to = 0;
使用此方案,对于new消息,reply_to字段将为0。
在Yii$criteria = new CDbCriteria();
$criteria->condition = "reply_to is NULL or reply_to = 0";
$criteria->order = "sent_at ASC";
$criteria->limit = 5;
$model = UserMessage::model()->findAll($criteria);
查看消息和创建回复时,将reply_to代码设置为上一级的值。这允许无限次嵌套。
MSG : I need help with this question (id = 1, reply_to = 0)
MSG : L Re: I need help with this question (id = 2, reply_to = 1)
MSG : L Re: I need help with this question (id = 3, reply_to = 1)
MSG : L Re: I need help with this question (id = 4, reply_to = 3)
MSG : L Re: I need help with this question (id = 5, reply_to = 3)
MSG : L Re: I need help with this question (id = 8, reply_to = 5)
MSG : L Re: I need help with this question (id = 6, reply_to = 1)
MSG : L Re: I need help with this question (id = 7, reply_to = 6)
如果没有在数据库表中添加更多信息,则不能将消息划分为对话
除非,你的对话是user_id - recipient_id
唯一的。
在这种情况下,您可以使用Group By user_id, recipient_id