Laravel 5.5 – How to query notifications for follower with created_at timestamp?

A user can be a follower and/or a leader. Here userA and userB (both leaders) were followed by userC (follower) at different times (see followers table below for created_at timestamp).

To illustrate the issue, I will first layout the template:

userX action (year) // template to understand issue below

userC followed userA (2016)
userA added postA/notification (2017)
userC should get notification in feed, since they followed userA a year BEFORE their post

userB added postB/notification (2018)
userC followed userB (2019)
userC should NOT get notification in feed, since they followed userB a year AFTER their post

To accomplish this, I tried this query but it does not work correctly:
$user = AppUser::find(3); // follower
$leaders = $user->leaders()
                ->with(['leaderNotifications'=>function($query){
              $query->where('notifications.created_at','>','followers.created_at');
                }])
                ->get();

I think the issue is related to the created_at not being queried correctly. Here is the exact setup to see it locally:

1) DB table name/data

users

enter image description here

followers

enter image description here

notifications

enter image description here

2) Models

// User
<?php
namespace App;

use IlluminateNotificationsNotifiable;
use IlluminateFoundationAuthUser as Authenticatable;
use IlluminateNotificationsDatabaseNotification;
use IlluminateNotificationsDatabaseNotificationCollection;

class User extends Authenticatable {
    use Notifiable;

    // Pivot Table
    public function leaders() {
      return $this->belongsToMany('AppUser', ‘followers’, 'follower_id', 'leader_id')
                  ->withPivot('created_at'); // need timestamp to compare against notification timestamp
    }

    public function leaderNotifications() {
        return $this->hasMany(DatabaseNotification::class, 'leader_id')
                    ->orderBy('created_at', 'desc');
    }
}

I am trying to fetch the correct notifications for the current follower userC, meaning only the new notifications after they followed a leader, not the leader’s old notifications before they were followed by the current user.
It’s also worth noting that the final query should be able to paginate->(20) of these notifications at a time, since it will be tested with a million rows, so we need to ensure it’s efficient/scalable and can be paginated.
What would an efficient/scalable query be for this issue?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Here is how I solved it in case it helps others:

$user = AppUser::find(3); // follower
$leaders = DB::table('notifications')
             ->join('followers','followers.leader_id','=','notifications.leader_id')
             ->where('followers.follower_id', $user->id)
             ->whereRaw('notifications.created_at > followers.created_at')
             ->paginate(20);

No need for eager loading or complicating it even more. Just a simple DB query handles it!

Method 2

1) For raw query in Laravel you should using whereRaw.

2) When using with(), Laravel get data in two query , first
get result of parent query and then using result for getting with() ,
If you want to use pivot columns of first query in with closure,you can
join.

Try this :

$user = AppUser::find(3); // follower
$leaders = $user
          ->leaders()
          ->with(['leaderNotifications'=>function($query) use ($user){
              $query->whereRaw('notifications.created_at > followers.created_at')
                    ->join('followers','followers.leader_id','=','notifications.leader_id')
                    ->where('followers.follower_id',$user['id']);
                 }])
            ->get();


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x