How to group notifications

Asked

Viewed 62 times

1

I want to make a system of notifications that come together when they’re the same type, like the ones on Facebook. For now I have the table with the following columns:

user_id (person receiving the notification),

icon (Usually another user’s profile photo),

Description (the description of the notification),

Registry (registration date),

Seen (a boolean who checks if the person has already viewed)

How I create notifications? Well, it happens when the action is executed. For example, I click the follow button So-and-so, within the function I record the notification, so:

user_id: User id I followed,

icon: My profile picture,

Description: Insanity started following you!,

Registry: CURRENT_TIME(),

Seen: 0

I have two main problems:

  • If I keep clicking the follow button several times, it will send multiple notifications.
  • The cluster problem: If more than one person follows, let’s assume that 1000 people do this, 1000 notifications will appear. How to group like Facebook, example: "Geraldo, Thiago and more 998 people started following you!".
php mysql sql
  • 1

    I think the logic of face book is if you have more than x notifications it instead of displaying all it will display the last and how many other notifications you have of the same type

  • This is kind of complex. But you will need to change the db structure: create a table with the activity types and then relate them to your current table (instead of the Description field). This will help the server to compare records: it is much faster to check two ints than two strings. (see about database normalization)

1 answer

0

Use the clause GROUP BY, which is used to group (or aggregate) the rows of the table according to a criterion chosen by the user, and a group function can then be applied to each of the groups. Using GROUP BY cannot select individual lines.

The example below determines the average salary of each function (JOB):

SELECT job, AVG(sal)
FROM emp
GROUP BY job;


JOB       AVG(SAL)               
--------- ----------
CLERK     1037,5                 
SALESMAN  1400                   
PRESIDENT 5000                   
MANAGER   2758,3 
ANALYST   3000                   

5 rows selected

Browser other questions tagged php mysql sql

You are not signed in. Login or sign up in order to post.