TP5.x——聊天列表查询

前言

查询聊天列表，并返回最后一条聊天记录。这个有一个比较尴尬的点就是，一个是你主动发出的，一个是你接收的。
所以这个SQL会比较长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长长！

步骤

查询出自己发的消息然后联合查询接收到的消息,这里要提下union的特性,就是两个合并的查询语句

字段数量必须一致!
字段顺序必须一致!
数据类型也要相似!

原始SQL

SELECT `uid`,`nickname`,`avatar`,`content`,`time` FROM ( 
    SELECT * FROM ( 
        SELECT toid as uid,`content`,`time` FROM `chat_log` WHERE ( fromid =1 )  
        UNION 
        SELECT fromid as uid,content,time FROM chat_log WHERE toid = 1 
    ) `tmp` GROUP BY tmp.uid ORDER BY `tmp`.`time` 
) `t` LEFT JOIN `chat_user` `u` ON `t`.`uid`=`u`.`id`

查询结果

改为TP5

整理思路也很简单,就是先进行**两次**sql语句的构造,然后用左连接查询.

 $sub = Db::name('log')
                ->field('toid as uid, content, time')
                ->where('fromid ='.$fromid)
                ->union('SELECT fromid as uid,content,time FROM chat_log WHERE toid = '.$fromid)
                ->buildSql();
            $query = Db::table($sub)
                ->alias('tmp')
                ->group('tmp.uid')
                ->order('tmp.time')
                ->buildSql();
            $info = DB::table($query)
                ->alias('t')
                ->field('uid,nickname,avatar,content,time')
                ->join('chat_user u', 't.uid=u.id', 'LEFT')
                ->select();

查询结果

{
    "status": true,
    "msg": "请求成功",
    "data": [
        {
            "uid": 2,
            "nickname": "立冬",
            "avatar": "https://ss0.bdstatic.com/70cFvHSh_Q1YnxGkpoWK1HF6hhy/it/u=3667616433,706958275&fm=26&gp=0.jpg",
            "content": "1553187007",
            "time": 1553187000
        },
        {
            "uid": 3,
            "nickname": "立春",
            "avatar": "https://ss0.bdstatic.com/70cFuHSh_Q1YnxGkpoWK1HF6hhy/it/u=3962805517,413692801&fm=26&gp=0.jpg",
            "content": "立春姐姐",
            "time": 1553187001
        },
        {
            "uid": 4,
            "nickname": "立夏",
            "avatar": "https://ss1.bdstatic.com/70cFvXSh_Q1YnxGkpoWK1HF6hhy/it/u=153106697,274998536&fm=26&gp=0.jpg",
            "content": "立夏姐姐",
            "time": 1553187002
        },
        {
            "uid": 5,
            "nickname": "小雨",
            "avatar": "https://ss0.bdstatic.com/70cFvHSh_Q1YnxGkpoWK1HF6hhy/it/u=412473880,1796535308&fm=26&gp=0.jpg",
            "content": "小雨妹妹",
            "time": 1553187022
        }
    ]
}