public static function getNotifications($user_id)
{
//$query = "select noti.*, ng.sent_at,ng.is_read from (SELECT noti.notifi_id,noti.objectID,max(noti.updated_at) as 'sent_at', min(noti.is_read) as 'is_read', count(distinct(noti.sender_id)) as 'sender_count', case when count(DISTINCT(noti.sender_id)) = 1 then usr.name when count(DISTINCT(noti.sender_id)) = 2 then GROUP_CONCAT(usr.name SEPARATOR ' and ') when count(DISTINCT(noti.sender_id)) > 2 then CONCAT(count(distinct(noti.sender_id)), ' users' ) end as sender_string FROM `notifications` noti join users usr on noti.sender_id = usr.user_id where usr.user_id = 11 group by noti.objectID) as ng join notifications noti on noti.notifi_id = ng.notifi_id order by ng.is_read asc,ng.sent_at desc";
$notificationsGroup = DB::table('notifications')->select('notifications.notifi_id', 'notifications.objectID', 'notifications.objectType', 'notifications.type', DB::raw('max(notifications.updated_at) as updated_at'), DB::raw('min(notifications.is_read) as is_read'), DB::raw('count(distinct(notifications.sender_id)) as sender_count'), DB::raw("case\n when count(DISTINCT(notifications.sender_id)) = 1 then users.name\n when count(DISTINCT(notifications.sender_id)) = 2 then GROUP_CONCAT(users.name SEPARATOR ' and ')\n when count(DISTINCT(notifications.sender_id)) > 2 then CONCAT(count(distinct(notifications.sender_id)), ' users' )\n end as sender_string"))->join('users', 'users.user_id', '=', 'notifications.sender_id')->whereRaw('notifications.receiver_id = ' . $user_id)->groupBy('notifications.type', 'notifications.objectID');
$notifications = $notificationsGroup->get();
foreach ($notifications as $noti) {
$result[] = strval(Notification::convertToNotification((array) $noti));
}
return new Collection($result);
}
