Selecting distinct notifications and prepare a clearer notification message

Question

I have created a notification system and it's pretty good. There are a few things that need to be worked on to make it perfect - so here I am.

The application I'm working on is a website written in PHP.

My notifications have this seen column which indicates if the notification was checked out or not. This column gets updated when an user click on the notification dropdown button which is pretty similar to Facebook's.

There are a few occasions when there are two identical notifications - Same user to same target (URL). I want notifications to be selected in an unique way and the identical ones to be ignored. I can't even understand how those identical ones are being stored in the first place.

Anyway, here's a sample output array of notifications:

Array
(
    [0] => Array
        (
            [notification_id] => 34
            [receiver_id] => 9
            [notification_issuer] => 11
            [notification_message] => Dugipo has liked your image
            [notification_target] => http://localhost/imgzer/image/v2M6S2V1J3h7
            [notification_type] => like
            [notification_time] => 1391291043
            [notification_seen] => 1
            [user_avatar] => 
            [username] => Dugipo
        )

    [1] => Array
        (
            [notification_id] => 33
            [receiver_id] => 9
            [notification_issuer] => 11
            [notification_message] => Dugipo has liked your image
            [notification_target] => http://localhost/imgzer/image/v2M6S2V1J3h7
            [notification_type] => like
            [notification_time] => 1391288815
            [notification_seen] => 1
            [user_avatar] => 
            [username] => Dugipo
        )

    [2] => Array
        (
            [notification_id] => 32
            [receiver_id] => 9
            [notification_issuer] => 11
            [notification_message] => Dugipo has liked your comment
            [notification_target] => http://localhost/imgzer/image/o3F6g8b8t6Q0#cid-64
            [notification_type] => like
            [notification_time] => 1391216243
            [notification_seen] => 1
            [user_avatar] => 
            [username] => Dugipo
        )

    [3] => Array
        (
            [notification_id] => 31
            [receiver_id] => 9
            [notification_issuer] => 11
            [notification_message] => Dugipo has liked your image
            [notification_target] => http://localhost/imgzer/image/o3F6g8b8t6Q0
            [notification_type] => like
            [notification_time] => 1391216135
            [notification_seen] => 1
            [user_avatar] => 
            [username] => Dugipo
        )

    [4] => Array
        (
            [notification_id] => 30
            [receiver_id] => 9
            [notification_issuer] => 11
            [notification_message] => Dugipo has liked your image
            [notification_target] => http://localhost/imgzer/image/o3F6g8b8t6Q0
            [notification_type] => like
            [notification_time] => 1391214642
            [notification_seen] => 1
            [user_avatar] => 
            [username] => Dugipo
        )

)

As you can see, there are 2 pairs of identical arrays and that's not what I'm after. I'm also trying to achieve the effect when two or more people liked the same thing, the messages become like: X, Y and n others have liked your image

I'm can't think on how I'd do this. Anyway, this is the function/method that collects the notifications:

public function getNotifications($seen = false)
{
    global $db;

    $seen = ($seen === true) ? 1 : 0;
    $limit = ($seen === true) ? 5 : 10;

    $sql = 'SELECT n.*, u.user_avatar, u.username FROM ' . NOTIFICATIONS_TABLE . ' n 
        LEFT JOIN ' . USERS_TABLE . " u 
            ON n.notification_issuer = u.user_id
        WHERE receiver_id = ? 
            AND notification_seen = $seen 
        ORDER BY notification_time DESC 
        LIMIT 5";   
    $query = $db->prepare($sql);
    $query->execute(array($this->id));

    $result = $query->fetchAll(PDO::FETCH_ASSOC);

    /*for ($i = 0; $i < sizeof($result); $i++)
    {
        if ($result[$i]['notification_message'] == $result[++$i]['notification_message'])
        {
            $new_message = '';

            if ($result[$i]['notification_issuer'] != $result[++$i]['notification_issuer'])
            {
                $new_message .= '';
            }
        }
    }*/

    return $result;
}

The commented for loop was meant to create that effect where the people who liked a certain thing become a single notification and the names get separated by a comma and an "and" for the n other people who liked the image.

This is the function/method that stores the notifications:

public function sendNotification($notification, $receiver, $notification_target = null, $notification_type = 'default')
{
    global $db;

    $sql = 'INSERT INTO ' . NOTIFICATIONS_TABLE . '(receiver_id, notification_issuer, notification_message, notification_target, notification_type, notification_time) 
        VALUES (?, ?, ?, ?, ?, ?)';
    $query = $db->prepare($sql);
    $query->execute(array($receiver, $this->id, $notification, $notification_target, $notification_type, time()));  
}

Example use of this method:

// Send notification / Make sure that the notification sender isn't the same user that's going to receive the notification
if ($user->get_data('user_id') != $this->image_uploader_id)
{
    $user->sendNotification(
        sprintf('<strong>%s</strong> has liked your image', $user->get_data('username')), 
        $this->image_uploader_id, 
        self::convert('name2url', $this->image_name),
        'like'
    );
}

---

Lastly, here's the schema of the notifications table:

+----------------------+---------------------+------+-----+---------+----------------+
| Field                | Type                | Null | Key | Default | Extra          |
+----------------------+---------------------+------+-----+---------+----------------+
| notification_id      | int(11) unsigned    | NO   | PRI | NULL    | auto_increment |
| receiver_id          | int(11) unsigned    | NO   |     | NULL    |                |
| notification_issuer  | int(11) unsigned    | NO   |     | NULL    |                |
| notification_message | varchar(255)        | NO   |     | NULL    |                |
| notification_target  | varchar(255)        | YES  |     | NULL    |                |
| notification_type    | varchar(55)         | NO   |     | default |                |
| notification_time    | int(11) unsigned    | NO   |     | NULL    |                |
| notification_seen    | tinyint(1) unsigned | NO   |     | 0       |                |
+----------------------+---------------------+------+-----+---------+----------------+

SQL Fiddle: http://www.sqlfiddle.com/#!2/6970b/1

---

So I'm basically after two things here:

1. Be able to select unique rows. Repeated rows get ignored
2. Achieve the effect to merge notifications that have the same target to one notification which then shows the names of the persons of the same action (ie. liking) like this: X, Y, Z and n others liked your image.

So after a SELECT from the database, I'd like to have all users that that created a notification for the same target, become a nicely readable string like I've described it above. I'm trying to avoid users that liked/disliked/commented on the same target to have a separate notification to the receiver - they should concatenate somehow.

So lets say the action is image_like. Five users image_like-d the same notification_target or now we can identify what the user will get notified about with notification_type (which will be image_like) and reference_id (we'll know it will be an image now); So now we have complete knowledge what image_id is liked based on reference_id and we also know it's an image because the notification_type says image_like.

So with this table:

id    reference    target     issuer_id    type
1     25           tg1        43           like
2     25           tg1        23           like
3     53           tg2        77           comment
4     53           tg2        23           comment
5     53           tg2        67           comment
6     53           tg2        98           dislike
7     34           tg3        65           like

From this table, this would be the results:

• User43 and User23 liked reference25 (links to tg1)
• User77, User23 and User67 commented on reference 53 (links to tg2)
• User98 disliked reference53 (links to tg2)
• User65 liked reference34 (links to tg3)

Any help would be appreciated.

Answer 1

I am sharing only the query part which solves your first and second requirements assuming that your data remains in the same structure. You can add the joins and other necessary details in your query.

SELECT myView.*, CONCAT(REPLACE(REPLACE(group_concat(SUBSTRING(notification_message,9)),'</strong>',''),SUBSTRING_INDEX(notification_message,'>',-1),''), SUBSTRING_INDEX(notification_message,'>',-1)) AS Message
FROM (SELECT DISTINCT receiver_id, notification_issuer, notification_message, notification_target, notification_type FROM imgzer_notifications
WHERE receiver_id = 9 and notification_seen = 1 ORDER BY notification_time DESC) myView GROUP BY myView.receiver_id, myView.notification_target, myView.notification_type;

I know the String functions looks complicated, but to arrive at your required output, had to play around with the Strings generated. I have modified your data set in the SQL Fiddle a little to check my output. You can do the same at:

http://www.sqlfiddle.com/#!2/70a937/49

Let me know in case there is some other criteria that is to be met or any enhancement you require in the query.