如何查询以查找 laravel 5 的出发和到达时间?

Question

我试图搜索出发时间和巴士到达时间

我的数据库

id | bus_id | city | time
1  |1       |  1   | 05:00
2  |1       |  2   | 06:00
3  |1       |  3   | 07:00

示例用户选择城市起点 = 1 和城市目的地 = 2

那么我期望的结果是

bus | time departure | time arrival
1   | 05:00          | 06:00

我的查询

$departures = Departure::whereBetween(
'city', [
$request->origin, $request->destination
])
->get();

Answer 1

如果到达特定城市的特定公共汽车的时间只有一个记录， 您可以尝试此操作，否则它将返回最大到达时间和最大离开时间。

它将两条记录合并在一起(bus_1到 A，bus_1 到 b(，并选择最大时间作为到达时间或出发时间：

$departure_citys = Departure::where('city',$request->origin)
->selectRaw('id, 
bus_id, 
city,
time AS time_departure, 
NULL AS time_arrival');
$arrival_citys   = Departure::where('city',$request->destination)
->selectRaw('id, 
bus_id, 
city,
NULL AS time_departure, 
time AS time_arrival');
$departures = $departure_citys->unionAll($arrival_citys);
DB::table(DB::raw("({$departures->toSql()}) AS tr"))
->mergeBindings($departures->getQuery())
->groupBy('city')
->selectRaw('bus_id,
MAX(time_departure) AS time_dept,
MAX(time_arrival) AS time_arvl')->get();