Laravel查询：同语句中同时检查布尔值及模型关联问题

嘿，这个需求我刚好做过，给你整理了两种靠谱的方案，先从基础的模型关联确认开始，再到核心的查询实现：

先把每个模型的关联代码贴出来，确保基础逻辑没问题：

• User模型：

class User extends Model
{
    public function questions()
    {
        return $this->hasMany(Question::class);
    }

    public function upvotes()
    {
        return $this->hasMany(Upvote::class);
    }
}

• Question模型：

class Question extends Model
{
    public function user()
    {
        return $this->belongsTo(User::class);
    }

    public function answers()
    {
        return $this->hasMany(Answer::class);
    }
}

• Answer模型：

class Answer extends Model
{
    public function question()
    {
        return $this->belongsTo(Question::class);
    }

    public function upvotes()
    {
        return $this->hasMany(Upvote::class);
    }
}

• Upvote模型（补充字段关联）：

class Upvote extends Model
{
    protected $fillable = ['user_id', 'answer_id', 'upvoted', 'downvoted'];

    public function user()
    {
        return $this->belongsTo(User::class);
    }

    public function answer()
    {
        return $this->belongsTo(Answer::class);
    }
}

这种方式性能最优，因为EXISTS只要找到匹配的记录就会停止查询，比计数更高效。我们可以在Answer模型里定义一个查询作用域（Scope）来复用这个逻辑：

class Answer extends Model
{
    // ... 其他关联代码 ...

    /**
     * 给Answer查询附加当前用户的投票状态
     * @param \Illuminate\Database\Eloquent\Builder $query
     * @param int|null $userId
     * @return \Illuminate\Database\Eloquent\Builder
     */
    public function scopeWithUserVote($query, $userId)
    {
        // 如果用户未登录，直接返回false
        if (!$userId) {
            return $query->selectRaw('answers.*, FALSE as has_upvoted, FALSE as has_downvoted');
        }

        return $query->selectRaw('answers.*')
            ->selectRaw('EXISTS(SELECT 1 FROM upvotes WHERE upvotes.answer_id = answers.id AND upvotes.user_id = ? AND upvotes.upvoted = 1) as has_upvoted', [$userId])
            ->selectRaw('EXISTS(SELECT 1 FROM upvotes WHERE upvotes.answer_id = answers.id AND upvotes.user_id = ? AND upvotes.downvoted = 1) as has_downvoted', [$userId]);
    }
}

比如你要查询某个问题及其所有回答，同时带上当前用户的投票状态：

// 获取当前登录用户ID，未登录则为null
$userId = auth()->id();

// 查询单个问题及带投票状态的回答
$question = Question::with(['answers' => function ($query) use ($userId) {
    $query->withUserVote($userId);
}])->find($yourQuestionId);

// 如果是查询所有问题：
$questions = Question::with(['answers' => function ($query) use ($userId) {
    $query->withUserVote($userId);
}])->get();

现在每个Answer对象都会有has_upvoted和has_downvoted两个布尔属性，直接用就行：

@foreach($question->answers as $answer)
    <div class="answer-card">
        <p>{{ $answer->content }}</p>
        <div class="vote-controls">
            <button class="btn {{ $answer->has_upvoted ? 'active' : '' }}">
                👍 {{ $answer->upvotes->where('upvoted', 1)->count() }}
            </button>
            <button class="btn {{ $answer->has_downvoted ? 'active' : '' }}">
                👎 {{ $answer->upvotes->where('downvoted', 1)->count() }}
            </button>
        </div>
    </div>
@endforeach

如果你更习惯用Laravel的withCount方法，也可以这样写（性能略逊于EXISTS，但代码更简洁）：

public function scopeWithUserVote($query, $userId)
{
    if (!$userId) {
        return $query->selectRaw('answers.*, FALSE as has_upvoted, FALSE as has_downvoted');
    }

    return $query->withCount([
        'upvotes as has_upvoted' => function ($q) use ($userId) {
            $q->where('user_id', $userId)->where('upvoted', 1);
        },
        'upvotes as has_downvoted' => function ($q) use ($userId) {
            $q->where('user_id', $userId)->where('downvoted', 1);
        }
    ])
    // 把计数转成布尔值
    ->selectRaw('answers.*, (has_upvoted > 0) as has_upvoted, (has_downvoted > 0) as has_downvoted');
}

这个方案的原理是先统计当前用户对该Answer的赞/踩数量，然后通过(count > 0)转成布尔值，效果和第一种方案一致。

内容的提问来源于stack exchange，提问作者waq