聊天功能获取聊天记录列表并展示最新一条聊天记录

聊天功能获取聊天记录列表并展示最新一条聊天记录

记录一次项目中聊天功能，需要获取好友聊天记录列表并且展示最新一条聊天记录，同时获取新消息的数目的sql语句。

  SELECT count(read_status = 0 or null) AS newMessage,receiver,content,send_time,nickname,avatar_path FROM (SELECT receiver_id as receiver,content,1 as read_status,send_time,type FROM message WHERE (sender_id = ?) AND (receiver_id <> ?) UNION SELECT sender_id as receiver,content,read_status,send_time,type FROM message WHERE (sender_id <> ?) AND (receiver_id = ?) ORDER BY send_time DESC) as newTable INNER JOIN user on receiver = user.id GROUP BY receiver ORDER BY send_time DESC LIMIT ?,?

数据库表结构
id(自增主键)
content(消息内容)
type(图片类型，有文本消息，图片消息)
read_status(消息是否已读)
sender_id(发送消息的用户id)
receiver_id(接收消息的用户id)
send_time(消息的发送时间)

效果图如下
其中打码的为用户的头像和昵称