Laravel中如何按关联表road_tax的expires字段排序Vehicle查询结果

我有两张表：vehicles和road_tax。vehicles表包含id和registration字段，与road_tax表（含id、vehicle_id、valid from、expires字段）为一对多关联，一辆车有多条缴税历史记录。我需要按车辆需重新缴税的先后顺序（即expires字段升序）列出所有车辆，目前已能显示车辆的最新缴税到期时间，但无法按该字段排序。我使用Laravel框架，具备PHP和MySQL基础，现有代码如下：

Controller代码 #

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Models\Road_tax;
use App\Models\Vehicle;
use Carbon\Carbon;
class DashboardController extends Controller
{
    /**
     * Create a new controller instance.
     *
     * @return void
     */
    public function __construct()
    {
        $this->middleware('auth');
    }

    /**
     * Show the application dashboard.
     *
     * @return \Illuminate\Contracts\Support\Renderable
     */

    public function Index()
    {
        $road_taxes = Vehicle::with('latest_Road_Tax')->get()

        return view('dashboard.index', compact('road_taxes'));
    }
}

Vehicle Model代码 #

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Vehicle extends Model
{

    public function Road_taxes()
    {
        return $this->hasMany(Road_tax::class);
    }

    public function latest_Road_Tax()
    {
        return $this->hasOne(Road_tax::class)->latest("expires");
    }
    
}

View代码 #

@foreach($road_taxes as $road_tax) 
    <div class="dashboard-item-title">
        <h6 style="font-weight:600; margin-bottom:0px;">{{$road_tax->registration}}</h6>

        <span class="dashboard-item-body" style="margin-top:-10px;">
            <small style="font-weight:300; color:grey;">Tax expires for this vehicle on</small>
            <small style="font-weight:300;"> | {{$road_tax->latest_Road_Tax->expires}}</small>
        </span>
    </div>
@endforeach

直接使用with('latest_Road_Tax')仅能预加载关联数据，无法用关联字段对主模型排序。需要通过子查询获取每个车辆的最新expires值，以此作为排序依据。

1. 修改控制器查询逻辑 #

方法一：子查询获取最新到期时间并排序 #

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Models\Road_tax;
use App\Models\Vehicle;
use Carbon\Carbon;
use Illuminate\Database\Eloquent\Builder;

class DashboardController extends Controller
{
    public function __construct()
    {
        $this->middleware('auth');
    }

    public function Index()
    {
        // 子查询：获取每个车辆的最新expires时间
        $latestExpiresSub = Road_tax::select('expires')
            ->whereColumn('road_tax.vehicle_id', 'vehicles.id')
            ->latest('expires')
            ->limit(1);

        // 查询车辆并按最新到期时间升序排序
        $vehicles = Vehicle::with('latest_Road_Tax')
            ->select('vehicles.*')
            ->selectSub($latestExpiresSub, 'latest_expires')
            ->orderBy('latest_expires', 'asc')
            ->get();

        return view('dashboard.index', compact('vehicles'));
    }
}

方法二：关联子查询直接排序（更简洁） #

public function Index()
{
    $vehicles = Vehicle::with('latest_Road_Tax')
        ->orderBy(function (Builder $query) {
            $query->select('expires')
                ->from('road_tax')
                ->whereColumn('road_tax.vehicle_id', 'vehicles.id')
                ->latest()
                ->limit(1);
        }, 'asc')
        ->get();

    return view('dashboard.index', compact('vehicles'));
}

2. 调整视图变量名（语义化优化） #

原视图中$road_tax实际是Vehicle实例，建议修改变量名避免混淆：

@foreach($vehicles as $vehicle) 
    <div class="dashboard-item-title">
        <h6 style="font-weight:600; margin-bottom:0px;">{{$vehicle->registration}}</h6>
        <span class="dashboard-item-body" style="margin-top:-10px;">
            <small style="font-weight:300; color:grey;">Tax expires for this vehicle on</small>
            <small style="font-weight:300;"> | {{$vehicle->latest_Road_Tax->expires}}</small>
        </span>
    </div>
@endforeach

原理说明 #

• 预加载with('latest_Road_Tax')仅负责拉取关联数据，不会将关联字段纳入主查询的排序逻辑。
• 通过子查询将每个车辆的最新expires值映射为主查询的临时字段，就能基于该字段实现排序。

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