0

I use a notifications table and a subnotifications table, and I am also using queues so it runs in the background when a user posts something. When a user has 10 followers and they create a post, the notifications table gets a single entry which includes the post data for the notification, and the subnotifications table gets 10 entries (one subnotification per follower, each referring to the id of the notification, so we don't have to repeat the notification data 10 times, with a read_at to know if it was read or not by that follower).

This is quick and works great without any issues. However, when testing with 1 million followers, it takes about ~6 hours to insert the subnotifications for one post! This of course is not acceptable, as it is takes too long to insert 1 million subnotifications, one per follower. Imagine that same user posts 10 posts, that'll be like ~60 hours of inserting and 10 million subnotification rows.

I just want followers to know there is a new post if they didn't read it yet. Is there a better, more efficient way that scales?

UPDATE: Stuck with current approach see below...

If a follower $user has 100 leaders they follow (which they followed at different created_at timestamps of course in the followers table), what would the correct query be to know about leader new posts from the time the follower followed each leader? I get stuck at created_at with this pseudo code:

// Assume `leader_id` is a column in the notifications table
DB::table('notifications')
  ->whereIn('leader_id', $leaderIds)
  ->where(`created_at`, '>', $whatTimestampsGoHere)
  ->paginate(20);

There is 100 different timestamps and I am stuck on how to solve this one correctly and efficiently. Any ideas?

Wonka
  • 8,244
  • 21
  • 73
  • 121
  • 1
    Why not only insert into the subnotifications when the users read them? Fetch the notifications to show from the `notifications` table where there is no entry on the `subnotifications` with an user_id and that notification id, and once user see, add the entry to the `subnotifications` – Sérgio Reis Mar 29 '18 at 12:40
  • Thanks! That sounds like a great alternative, but the issue I think would be if a user has 3 followers today and creates a post, we can do the check if they exist or not in the `subnotifications` with the `user_id`/`notification_id` row to know if it's read or not. But if they get 7 new followers tomorrow, the new 7 shouldn't be notified, since they started following after the post. Would I have to scan the followers table to compare their following timestamp < notification timestamp, and ignore the new followers after the post? Is this efficient or is there a better way? – Wonka Mar 29 '18 at 13:54
  • 1
    Hmm that sounds more reasonable, that way you actually end up saving alot of insertions to the database and keep the functionality as is – Sérgio Reis Mar 29 '18 at 14:21
  • Can't really understand that question – Sérgio Reis Apr 09 '18 at 20:05
  • If you followed 100 people, the followers table will keep record of your `follower_id`, the `leader_id`, and the `created_at` timestamp. When you look at your notifications, you should only see the new content your leaders posted. It's easy to get that if you only had 1 leader, since you compare the timestamp you followed them with the timestamp of the notification. But since you have 100 leaders, you don't want to get their old posts before you followed them, only posts after you followed them, but you don't have 1 timestamp only to compare, you have 100 timestamps, so that's the issue atm.. – Wonka Apr 09 '18 at 20:12
  • Hmmm can't you load your "leaders" and eager load their content? `$user->leaders()->with('notifications')` leaders being the users you follow, and notifications the content you want. (1user hasMany followers, 1 follower(user I guess) has many notifications) loading the content, you may also filter while eager loading the notifications to be after the created_at timestamp – Sérgio Reis Apr 10 '18 at 00:17
  • The issue seems that `$user->leaders()->with('notifications')` does not get the notifications for the follower, since it assumes `$user` is the notifiable, even though the `notifiable_id` in db is `0` (to denote it's for all followers) and not the `$user` id. The only real identifier is the `leader_id` column on the `notifications` table, which is the `leader_id` (user id) whenever there is a `0` for notifiable. How can we have it use the `leader_id` value then, and eager load the leaders for their followed timestamps to be able to compare timestamps? I think the query may have to change... – Wonka Apr 10 '18 at 16:49
  • If the relationships are set correctly, you can do that query, https://laravel.com/docs/5.5/eloquent-relationships#one-to-many all of the relationship functions accept extra parameters as column/tables names. – Sérgio Reis Apr 10 '18 at 16:51

1 Answers1

1

As stated in the comments, you can reduce the inserts, if you only insert to the child table i.e. subnotifications when the user reads it and not on creating it on the notification creation, which avoids that issue. When trying to check if user has seen the notification, just check if they exist in subnotifications for the user in question and the notification.

Also as said, when fetching notifications to show to users fetch them from notifications but limit the notifications to the notifications created after the user started following so that new users don't get flooded with notifications.

Sérgio Reis
  • 2,483
  • 2
  • 19
  • 32
  • 1
    Hey Sergio, I created a new question here: https://stackoverflow.com/questions/49764452 to enable recreating the issue locally, since I can't get the query to work properly with the `created_at` timestamps comparison, and I didn't want to keep adding to this question. Thanks for the direction so far man, I really appreciate your help :) – Wonka Apr 11 '18 at 00:06