Laravel Eloquent多whereHas关联查询实现公交线路站点顺序校验

云上老周

2026-9-30

实现方案

你可以直接把原生SQL的EXISTS校验逻辑平移到Eloquent查询中，同时关联places表直接通过slug匹配地点，完整代码如下：

use Illuminate\Support\Facades\DB;

$buses = Bus::whereHas('route', function ($query) use ($from, $to) {
    $query->whereExists(function ($startQuery) use ($from, $to) {
        $startQuery->select(DB::raw(1))
            ->from('route_locations as start_loc')
            // 关联出发地地点表匹配slug
            ->join('places as start_place', 'start_loc.place_id', '=', 'start_place.id')
            ->whereColumn('start_loc.route_id', 'routes.id')
            ->where('start_place.slug', $from)
            ->where('start_loc.end', 0)
            // 校验存在顺序更靠后的目的地站点
            ->whereExists(function ($endQuery) use ($to) {
                $endQuery->select(DB::raw(1))
                    ->from('route_locations as end_loc')
                    ->join('places as end_place', 'end_loc.place_id', '=', 'end_place.id')
                    ->whereColumn('end_loc.route_id', 'start_loc.route_id')
                    ->where('end_place.slug', $to)
                    ->where('end_loc.start', 0)
                    ->whereColumn('end_loc.order', '>', 'start_loc.order');
            });
    });
})
// 预加载关联数据，视图层可直接调用关联属性
->with(['route.locations.place'])
->get();