laravel框架查询回复,并且查询每个回复下面5条二级回复怎么写?

Question

回复表是一个,reply
如题,这个咋写啊,愁死了

Answer 1

你可以用 Eloquent ORM 来进行数据库查询。假设现在有两个模型，Comment 和 Reply，并且它们之间是有一对多关系。
Comment 模型：

// app/Models/Comment.php
namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Comment extends Model
{
    public function replies()
    {
        return $this->hasMany(Reply::class);
    }
}

Reply 模型：

// app/Models/Reply.php
namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Reply extends Model
{
    public function comment()
    {
        return $this->belongsTo(Comment::class);
    }
}

在 config/app.php 文件中注册模型：

'aliases' => [
    // ...
    'Comment' => App\Models\Comment::class,
    'Reply' => App\Models\Reply::class,
],

你得先写那两个模型，再用下面查询方法：

use App\Models\Comment;
use Illuminate\Support\Facades\DB;

$commentsWithTop5Replies = Comment::with(['replies' => function ($query) {
    $query->select('id', 'comment_id', 'content', 'created_at')
        ->selectSub(
            DB::raw('ROW_NUMBER() OVER (PARTITION BY comment_id ORDER BY created_at DESC)'),
            'reply_rank'
        )
        ->fromSub(function ($query) {
            $query->from('replies')->select('id', 'comment_id', 'content', 'created_at');
        }, 'replies_sub')
        ->where('reply_rank', '<=', 5);
}])->get();

这样就好了

Answer 2

select *
from (select *, row_number() over (partition by comment_id) as w
      from reply) as t
where w <= 5

支持窗口函数就用这个试试. mysql8支持.