Laravel Eloquent复杂查询求助：多表关联仅返回单条结果

Hey there, sorry to hear you've been stuck on this for three weeks—let's get this sorted out for you! The core issues here are fixing your query to return all threads, and leveraging Laravel's Eloquent ORM (with proper model relationships) to make this code cleaner and more maintainable long-term.

Why Your Initial Query Only Returns One Thread #

The problem in your first DB query is this line:

->where('replies.created_at', '=', DB::raw('(SELECT max(created_at) FROM replies)'))

This filters results to only the thread that has the absolute latest reply across your entire database, not the latest reply per thread. That's why you're only seeing one result instead of two.

The Better Approach: Use Eloquent Models & Relationships #

First, let's define your models and their relationships—this will eliminate the need for messy manual joins.

Step 1: Define the Models #

Thread.php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsTo;
use Illuminate\Database\Eloquent\Relations\HasMany;
use Illuminate\Database\Eloquent\Relations\HasOne;

class Thread extends Model
{
    // Disable timestamps if your table doesn't have created_at/updated_at
    // public $timestamps = false;

    public function author(): BelongsTo
    {
        return $this->belongsTo(User::class, 'user_id');
    }

    public function replies(): HasMany
    {
        return $this->hasMany(Reply::class);
    }

    public function firstReply(): HasOne
    {
        return $this->hasOne(Reply::class)->oldest();
    }

    public function latestReply(): HasOne
    {
        return $this->hasOne(Reply::class)->latest();
    }
}

Reply.php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsTo;

class Reply extends Model
{
    // public $timestamps = false;

    public function thread(): BelongsTo
    {
        return $this->belongsTo(Thread::class);
    }

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

Step 2: Write the Eloquent Query #

Now you can fetch all the data you need with a clean, readable query:

use App\Models\Thread;

$threads = Thread::with([
    // Eager load author (only select needed fields to optimize)
    'author:id,name',
    // Eager load first reply (only get the body)
    'firstReply:id,thread_id,body',
    // Eager load latest reply with its user
    'latestReply' => function ($query) {
        $query->with('user:id,name')->select('id', 'thread_id', 'created_at', 'user_id');
    }
])
// Get total number of replies per thread
->withCount('replies')
// Select only the thread fields you need
->select('id', 'category_id', 'title', 'visits', 'hd', '18', 'prv')
->get();

Step 3: Access the Data #

You can loop through the results and map the fields exactly as you need:

foreach ($threads as $thread) {
    $threadData = [
        'thread_id' => $thread->id,
        'thread_category' => $thread->category_id,
        'thread_title' => $thread->title,
        'thread_first_message' => $thread->firstReply?->body, // Null-safe in case no replies
        'thread_author' => $thread->author->name,
        'author_id' => $thread->author->id,
        'last_reply_date' => $thread->latestReply?->created_at,
        'last_reply_user' => $thread->latestReply?->user->name,
        'lru_id' => $thread->latestReply?->user->id,
        'visits' => $thread->visits,
        'hd' => $thread->hd,
        '18' => $thread->{'18'}, // Use curly braces for numeric field names
        'prv' => $thread->prv,
        'count_replies' => $thread->replies_count
    ];

    // Use $threadData as needed
}

Alternative: Fixed DB Facade Query #

If you want to stick with the DB facade for now, here's a corrected query that returns all threads and avoids the single-result issue:

$threads = DB::table('threads as t')
    ->select(
        't.id as thread_id',
        't.category_id as thread_category',
        't.title as thread_title',
        // Get first reply body per thread
        DB::raw('(SELECT body FROM replies WHERE thread_id = t.id ORDER BY created_at ASC LIMIT 1) as thread_first_message'),
        'u1.name as thread_author',
        'u1.id as author_id',
        // Get latest reply date per thread
        DB::raw('(SELECT created_at FROM replies WHERE thread_id = t.id ORDER BY created_at DESC LIMIT 1) as last_reply_date'),
        // Get latest reply user name per thread
        DB::raw('(SELECT u.name FROM replies r JOIN users u ON r.user_id = u.id WHERE r.thread_id = t.id ORDER BY r.created_at DESC LIMIT 1) as last_reply_user'),
        // Get latest reply user ID per thread
        DB::raw('(SELECT u.id FROM replies r JOIN users u ON r.user_id = u.id WHERE r.thread_id = t.id ORDER BY r.created_at DESC LIMIT 1) as lru_id'),
        't.visits',
        't.hd',
        't.18',
        't.prv',
        // Count total replies per thread
        DB::raw('(SELECT COUNT(id) FROM replies WHERE thread_id = t.id) as count_replies')
    )
    ->join('users as u1', 'u1.id', '=', 't.user_id')
    ->get();

Notes on Your Raw SQL #

Your raw SQL uses implicit joins (comma-separated tables) which is outdated syntax—explicit JOIN clauses are more readable and less error-prone. Additionally, joining the replies table three times can lead to performance issues if you have a lot of replies; using subqueries (like in the DB facade example above) or Eloquent relationships is a better approach.

内容的提问来源于stack exchange，提问作者David Encina Martínez