public function getUserConversations($user_id)
{
$return = array();
$conversations = new Collection();
$convs = DB::select('
SELECT msg.conv_id as conv_id, msg.created_at, msg.id msgId, msg.content, mst.status, mst.self, us.' . $this->usersTableKey . ' userId
FROM ' . $this->tablePrefix . 'messages msg
INNER JOIN (
SELECT MAX(created_at) created_at
FROM ' . $this->tablePrefix . 'messages
GROUP BY conv_id
) m2 ON msg.created_at = m2.created_at
INNER JOIN ' . $this->tablePrefix . 'messages_status mst ON msg.id=mst.msg_id
INNER JOIN ' . $this->usersTable . ' us ON msg.sender_id=us.' . $this->usersTableKey . '
WHERE mst.user_id = ? AND mst.status NOT IN (?, ?)
ORDER BY msg.created_at DESC
', array($user_id, self::DELETED, self::ARCHIVED));
$convsIds = array();
foreach ($convs as $conv) {
//this is for the query later
$convsIds[] = $conv->conv_id;
//this is for the return result
$conv->users = array();
$return[$conv->conv_id] = $conv;
$conversation = new Conversation();
$conversation->setId($conv->conv_id);
$message = new Message();
$message->setId($conv->msgId);
$message->setCreated($conv->created_at);
$message->setContent($conv->content);
$message->setStatus($conv->status);
$message->setSelf($conv->self);
$message->setSender($conv->userId);
$conversation->addMessage($message);
$conversations[$conversation->getId()] = $conversation;
}
$convsIds = implode(',', $convsIds);
if ($convsIds != '') {
$usersInConvs = DB::select('
SELECT cu.conv_id, us.' . $this->usersTableKey . '
FROM ' . $this->tablePrefix . 'conv_users cu
INNER JOIN ' . $this->usersTable . ' us
ON cu.user_id=us.' . $this->usersTableKey . '
WHERE cu.conv_id IN(' . $convsIds . ')
', array());
foreach ($usersInConvs as $usersInConv) {
if ($user_id != $usersInConv->id) {
$user = new \stdClass();
$user->id = $usersInConv->id;
//this is for the return result
$return[$usersInConv->conv_id]->users[$user->id] = $user;
}
$conversations[$usersInConv->conv_id]->addParticipant($usersInConv->id);
}
}
return $conversations;
}
/**
* @param $user_id
* @return Collection[Conversation]
*/
public function getUserConversations($user_id)
{
$return = [];
$conversations = new Collection();
$convs = $this->tbmRepo->getConversations($user_id);
$convsIds = [];
foreach ($convs as $conv) {
//this is for the query later
$convsIds[] = $conv->conv_id;
//this is for the return result
$conv->users = [];
$return[$conv->conv_id] = $conv;
$conversation = new Conversation();
$conversation->setId($conv->conv_id);
$message = new Message();
$message->setId($conv->msgId);
$message->setCreated($conv->created_at);
$message->setContent($conv->content);
$message->setStatus($conv->status);
$message->setSelf($conv->self);
$message->setSender($conv->userId);
$conversation->addMessage($message);
$conversations[$conversation->getId()] = $conversation;
}
$convsIds = implode(',', $convsIds);
if ($convsIds != '') {
$usersInConvs = $this->tbmRepo->getUsersInConvs($convsIds);
foreach ($usersInConvs as $usersInConv) {
if ($user_id != $usersInConv->id) {
$user = new \stdClass();
$user->id = $usersInConv->id;
//this is for the return result
$return[$usersInConv->conv_id]->users[$user->id] = $user;
}
$conversations[$usersInConv->conv_id]->addParticipant($usersInConv->id);
}
}
return $conversations;
}