常见的数据列表查询：同时支持置顶、锁定位置、移动排序、分页的实现逻辑

    /**
     * 分类下的问题列表
     *
     * @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 \Illuminate\Database\Eloquent\Relations\BelongsToMany
     */
    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 \Illuminate\Database\Concerns\BuildsQueries|\Illuminate\Database\Eloquent\Relations\BelongsToMany|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|\Illuminate\Http\Resources\Json\AnonymousResourceCollection
     */
    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;
    }