mysql - 如何使用 laravel fluent query builder 获取行数

Question

如何使用 Laravel fluent query builder 获取行数

我附上了用于过滤其他数据的查询，我还需要获取行数。

这是数据库表:-

  $results = DB::table('newsfeed_posts')
    ->select('newsfeed_posts.*', 'users.first_name as posted_user_first_name', 'users.last_name as posted_user_last_name', 'users.profile_image as posted_user_profile_image','proid.profile_image as posted_receiver_profile_image', 'timeline.first_name as post_receiver_first_name', 'timeline.last_name as post_receiver_last_name', 'timeline.office_branch_id as post_receiver_office_id')
    ->leftJoin('users', 'users.id', 'newsfeed_posts.post_sender_id')
    ->leftJoin('users as timeline', 'timeline.id', 'newsfeed_posts.post_receiver_id') 
    ->leftJoin('users as proid', 'proid.id', 'newsfeed_posts.post_receiver_id') 
    ->where('newsfeed_posts.deleted_status', '0')
    // ->where('newsfeed_posts.post_sender_id', Auth::user()->id)
    ->where('newsfeed_posts.post_receiver_id', Auth::user()->id)
    ->groupBy('newsfeed_posts.id')
    ->orderBy('newsfeed_posts.id', 'DESC')
    ->get();

    return $results;

Answer 1

使用纯 Laravel 查询构建器，您可能必须发出两个单独的查询:

$count = DB::table('newsfeed_posts')
    ->select('newsfeed_posts.*', 'users.first_name as posted_user_first_name', 'users.last_name as posted_user_last_name', 'users.profile_image as posted_user_profile_image','proid.profile_image as posted_receiver_profile_image', 'timeline.first_name as post_receiver_first_name', 'timeline.last_name as post_receiver_last_name', 'timeline.office_branch_id as post_receiver_office_id')
    ->leftJoin('users', 'users.id', 'newsfeed_posts.post_sender_id')
    ->leftJoin('users as timeline', 'timeline.id', 'newsfeed_posts.post_receiver_id') 
    ->leftJoin('users as proid', 'proid.id', 'newsfeed_posts.post_receiver_id') 
    ->where('newsfeed_posts.deleted_status', '0')
    ->where('newsfeed_posts.post_receiver_id', Auth::user()->id)
    ->groupBy('newsfeed_posts.id')
    ->count();

然后，使用您当前的查询生成您想要的结果集。

如果您在后台使用 MySQL 8+，那么还有一个替代方案。我们可以尝试使用 COUNT() 作为分析函数，只使用一个查询:

$results = DB::table('newsfeed_posts')
    ->selectRaw('COUNT(*) OVER () AS cnt, newsfeed_posts.*', 'users.first_name as posted_user_first_name', 'users.last_name as posted_user_last_name', 'users.profile_image as posted_user_profile_image','proid.profile_image as posted_receiver_profile_image', 'timeline.first_name as post_receiver_first_name', 'timeline.last_name as post_receiver_last_name', 'timeline.office_branch_id as post_receiver_office_id')
    ->leftJoin('users', 'users.id', 'newsfeed_posts.post_sender_id')
    ->leftJoin('users as timeline', 'timeline.id', 'newsfeed_posts.post_receiver_id') 
    ->leftJoin('users as proid', 'proid.id', 'newsfeed_posts.post_receiver_id') 
    ->where('newsfeed_posts.deleted_status', '0')
    ->where('newsfeed_posts.post_receiver_id', Auth::user()->id)
    ->groupBy('newsfeed_posts.id')
    ->orderBy('newsfeed_posts.id', 'DESC')
    ->get();

整个结果集的计数(在 GROUP BY 聚合之后)然后可以使用别名 cnt 获得。