Laravel 5.5-如何使用created_at时间戳查询关注者的通知? [英] Laravel 5.5 - How to query notifications for follower with created_at timestamp?

问题描述

用户可以是关注者和/或领导者.在不同的时间，userA和userB(都是领导者)之后是userC(跟随者)(有关时间戳，请参见下面的followers表).

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 = App\User::find(3); // follower
$leaders = $user->leaders()
                ->with(['leaderNotifications'=>function($query){
              $query->where('notifications.created_at','>','followers.created_at');
                }])
                ->get();

我认为该问题与created_at未被正确查询有关.这是在本地查看的确切设置:

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

1)数据库表名称/数据

users

followers

notifications

2)型号

// User
<?php
namespace App;

use Illuminate\Notifications\Notifiable;
use Illuminate\Foundation\Auth\User as Authenticatable;
use Illuminate\Notifications\DatabaseNotification;
use Illuminate\Notifications\DatabaseNotificationCollection;

class User extends Authenticatable {
    use Notifiable;

    // Pivot Table
    public function leaders() {
      return $this->belongsToMany('App\User', ‘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');
    }
}

我正在尝试获取当前关注者userC的正确通知，这意味着仅在他们跟随领导者之后的新通知 ，而不是领导者之前的旧通知他们由当前用户关注. 还值得注意的是，最终查询应该一次能够对这些通知进行一次paginate->(20)，因为它将经过一百万行的测试，因此我们需要确保其有效/可扩展并且可以分页. 对于此问题，有效/可扩展的查询是什么?

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?

推荐答案

在解决其他问题的情况下，这是我解决的方法:

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

$user = App\User::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);

无需急于加载或使其更加复杂.只需一个简单的DB查询即可处理！

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

这篇关于Laravel 5.5-如何使用created_at时间戳查询关注者的通知?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！