Geek Answers Handbook

Social Media Notification Logic

I work in some kind of social network. It is similar to twitter / instagram, there are users, they can follow each other, make messages, write comments and create sympathies.

I have implemented a notification system where the user receives information about the activities of the people they follow (what they like and comment on).

I used the mysql query for this. This is a simplified version of the request:

SELECT t.type, t.param1, t.param2, t.date_time FROM (
    SELECT 'like' as type,
    likes.user_id as param1,
    likes.user_name as param2,
    likes.date_time as date_time,
    FROM likes
    WHERE (SOME CONDITION GOES HERE)

    UNION ALL

    SELECT 'comment' as type,
    comments.user_id as param1,
    comments.user_name as param2,
    comments.date_time as date_time,
    FROM comments
    WHERE (SOME CONDITION GOES HERE)
) as t ORDER BY t.date_time ASC

Everything is working fine.

My next step is to show the user how many new notifications he has.

I have one solution for this, and I want to ask you whether it is good / effective or maybe you know how to implement it much better.

, : "total_notifications" users. , , mysql, , , "".

, , -, SELECT t.type SELECT count(*).

, , - ?

+4
2

, , , , , (bool)

, - , .

-

SELECT COUNT(*) WHERE user_id= current user id AND seen = 0.

, 1, .

, .

0

, SELECT (*) WHERE /, . , , , . , , . total_notifications 2 , "". , "", , , , , , . "status" ( "", "" ). . "", 1 , status = 'new'. , .

SELECT count(*) as t_new FROM (
    SELECT 'like' as type
    FROM likes
    WHERE (SOME CONDITION GOES HERE) AND status='New'

    UNION ALL

    SELECT 'comment' as type
    FROM comments
    WHERE (SOME CONDITION GOES HERE) AND status='New'
) as t

, , "date_time > timestamp_of_last_activity_user_seen" WHERE.

0

More articles:


All Articles