Subselecting array in mysql

I have the following tables:

chatUsers

chatId | userId |
---------|---------------|
1 | 1 |
1 | 2 |
2 | 1 |
2 | 3 |


chatRooms

chatId |lastMessageTime|
---------|---------------|
1 | yesterday | //for example 1000000000
2 | today | //for example 1532473344


Let's say I am userId 1

userId 1

I would like to load all the chatRooms that I am in.

Ordered by chatRooms.lastMessageTime + I'd like to know what users are in the same chat room (chatUsers.userId)

chatRooms.lastMessageTime

chatUsers.userId

So for example in this case:

[{
"chatId": 2,
"users": [1, 3]
},
{
"chatId": 1,
"users": [1, 2]
}]


Is it possible? I've managed to make it without the users selection:

$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);


But I am unable to make a subselect inside the select..

1 Answer
1

$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);


In this way you'll get users in a comma-separated string.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.