需求描述
- 假设有个操作后台，可以获取某个分类下的所有数据列表
- 针对当前这个分类的列表，可以进行如下操作：置顶、锁定在当前位置、拖动排序（锁定的不可改变排序、如果是置顶的，必须同为置顶的数据）
实现逻辑
每页动态算出offset,然后替换,分页就实现了锁定
逻辑： 假设每页50条数据，查出当页的lock值的数据，比如第一页就是lock为1-50的，第二页就是lock为51-100的，同时查出前面几页所有的lock数据的数量，这个数量就是当前页需要往前偏移的offset，根据这个offset获取的列表，进行当前页有lock的进行替换。这样满足任意一页的数据。
算法：
- 当前页lock值 = (page-1)limit ~ pagelimit （即需要替换到list的数据）
- 当前页前面所有的lock值为 1 ~ （page-1）*limit，查出数据数量n，这个n就是要偏移的值,第一页是0就不查了，少一次请求
- 当前列表的数据list = offset((page-1)*limit - n)->limit()
示例： 第一页，查出所有lock为0的正常排序的数据列表等待替换，此时n值为0，offset为（0-0=0），需要替换的数据是所有lock值为1-50的，从上往下塞入list中。
第二页，查出所有lock值是前面的数据数量n，即lock=1-50的数据数量，n即为需要往前的偏移量，offset=(page-1)limit-n=50-n，根据这个offset查出所有lock为0的第二页正常排序数据等待替换，查出lock值为第二页的即50-100,从上往下塞入list中。lock值需要减去(page-1)limit即50, 按照这个计算后的顺序塞入list。
第三页，查出所有lock值是前面的数据数量n，即lock=1-100的数据数量，n即为需要往前的偏移量，offset=(page-1)limit-n=100-n，根据这个offset查出所有lock为0的第三页正常排序数据等待替换，查出lock值为第三页的即100-150，从上往下塞入list中。lock值需要减去(page-1)limit即100, 按照这个计算后的顺序塞入list。
示例代码
代码语言：javascript复制 /**
* 分类下的问题列表
*
* @param QuestionType $questionType
* @param $questionId
* @param $questionTitle
* @param $limit
* @param $page
* @param $isAdmin
* @param $hasAnswer
* @return array
*/
public function getTypeQuestions(QuestionType $questionType, $questionId, $questionTitle, $limit, $page, $isAdmin, $hasAnswer = false)
{
// 总数
$count = $this->getTypeQuestionsCount($questionType, $questionId, $questionTitle, $isAdmin, $hasAnswer);
// 当前页的lock值 双边包括 即将要进行替换到列表的数据
$currLockStart = ($page - 1) * $limit 1;
$currLockEnd = $page * $limit;
$currLockList = $questionType->questionsLock($currLockStart, $currLockEnd, $isAdmin, $hasAnswer)->get()->toArray(); // 1-50 51-100
// 偏移量值 前面所有lock值的数据数量n page=1时为0减少一次请求
$preLockStart = 1;
$preLockEnd = ($page - 1) * $limit;
$n = $preLockEnd == 0 ? 0 : $questionType->questionsLock($preLockStart, $preLockEnd, $isAdmin, $hasAnswer)->count(); // 1-0 1-50 1-100 的数据条目
// 动态计算偏移量offset
$offset = $limit * ($page - 1) - $n;
// 为替换之前的数据列表
$list = $questionType->questions($questionId, $questionTitle, $isAdmin, $hasAnswer)
->offset($offset)
->limit($limit)
->get()->toArray();
// 指定插入 (当搜索时不处理位置锁定)
if (!$questionId && !$questionTitle) {
foreach ($currLockList as $item) {
array_splice($list, $item['lock'] - $preLockEnd - 1, 0, [$item]);
}
}
// 截取
$data['total'] = $count;
$data['data'] = array_slice($list, 0, $limit);
return $data;
}
/**
* 获取某一lock区间的question列表数据
*
* @param $lockStart
* @param $lockEnd
* @param bool $isAdmin
* @param bool $hasAnswer
* @return IlluminateDatabaseEloquentRelationsBelongsToMany
*/
public function questionsLock($lockStart, $lockEnd, $isAdmin = true, $hasAnswer = false)
{
$table = (new QuestionTypeRelation())->getTable();
$filed = $this->handleCommonRelationField($isAdmin);
return $this->belongsToMany(Question::class, $table, 'question_type_id', 'question_id')
->select($filed)
->with('user')
->where($table.'.lock', '>=', $lockStart)
->where('questions.audit_status', 1)
// ->when(!$isAdmin, function ($query) {
// return $query->where('questions.audit_status', 1);
// })
->when($hasAnswer, function ($query) {
return $query->where('questions.answer_count', '>' ,0);
})
->where($table.'.lock', '<=', $lockEnd)
// ->withTrashed()
->orderBy($table.'.lock');
}
/**
* 通用模型 获取某个type下的question列表数据
*
* @param $questionId
* @param $questionTitle
* @param bool $isAdmin
* @param bool $hasAnswer
* @return IlluminateDatabaseConcernsBuildsQueries|IlluminateDatabaseEloquentRelationsBelongsToMany|mixed
*/
public function questions($questionId, $questionTitle, $isAdmin = true, $hasAnswer = false)
{
$table = (new QuestionTypeRelation())->getTable();
$filed = $this->handleCommonRelationField($isAdmin);
$dbQuery = $this->belongsToMany(Question::class, $table, 'question_type_id', 'question_id')
->select($filed)
->with('user')
// ->withTrashed()
->when($questionId, function ($query) use ($questionId) {
return $query->where('questions.id', $questionId);
})
->where('questions.audit_status', 1)
// ->when(!$isAdmin, function ($query) {
// return $query->where('questions.audit_status', 1);
// })
->when($hasAnswer, function ($query) {
return $query->where('questions.answer_count', '>' ,0);
})
->when($questionTitle, function ($query) use ($questionTitle) {
return $query->where('questions.title', 'like', sprintf('%%%s%%', $questionTitle));
});
// 没有任何搜索条件的时候,只查lock=0的,动态计算limit替换数据
if (!$questionId && !$questionTitle) {
$dbQuery->where($table.'.lock', 0);
}
return $dbQuery->orderByDesc($table.'.top')
->orderByDesc($table.'.sort');
} /**
* 置顶 取消置顶 移除
*
* @param QuestionType $questionType
* @param $params
* @return string
*/
public function questionsAction(QuestionType $questionType, $params)
{
$message = '';
switch ($params['action']) {
case 'del':
QuestionTypeRelation::where(['question_id' => $params['question_id'], 'question_type_id' => $questionType->id])->delete();
$message = '移除成功';
OperationLog::writeFeedLog(13, 50, $params['question_id']);
break;
case 'top':
$item = QuestionTypeRelation::where(['question_id' => $params['question_id'], 'question_type_id' => $questionType->id])->first();
if ($item) {
if ($item->top == 0) {
$res = QuestionTypeRelation::where('question_type_id', $questionType->id)->orderByDesc('top')->first();
$top = $res ? $res->top 1 : 1;
$item->top = $top;
$item->save() && OperationLog::writeFeedLog(13, 12, $params['question_id']);
$message = '置顶成功';
} else {
$item->top = 0;
$item->save() && OperationLog::writeFeedLog(13, 13, $params['question_id']);
$message = '已取消置顶';
}
}
break;
default:
break;
}
return $message;
}
/**
* 锁定 取消锁定
*
* @param QuestionType $questionType
* @param $params
* @return string
*/
public function questionsLock(QuestionType $questionType, $params)
{
$message = '';
$item = QuestionTypeRelation::where(['question_id' => $params['question_id'], 'question_type_id' => $questionType->id])->first();
if ($item) {
if ($params['index'] == 0) {
$item->lock = $params['index'];
$item->save() && OperationLog::writeFeedLog(13, 49, $params['question_id']);
$message = '已取消锁定';
} else {
// 避免锁定值重复导致数据丢失 避免重复锁定同一个值来减少请求
if ($params['index'] == $item->lock) {
$message = '锁定成功';
} else {
$count = QuestionTypeRelation::where(['lock' => $params['index'], 'question_type_id' => $questionType->id])->count();
if ($count) {
$message = '当前位置已有锁定数据，请先取消锁定后再进行设置';
} else {
$item->lock = $params['index'];
$item->save() && OperationLog::writeFeedLog(13, 48, $params['question_id']);
$message = '锁定成功';
}
}
}
}
return $message;
}
/**
* @param QuestionType $questionType
* @param $params
* @return string
*/
public function questionsSort(QuestionType $questionType, $params)
{
$message = '';
$currItem = QuestionTypeRelation::where(['question_id' => $params['curr_question_id'], 'question_type_id' => $questionType->id])->first();
$targetItem = QuestionTypeRelation::where(['question_id' => $params['target_question_id'], 'question_type_id' => $questionType->id])->first();
if ($currItem && $targetItem) {
switch ($params['is_top']) {
case 1:
// 必须同为置顶数据
if ($currItem->top && $targetItem->top) {
$message = $this->dragSort($questionType->id, $params['action'], $currItem, $targetItem, true);
} else {
$message = '必须同为置顶数据';
}
break;
case 0:
// 必须都没有置顶
if (!$currItem->top && !$targetItem->top) {
$message = $this->dragSort($questionType->id, $params['action'], $currItem, $targetItem, false);
} else {
$message = '必须同为非置顶数据';
}
break;
default:
break;
}
}
return $message;
}
/**
* @param $questionTypeId
* @param $action
* @param QuestionTypeRelation $currItem
* @param QuestionTypeRelation $targetItem
* @param false $isTop
* @return string
*/
private function dragSort($questionTypeId, $action, QuestionTypeRelation $currItem, QuestionTypeRelation $targetItem, $isTop = false)
{
if ($isTop) {
$filed = 'top';
} else {
$filed = 'sort';
}
$message = '';
$oldCurrSort = (int) $currItem->$filed;
$oldTargetSort = (int) $targetItem->$filed;
switch ($action) {
case 'up':
// 移动到谁上面
if ($oldCurrSort >= $oldTargetSort) {
$message = '上移参数错误，无法将上方数据上移置下方数据前，请尝试下移动作';
} else {
QuestionTypeRelation::where('question_type_id', $questionTypeId)
->where($filed, '>', $oldCurrSort)
->where($filed, '<=', $oldTargetSort)
->where($filed, '!=', 0)
->update([$filed => DB::raw($filed . ' - 1')]);
$currItem->$filed = $oldTargetSort;
$currItem->save() && OperationLog::writeFeedLog(13, 11, $currItem->question_id);
$message = '调序成功';
}
break;
case 'down':
// 移动到谁下面
if ($oldCurrSort <= $oldTargetSort) {
$message = '下移参数错误，无法将下方数据下移置上方数据后，请尝试上移动作';
} else {
QuestionTypeRelation::where('question_type_id', $questionTypeId)
->where($filed, '<', $oldCurrSort)
->where($filed, '>=', $oldTargetSort)
->where($filed, '!=', 0)
->update([$filed => DB::raw($filed . ' 1')]);
$currItem->$filed = $oldTargetSort;
$currItem->save() && OperationLog::writeFeedLog(13, 11, $currItem->question_id);
$message = '调序成功';
}
break;
default:
break;
}
return $message;
}
/**
* 更新问题分类关系
* 维护分类关系的更新,原本的sort不变,新增的才用id,防止拖动排序后的sort被重新赋值成ID
*
* @param Question $question
* @param $questionType
*/
private function handleEditQuestionRelation(Question $question, $questionType)
{
$sync = [];
$relations = QuestionTypeRelation::where('question_id', $question->id)->get();
$currTypeIds = $relations->pluck('question_type_id')->toArray();
$intersects = array_intersect($currTypeIds, $questionType);
$diffs = array_diff($questionType, $currTypeIds);
foreach ($intersects as $intersect) {
foreach ($relations as $relation) {
if ($relation->question_type_id == $intersect) {
$sync[$intersect] = ['sort' => $relation->sort];
}
}
}
foreach ($diffs as $diff) {
$sync[$diff] = ['sort' => $question->id];
}
$question->questionTypes()->sync($sync);
}
/**
* 问题审核操作日志
*
* @param $questionId
* @param $auditStatus
*/
private function handleQuestionAuditLogs($questionId, $auditStatus)
{
// 添加日志
$opAction = 0;
switch ($auditStatus) {
case 0:
$opAction = 43;
break;
case 1:
$opAction = 5;
break;
case 2:
$opAction = 6;
break;
}
OperationLog::writeFeedLog(10, $opAction, $questionId);
}
/**
* 审核操作系统通知
*
* @param Question $question
* @param $auditStatus
*/
private function handleQuestionAuditNotice(Question $question, $auditStatus)
{
// 系统通知
$notice_type = Notice::NOTICE_SYSTEM_QUESTION_AUDIT;
$toUser = '';
switch ($auditStatus) {
case 0:
break;
case 1:
$toUser = '【你于' . (new Times())->utcChangeToPrc($question->created_at) . '提出的标题为“' . $question->title . '”的问题已通过审核】';
break;
case 2:
$toUser = '【你于' . (new Times())->utcChangeToPrc($question->created_at) . '提出的标题为“' . $question->title . '”的问题审核不通过，已删除】';
break;
}
if (!empty($toUser)) {
(new Notice())->system($question->user_id, $toUser, $notice_type);
}
}
/**
* 根据id或标题查找问题
*
* @param array $params
* @return mixed
*/
public function query($params)
{
if (!empty($params['light'])) {
$columns = ['id', 'user_id', 'summary'];
} else {
$columns = ['id', 'user_id', 'title', 'summary', 'images', 'video', 'created_at'];
}
$questions = Question::query()
->with(['user'])
->when(!empty($params['id']), function ($query) use ($params) {
$id = array_values(
array_filter(
explode(',', $params['id'])
)
);
return $query->whereIn('id', $id);
})
->when(!empty($params['title']), function ($query) use ($params) {
return $query->where('title', 'like', sprintf('%%%s%%', $params['title']));
})
->where('audit_status', Question::AUDIT_STATUS_PASS)
->get($columns)
->toArray();
return $questions ?: [];
}
/**
* 查询简单版
*
* @param array $ids
* @return mixed
*/
public function querySimple($ids)
{
$questions = Question::query()
->with(['user'])
->whereIn('id', $ids)
->where('audit_status', Question::AUDIT_STATUS_PASS)
->get(['id', 'user_id', 'images', 'video'])
->toArray();
return $questions ?: [];
}
/**
* 处理最佳答案
*
* @param $response
* @return mixed
*/
public static function handleBestAnswer($response)
{
$questionIds = [];
foreach ($response['data'] as $item) {
$questionIds[] = $item['id'];
}
if (count($questionIds)) {
$allAnswers = self::getBestAnswer($questionIds);
foreach ($response['data'] as &$res) {
$res['best_answer'] = null;
foreach ($allAnswers as $allAnswer) {
if ($res['id'] == $allAnswer['question_id']) {
$res['best_answer'] = $allAnswer;
}
}
}
}
return $response;
}
/**
* @param $response
* @return array
*/
public static function handleFlycardFormat($response)
{
$res = [];
$res['total'] = $response['total'];
$appId = Question::APPID_QUESTION;
foreach ($response['data'] as $datum) {
$temp['appid'] = $appId;
$temp['title'] = $datum['title'];
$temp['extra']['question'] = [
'id' => $datum['id'],
'content' => $datum['summary'],
'images' => $datum['images'],
'video' => $datum['video'],
'user' => $datum['user']
];
if ($datum['best_answer']) {
$temp['extra']['answer'] = [
'id' => $datum['best_answer']['id'],
'content' => $datum['best_answer']['summary'],
'images' => $datum['best_answer']['images'],
'video' => $datum['best_answer']['video'],
'user' => $datum['best_answer']['user']
];
}
$res['data'][] = $temp;
}
return $res;
}
/**
* pgc接口
* 获取当前question的额外数据 [question_id, answer_id, answer_count, best_answer_like_count]
*
* @param $questionId
* @param $memberId
* @return array
*/
public static function getPgcQuestionData($questionId, $memberId)
{
$res = [];
$question = Question::find($questionId);
if ($question) {
$answerCount = $question->answer_count;
$bestAnswer = self::getBestAnswer([$questionId]);
if ($bestAnswer->count()) {
// 查答案点赞状态
$hasLiked = false;
if ($memberId) {
$user = UserModel::where('member_id', $memberId)->first('id');
$hasLiked = Answer::find($bestAnswer[0]->id)->liked($user->id);
}
$res = [
'question_id' => $questionId,
'answer_id' => $bestAnswer[0]->id,
'answer_count' => $answerCount,
'best_answer_like_count' => $bestAnswer[0]->like_count,
'has_liked' => $hasLiked,
];
}
}
return $res;
}
/**
* 封装通用获取最佳答案数据
*
* @param $questionIds
* @return array|IlluminateHttpResourcesJsonAnonymousResourceCollection
*/
private static function getBestAnswer(array $questionIds)
{
$allAnswers = [];
$topAnswer = Answer::query()
->select('id', 'question_id')
->when($questionIds, function ($query) use ($questionIds) {
return $query->whereIn('question_id', $questionIds);
})
->where('audit_status', Answer::AUDIT_STATUS_PASS)
->where('sort', 1)
->pluck('id', 'question_id')->toArray();
// 有置顶答案的问题ID、答案ID
$topQuestionIds = array_keys($topAnswer);
$topAnswerIds = array_values($topAnswer);
// 没有置顶的问题的答案ID,以及相同排序的时候最大id值
$normalQuestionIds = array_diff($questionIds, $topQuestionIds);
$normalAnswerIds = [];
if (count($normalQuestionIds)) {
$idStr = implode(',', $normalQuestionIds);
$sql = '
SELECT
a1.id,
a1.question_id,
a2.max_id
FROM
answers a1
JOIN (
SELECT
question_id,
max(view_count like_count) AS hot_count,max(id) as max_id
FROM
answers
WHERE
question_id IN (' . $idStr . ')
AND
audit_status = 1
AND
deleted_at IS NULL
GROUP BY
question_id
) a2 ON a1.question_id = a2.question_id
AND (
a1.view_count a1.like_count
) = a2.hot_count
WHERE
a1.question_id IN (' . $idStr . ')
AND
audit_status = 1
AND
deleted_at IS NULL
GROUP BY
a1.question_id
';
$normalAnswer = DB::select($sql);
// 当热度值一样的时候，会有问题，需要判断max_id的hot_count是否等于id的hot_count,相等则取max_id
// 概率很小，建议省点资源，不然还需要每个不同的ID查询两次
foreach ($normalAnswer as $normal) {
$normalAnswerIds[] = $normal->id;
}
}
$allAnswerIds = array_merge($normalAnswerIds, $topAnswerIds);
$allAnswers = Answer::with('user')
->select(['id', 'question_id', 'user_id', 'images', 'video', 'summary', 'audit_status', 'like_count', 'view_count', 'comment_count'])
->whereIn('id', $allAnswerIds)
->get();
$allAnswers = AnswerResource::collection($allAnswers);
return $allAnswers;
}
