本站使用Swoole做了类似QQ的聊天功能,(点击用户头像或名称,进入用户主页,在用户主页点击“发私信”按钮进入聊天界面),可与其他会员即时聊天,聊天功能暂不详述,下面说说遇到的问题。我想在聊天窗口右侧,显示用户的最近联系人列表,如下图所示。
最近联系人要显示的内容有 最近联系人姓名,头像,最新的聊天内容,时间
mysql表结构如下
sponsor_user 消息发起人
to_user 消息接收人
type type=3的表示私聊
detail 聊天内容
add_time 时间
例如现在我要查我的最近联系人,我的id是1,我要查发起人是我或者接收人是我的记录sponsor_user = 1 or to_user = 1 ,这样会把所有和我有联系的记录查出来,但是这样的话,没办法去重,因为不知道group by 哪个字段,而且我只想找和我有联系的最新的那条记录,并且要找的是 非我 的那个用户的信息,因为我的联系人列表不能包括我。
怎么办呢,倒是有一个笨方法,先查出我发起的聊天,根据接收人去重;再查发给我的记录,根据发起人去重,然后遍历看哪个记录的时间最新。
#(1)查询我主动发起的联系人
$sql = "select * from (SELECT * FROM `test_message` WHERE sponsor_user = {$mid} AND type = '3' ORDER BY id DESC limit 9999) as tmp GROUP BY to_user";
$my_to_other_history = M()->query($sql);
$my_history_s = [];
if(!empty($my_to_other_history)){
foreach ($my_to_other_history as $v){
if(!isset($my_history_s[$v['to_user']])){
$my_history_s[$v['to_user']] = $v;
}
}
}
#(2)查询给我发起会话的联系人
$sql = "select * from (SELECT * FROM `test_message` WHERE to_user = {$mid} AND type = '3' ORDER BY id DESC limit 9999) as tmp GROUP BY sponsor_user";
$other_to_me_history = M()->query($sql);
$to_history_s = [];
if(!empty($other_to_me_history)){
foreach ($other_to_me_history as $v){
if(!isset($to_history_s[$v['sponsor_user']])){
$to_history_s[$v['sponsor_user']] = $v;
}
}
}
if(!empty($my_history_s) && empty($to_history_s)){
$res = $my_history_s;
}elseif (empty($my_history_s) && !empty($to_history_s)){
$res = $to_history_s;
}elseif (!empty($my_history_s) && !empty($to_history_s)){
$aList = [];
$i = 0;
foreach ($my_history_s as $k => $v){
if(isset($to_history_s[$k])){//如果给我聊天的人我有回复
$v = $to_history_s[$k]['add_time'] > $v['add_time'] ? $to_history_s[$k] : $v;//双方有聊天,留下新的那条记录
}
$aList[$i] = $v;
$i++;
}
$res = array2_sort($aList,'add_time');//根据时间排序数组
}
然后再找对方的信息
foreach ($res as $k => $v)
{
$opposite_uid = $v['sponsor_user'] == $my_uid ? $v['to_user'] : $v['sponsor_user'];//对方的ID
$opposite_uInfo = $xx_user_model->getInfoById($opposite_uid);
$v['opposite_uInfo'] = $opposite_uInfo;
$recent_con_user[$k] = $v;
}
但是这么做太费劲了,而且最重要的不能mysql分页,只能把结果全部查出来,然后php分割数组实现分页效果(为了分页而分页,没有意义)
这该如何是好呢,请教完同事后得知MySQL有union这个函数,好像可以实现我们的需求,然后摸索了半小时后,终于解决了问题。。。。
SELECT t.*,u.username,u.avatar_src from (
SELECT id,uid,detail,add_time FROM (
(SELECT id,to_user as uid,detail,add_time FROM test_message WHERE sponsor_user = 1 and type='3')
UNION
(SELECT id,sponsor_user as uid,detail,add_time FROM test_message WHERE to_user = 1 and type='3')
ORDER BY add_time DESC) as tmp GROUP BY tmp.uid ORDER BY tmp.add_time DESC limit 20
) as t LEFT JOIN test_member as u on t.uid = u.id
查询出的结果如下
这下就大功告成了,分页的问题也解决了。