我有以下表格:
chatUsers
chatId | userId |
---------|---------------|
1 | 1 |
1 | 2 |
2 | 1 |
2 | 3 |
chatRooms
chatId |lastMessageTime|
---------|---------------|
1 | yesterday | //for example 1000000000
2 | today | //for example 1532473344
假设我是userId 1
我想加载我所在的所有chatRooms。
按chatRooms.lastMessageTime
+订购,我想知道在同一聊天室(chatUsers.userId
)中有哪些用户
例如,在这个例子中:
[{
"chatId": 2,
"users": [1, 3]
},
{
"chatId": 1,
"users": [1, 2]
}]
有可能吗?我已经设法在没有用户选择的情况下做到了:
$qry = $db->prepare('SELECT cr.chatId FROM chatRooms AS cr INNER JOIN chatUsers AS cu ON cu.chatId = cr.chatId WHERE cu.userId = :userId
ORDER BY cr.lastMessageTime DESC, cr.chatId DESC');
$qry->bindParam(':userId', $userId, PDO::PARAM_INT);
$qry->execute();
$chatRooms = $qry->fetchAll(PDO::FETCH_ASSOC);
但是我不能在select中进行子选择。
发布于 2018-07-26 05:44:56
$qry = $db->prepare('SELECT cr.chatId, GROUP_CONCAT(cu.userId) AS users FROM chatRooms AS cr INNER JOIN chatUsers AS cu ON cu.chatId = cr.chatId WHERE cu.userId = :userId ORDER BY cr.lastMessageTime DESC, cr.chatId DESC GROUP BY chatId');
$qry->bindParam(':userId', $userId, PDO::PARAM_INT);
$qry->execute();
$chatRooms = $qry->fetchAll(PDO::FETCH_ASSOC);
通过这种方式,您将获得逗号分隔的字符串形式的用户。
https://stackoverflow.com/questions/51528042
复制相似问题