Laravel中Join与Left Join组合查询及SQL转查询构造器/ORM咨询

Hey there! Let's break down your two questions step by step—these are super common scenarios when working with Laravel's query tools, so I’ve got you covered.

Laravel’s query builder makes mixing inner joins (join()) and left joins (leftJoin()) straightforward—you just chain the methods in the order that makes sense for your data relationships.

Quick recap:

• join() creates an inner join: only records that match the condition in both tables are returned.
• leftJoin() creates a left outer join: all records from the "left" table (the one you started with) are kept, even if there’s no match in the right table (those fields will be null).

Here’s a simple example joining three tables (users, posts, comments) with both join types:

$results = DB::table('users')
    // Inner join: only users who have posts show up
    ->join('posts', 'users.id', '=', 'posts.user_id')
    // Left join: keep all posts, even if they have no comments
    ->leftJoin('comments', 'posts.id', '=', 'comments.post_id')
    ->select('users.name', 'posts.title', 'comments.content')
    ->get();

You can add as many joins as you need, mixing inner and left joins freely—just make sure your join conditions correctly link the tables.

First off: yes, you absolutely can convert that SQL to Laravel’s query builder ORM. Let’s assume your raw SQL looks something like this (based on the tables you mentioned):

SELECT 
    answers.*,
    questions.title AS question_title,
    users.name AS author_name,
    CASE WHEN upvote_answers.user_id IS NOT NULL THEN 1 ELSE 0 END AS has_upvoted
FROM answers
JOIN questions ON answers.question_id = questions.id
JOIN users ON answers.user_id = users.id
LEFT JOIN upvote_answers ON upvote_answers.answer_id = answers.id AND upvote_answers.user_id = {current_user_id}

Option 1: Using Laravel Query Builder #

This stays close to your original SQL syntax, with the added benefit of Laravel’s safety features (like automatic parameter binding):

// Get the current logged-in user's ID
$currentUserId = auth()->id();

$answers = DB::table('answers')
    // Inner joins with questions and users
    ->join('questions', 'answers.question_id', '=', 'questions.id')
    ->join('users', 'answers.user_id', '=', 'users.id')
    // Left join to check if the current user upvoted the answer
    ->leftJoin('upvote_answers', function ($join) use ($currentUserId) {
        $join->on('upvote_answers.answer_id', '=', 'answers.id')
             ->where('upvote_answers.user_id', '=', $currentUserId);
    })
    // Select your desired fields, including the boolean has_upvoted flag
    ->select(
        'answers.*',
        'questions.title AS question_title',
        'users.name AS author_name',
        DB::raw('CASE WHEN upvote_answers.user_id IS NOT NULL THEN TRUE ELSE FALSE END AS has_upvoted')
    )
    ->get();

Option 2: Using Laravel ORM (Eloquent) #

If you have Eloquent models set up for your tables, you can use relationships to make this even cleaner. First, define the relationships in your Answer model:

// app/Models/Answer.php
namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Answer extends Model
{
    // Relationship to the question the answer belongs to
    public function question()
    {
        return $this->belongsTo(Question::class);
    }

    // Relationship to the user who wrote the answer
    public function author()
    {
        return $this->belongsTo(User::class, 'user_id');
    }

    // Many-to-many relationship with users who upvoted the answer
    public function upvoters()
    {
        return $this->belongsToMany(User::class, 'upvote_answers');
    }
}

Then, use withCount() to add the has_upvoted flag directly to your Answer models:

$currentUserId = auth()->id();

$answers = Answer::with(['question', 'author'])
    ->withCount([
        'upvoters AS has_upvoted' => function ($query) use ($currentUserId) {
            $query->where('user_id', $currentUserId);
        }
    ])
    ->get();

// To automatically cast the has_upvoted integer (0/1) to a boolean, add this to your Answer model:
protected $casts = [
    'has_upvoted' => 'boolean',
];

Now each Answer instance in the results will have a has_upvoted boolean attribute telling you if the current user upvoted it.

Either approach works great—use query builder if you want to stick close to raw SQL, or use Eloquent if you prefer working with model relationships.

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