The fiddle and the query is here so it's easier to find, read on for the question itself.
SQL Fiddle if you guys wanna mess around with it
I'm unable to reproduce the issue using the fiddle.
Here is the query you can use in the fiddle
SELECT n.*, ns.notification_id AS is_read FROM notifications n
LEFT OUTER JOIN notification_status ns
ON n.id = ns.notification_id
LEFT JOIN notification_user_role nur
ON n.id = nur.notification_id
WHERE
(
n.esb_consultant_id = 19291
OR
n.esb_consultant_id = 'role'
)
AND nur.user_role_id = 'pl_sso_regional_vice_president'
AND n.creation_date <= NOW()
AND n.expiration_date >= NOW()
ORDER BY n.creation_date DESC, (is_read IS NULL) DESC, n.priority ASC
LIMIT 0, 10
I've also placed it lower in this post, but here it's easier to catch the eyes.
I'll try to keep this as brief as possible.
I'm working on a notification system. I have 3 tables described below.
I'm trying to get notifications with a LIMIT
of 10, paginated, 10 per page(so an OFFSET
of 10). I'm using ajax to load the next 10.
They are to be ordered by priority(from 1 to 6, 1 being displayed first, while 6th being displayed last).
All unread notifications must be displayed first(priority still applies), while read notifications must be displayed last(priority still applies).
Notifications are per role. A user can have several roles(thus the need for another table).
The notification_status
table described below is used to keep track of which notifications are read.
Which ever notification is NOT in the notification_status
table is NOT read. This is very important. I didn't make this decision. I just have to live with it.
To put it into the big picture lets have an example:
Assume we have 14 notifications:
5 of them will be priority 1, unread.
4 of them will be priority > 1, unread.
3 of them will be priority 1, read.
2 of them will be priority > 1, read.
The expected display order is the following.
5 unread priority 1
4 unread priority > 1
1 read priority 1
ajax starts here since we have 10 per page
2 read priority 1
2 read priority > 1
The table structure is as follows.
notifications
+-------------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| type_id | int(10) unsigned | NO | | NULL | |
| sticky | int(10) unsigned | NO | | NULL | |
| priority | int(10) unsigned | NO | | NULL | |
| esb_consultant_id | varchar(40) | NO | | | |
| message_id | varchar(100) | NO | | | |
| esb_params | varchar(255) | YES | | | |
| creation_date | datetime | YES | | NULL | |
| expiration_date | datetime | YES | | NULL | |
+-------------------+------------------+------+-----+---------+----------------+
notification_user_role
+-----------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+------------------+------+-----+---------+-------+
| user_role_id | varchar(150) | NO | | | |
| notification_id | int(10) unsigned | NO | MUL | NULL | |
+-----------------+------------------+------+-----+---------+-------+
notification_status
+-------------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+------------------+------+-----+---------+-------+
| esb_consultant_id | varchar(20) | NO | | | |
| notification_id | int(10) unsigned | NO | MUL | NULL | |
+-------------------+------------------+------+-----+---------+-------+
The query I'm using to retrieve the results:
SELECT n.*, ns.notification_id AS is_read FROM notifications n
LEFT OUTER JOIN notification_status ns
ON n.id = ns.notification_id
LEFT JOIN notification_user_role nur
ON n.id = nur.notification_id
WHERE
(
n.esb_consultant_id = :consultant_id
OR
n.esb_consultant_id = :role_all
)
AND nur.user_role_id = :consultant_role
AND n.creation_date <= NOW()
AND n.expiration_date >= NOW()
ORDER BY n.creation_date DESC, (is_read IS NULL) DESC, n.priority ASC
LIMIT $offset, $limit
$offset
is the page times 10 - so if the page is 0(first page) the offset is 0, if the page is 1(first ajax call) the offset is 10 and so on
$limit
is the limit, it's always 10.
:consultant_id
is the user id - unique
:role_all
is a simple string all
. It is used for when certain notifications are for all roles(such as a birthday notification). All users have this notification, regardless of role since all of them have a birthday.
The problem:
Whenever I'm doing an ajax call I get certain notifications that are duplicated. I'll just post a screenshot of it since it's easier than drawing it.
Do note that the ajax itself is just part of the way I'm retrieving the results but is not responsible for the duplicates themselvs, I'm absolutely sure. It's not a display issue either, I've double and triple checked that.
What I've noticed is that if I were to remove this part
ORDER BY n.creation_date DESC, (is_read IS NULL) DESC, n.priority ASC
from the query. It works fine. No duplicates.
The dump of the query above, with limit removed and order by removed:
Sorry for the image but it's easier.
I'm using PHP to query the database.
public function all($consultant_id, $consultant_role, $offset = 0) {
$limit = 10;
$offset = $offset * 10;
$query = <<<SQL
SELECT n.*, ns.notification_id AS is_read FROM notifications n
LEFT OUTER JOIN notification_status ns
ON n.id = ns.notification_id
LEFT JOIN notification_user_role nur
ON n.id = nur.notification_id
WHERE
(
n.esb_consultant_id = :consultant_id
OR
n.esb_consultant_id = :role_all
)
AND nur.user_role_id = :consultant_role
AND n.creation_date <= NOW()
AND n.expiration_date >= NOW()
ORDER BY n.creation_date DESC, (is_read IS NULL) DESC, n.priority ASC
LIMIT $offset, $limit
SQL;
$return = $this->connection
->query($query
, [
':consultant_role' => $consultant_role,
':consultant_id' => $consultant_id,
':role_all' => NotificationStatus::PL_N_ALL,
]
)->fetchAll(\PDO::FETCH_ASSOC);
foreach($return as $item) { // this is added simply for display purposes
echo $item['id'] . '<br>';
}
return $return;
}
The above is a copy+paste of the code used to retrieve the results. The function simply returns the results for displaying, no other magic going around.
The foreach
is added to simply display the results in the browser.
Here is the image of the output. Notification 10 is duplicated.
Here is the exact same code only with
ORDER BY n.creation_date DESC, (is_read IS NULL) DESC, n.priority ASC
removed. Limit and offset still apply here.
I'm not that good at mysql or sql in general and I'm not sure where the problem itself lies.
Any pointing in the right direction is greatly appreciated. Even workarounds or "hacks" I'm fine with.
mysql> SELECT * FROM notification_status WHERE notification_id = 10;
Empty set (0.00 sec)
mysql> SELECT * FROM notification_user_role WHERE notification_id = 10;
+--------------------------------+-----------------+
| user_role_id | notification_id |
+--------------------------------+-----------------+
| pl_sso_regional_vice_president | 10 |
+--------------------------------+-----------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM notifications WHERE id = 10;
+----+---------+--------+----------+-------------------+-------------------------------------+------------+---------------------+---------------------+
| id | type_id | sticky | priority | esb_consultant_id | message_id | esb_params | creation_date | expiration_date |
+----+---------+--------+----------+-------------------+-------------------------------------+------------+---------------------+---------------------+
| 10 | 2 | 0 | 4 | role | pl_n_325f1676e8a263c86432edc7b9f09c | NULL | 2018-02-01 00:00:00 | 2018-02-28 00:00:00 |
+----+---------+--------+----------+-------------------+-------------------------------------+------------+---------------------+---------------------+
1 row in set (0.00 sec)
mysql>