mysql - 帖子、评论和回复的最佳可能模式

Question

我正在尝试找出帖子、评论和对评论的回复的架构，其中< strong>回复只有单级(没有回复回复)。
帖子:

1) id 2) user_id 3) contents 4) privacy

评论:

1) id 2) user_id 3) post_id 4) content

回复:

1) id 2) user_id 3) comment_id 4) content

减少查询并获得最佳结果的可能方法是什么。

Answer 1

根据上述当前数据库结构，以下是表之间的关系。

• 帖子和评论 = OneToMany
• 评论和回复 = OneToMany

You can read more about relationships in Laravel

因此您需要在各自的模型中提供以下代码。

class Post extends Eloquent
{
    /**
     * Get all comments assign to single post
     * @return \Illuminate\Database\Eloquent\Collection
     */
    public function comments()
    {
        return $this->hasMany('App\Comment','post_id');
    }
}

class Comment extends Eloquent
{
    /**
     * Get all replies assign to single comment
     * @return \Illuminate\Database\Eloquent\Collection
     */
    public function replies()
    {
        return $this->hasMany('App\Reply','comment_id ');
    }
}

现在您已获得数据库的所有帖子:

$posts = Post::all();
if($posts->count() > 0) {
    foreach($posts as $post) {
        // single post information
        $comments = $post->comments;
        if($comments->count() > 0) {
            foreach($comments as $comment) {
                // single comment information 
                 $replies = $comment->replies;
                 if($replies->count() > 0) {
                    foreach($replies as $reply) {
                        // single reply information 
                    }
                 }
            }
        }
    }
}

如果您获取单个帖子:

$post = Post::find($postId);
if($post->count() > 0) {
    $comments = $post->comments;
    if($comments->count() > 0) {
        foreach($comments as $comment) {
            // single comment information 
             $replies = $comment->replies;
             if($replies->count() > 0) {
                foreach($replies as $reply) {
                    // single reply information 
                }
             }
        }
    }
}