时间戳

首页 > 解决方案 > LARAVEL：返回所有帖子的最后评论基于当前登录用户

sql - LARAVEL：返回所有帖子的最后评论基于当前登录用户

问题描述

查询 GET all POST 的 USER

$result = DB::table('tasks as t')->select('*' , 't.created_by as task_creator' , 't.task_id as task_id' , 't.created_at as task_created')
                ->join('task_assign as ta', 'ta.task_id', '=', 't.task_id')
                ->join('users as u' , 'u.id' , '=' , 't.created_by')
                ->join('task_categories as tcc' , 'tcc.category_id' , '=' , 't.category_id')
                ->leftJoin('task_comments as tc' , 'tc.task_id' , '=' , 't.task_id')
                ->where("ta.user_id" ,  Auth::user()->id)
                ->where("t.status" , 'A')
                ->where("t.task_status" , '!=' , 'D')
                ->orderBy('tc.date_added' ,'DESC')
                ->orderBy('t.task_id' ,'DESC')
                ->get();

获取用户最后评论的所有帖子

 # SET STATUS BASED ON USERS
$c = [];
$group = [];
foreach ($newArray as $key => $row) {
   # check if user is the last comment
   $check = DB::table('task_comments as tc')->select('*')
                  ->where("tc.task_id" , $row->task_id)
                  ->orderBy('tc.task_comments_id' ,'DESC')
                  ->get()->first();

  if (!empty($check)) {
      if ($check->user_id == Auth::user()->id) {
          $c[] = $check->user_id;
               $group['I'][$key] = $row;  
      }
   }
}

如果发布最后一条评论是当前登录用户，他们是我可以删除循环并获取所有帖子的一种方式。

标签： sqllaravel

解决方案

我使用的是 QueryBuilder 宏。将此脚本添加到您的app/Providers/AppServiceProvider.php

    /**
     * Register any application services.
     *
     * @return void
     */
    public function register()
    {
        Builder::macro('whereLatest', function ($table, $parentRelatedColumn) {
            return $this->where($table . '.id', function ($sub) use ($table, $parentRelatedColumn) {
                $sub->select('id')
                    ->from($table . ' AS t1')
                    ->whereColumn('t1.' . $parentRelatedColumn, $table . '.' . $parentRelatedColumn)
                    ->latest()
                    ->take(1);
            });
        });
    }

比你可以使用：

Auth::user()->whereHas('posts', function($q) {
    $q->whereLatest('posts_table_name', 'user_id');
});

推荐阅读