2023年6月23日发(作者：)

ThinkPHP5：评论、回复功能设计，连表查询优化⽬录0x00 评论表设计0x01 控制器

* @return:

*/ public function save() { $data = input('post.', [], 'htmlspecialchars'); //后端效验 $cmt_validate = validate('CmtValidate'); if (!$cmt_validate->check($data)) { return show(config('_code'), $cmt_validate->getError(), [], 403); } //查询数据库中是否有该⽂章 if (!model('News')->where(['id' => $data['news_id']])->find()) { return show(config('_code'), "⽂章不存在", [], 404); } $data['user_id'] = $this->user->id; try { $commentId = model('Comment')->add($data); //回写 if ($commentId && model('News')->where(['id' => $data['news_id']])->setInc('comment_count')) { return show(config('s_code'), '评论成功!', [], 202); } else { return show(config('_code'), '评论失败!', [], 500); return show(config('_code'), '评论失败!', [], 500); } } catch (Exception $e) { Log::write($e->getMessage()); return show('_code', '评论失败!', [], 500); } } /** * @description: 评论列表API v1.0 * @method GET * @param {type}

* @return:

*/ // public function read(){ // $news_id = input('',0,'intval'); // if(empty($news_id)){ // return show(config('_code'),'请输⼊⽂章id',[],404); // } // $param['news_id']= $news_id; // $count = model('Comment')->getNormalCommentsCountByCondition($param); // $ret = model('Comment')->getNormalCommentsByCondition($param,0,20); // if($ret){ // $result = [ // 'total'=>$count, // 'list'=>$ret // ]; // return show(config('s_code'),'获取评论成功!',$result,200); // }else{ // return show(config('_code'),'获取评论失败!',[],500); // } // } /** * @description:评论列表API v2.0 * @method GET

* @param {type}

* @return:

*/ public function read() { $news_id = input('', 0, 'intval'); if (empty($news_id)) { return show(config('_code'), '请输⼊⽂章id', [], 404); } $param['news_id'] = $news_id; $count = model('Comment')->getCountByCondition($param); $comments = model('Comment')->getListsByCondition($param,0,20); if($comments){ foreach($comments as $comment){ $userIds[]=$comment['user_id']; if($comment['to_user_id']){ $userIds[] = $comment['to_user_id']; } } $userIds = array_unique($userIds); } $userIds = model('User')->getUsersByUserId($userIds); if(empty($userIds)){ $userIdNames = []; }else{ foreach($userIds as $userId){ $userIdNames[$userId->id] = $userId; } } foreach($comments as $comment){ $resultDatas[] = [ $resultDatas[] = [ 'id'=>$comment->id, 'user_id'=>$comment->user_id, 'to_user_id'=>$comment->to_user_id, 'content' => $comment->content, 'username'=>!empty($userIdNames[$comment->user_id])?$userIdNames[$comment->user_id]->username:'', 'tousername'=>!empty($userIdNames[$comment->to_user_id])?$userIdNames[$comment->to_user_id]->username:'', 'username_image'=>!empty($userIdNames[$comment->user_id])?$userIdNames[$comment->user_id]->image:'', 'tousername_image'=>!empty($userIdNames[$comment->to_user_id])?$userIdNames[$comment->to_user_id]->image:'', 'create_time'=>$comment->create_time ]; } return show(config('s_code'),'获取⽤户列表成功!',$resultDatas,200); }}0x02 Model层

namespace appcommonmodel;use appcommonmodelBase;class Comment extends Base{ /** * @description: 通过条件获取评论的数量 * @param {type}

* @return:

*/

public function getNormalCommentsCountByCondition($param = []){ $count = $this ->alias('c') // ->join('ent_user u','_id = ') ->where(['_id'=>$param['news_id']]) ->count(); return $count; } /** * @description: 通过条件获取列表数据 * @param {type}

* @return:

*/

public function getNormalCommentsByCondition($param=[],$from=0,$size=5){ $ret = $this ->alias('c') ->join('ent_user u','_id = ') ->where(['_id'=>$param['news_id']]) ->limit($from,$size) ->order(['_time'=>'DESC']) ->select(); return $ret; } public function getCountByCondition($param=[]){ return $this->where($param) ->field('id') ->count(); } /** * @description: 获取评论列表 * @param {type}

* @return:

*/

public function getListsByCondition($param=[],$from=0,$size=5){ return $this->where($param) ->field('*') ->limit($from,$size) ->order(['create_time'=>'desc']) ->select(); }}0x03 关联查询优化连表查询的劣势：1.多个表同时读会导致多个表同时被锁，多个表不能写⼊。 并且这种锁机制会拉慢性能，导致连表查询 ⽐ 同样的多次单表查询慢很多。2⼤量的关联查询会导致集中式的数据库架构很难向分布式架构转换优化思路：（多次单表查询+拼接数据）先将主表信息查询出来，然后将userid toUserId 弄成⼀个数组 去重然后⽤这个数组 去 副表中查 对应的信息（in）以id为键，组成⼀个数组然后将两个数组 中有⽤的信息拼成⼀个数组返回代码： /** * @description:评论列表API v2.0 * @method GET

* @param {type}

* @return:

*/ public function read() { $news_id = input('', 0, 'intval'); if (empty($news_id)) { return show(config('_code'), '请输⼊⽂章id', [], 404); } $param['news_id'] = $news_id; $count = model('Comment')->getCountByCondition($param); $comments = model('Comment')->getListsByCondition($param,0,20); if($comments){ foreach($comments as $comment){ $userIds[]=$comment['user_id']; if($comment['to_user_id']){ $userIds[] = $comment['to_user_id']; } } $userIds = array_unique($userIds); } $userIds = model('User')->getUsersByUserId($userIds); if(empty($userIds)){ $userIdNames = []; }else{ foreach($userIds as $userId){ $userIdNames[$userId->id] = $userId; } } foreach($comments as $comment){ $resultDatas[] = [ 'id'=>$comment->id, 'user_id'=>$comment->user_id, 'to_user_id'=>$comment->to_user_id, 'content' => $comment->content, 'username'=>!empty($userIdNames[$comment->user_id])?$userIdNames[$comment->user_id]->username:'', 'tousername'=>!empty($userIdNames[$comment->to_user_id])?$userIdNames[$comment->to_user_id]->username:'', 'username_image'=>!empty($userIdNames[$comment->user_id])?$userIdNames[$comment->user_id]->image:'', 'tousername_image'=>!empty($userIdNames[$comment->to_user_id])?$userIdNames[$comment->to_user_id]->image:'', 'create_time'=>$comment->create_time ]; } return show(config('s_code'),'获取⽤户列表成功!',$resultDatas,200); }User表：

namespace appcommonmodel;use thinkmodel;class User extends Model{ protected $autoWriteTimestamp = true; public function getUsersByUserId($userIds=[]){ $data = [ 'id'=>['in',implode(',',$userIds)] ]; $order = [ 'create_time'=>'DESC' ]; return $this->field(['id','username','image']) ->where($data) ->order($order) ->select(); }}