Laravel地铁APP无需输入route_id查询两站点上下行路线方案求解

解决方案 #

一、无需修改表结构的查询方案 #

你只需要调整原有递归查询逻辑，去掉固定route_id的限制，自动匹配所有同时包含起点、终点且站点顺序符合通行方向的线路即可，优化后的SQL如下：

SET @source = 2, @destination = 8;

WITH RECURSIVE
cte AS 
(
    SELECT `route_id`, `station_id`, `next_station_id`, `station_order`, `distance`, `duration`
    FROM `route_station`
    WHERE `station_id` = @source
    -- 先过滤掉不可能到达终点的线路：必须同时包含终点
    AND `route_id` IN (
        SELECT DISTINCT rs1.route_id
        FROM route_station rs1
        JOIN route_station rs2 ON rs1.route_id = rs2.route_id
        WHERE rs1.station_id = @source AND rs2.station_id = @destination
        -- 保证起点站顺序小于终点站，方向正确
        AND rs1.station_order < rs2.station_order
    )
    
    UNION ALL
    
    SELECT 
        cte.`route_id`, 
        rs.`station_id`, 
        rs.`next_station_id`, 
        rs.`station_order`,
        cte.distance + rs.distance,
        ADDTIME(cte.duration, rs.duration)
    FROM `route_station` rs
    JOIN cte ON rs.station_id = cte.next_station_id
        AND rs.route_id = cte.route_id
    WHERE cte.station_id != @destination
)
-- 按线路分组取所有可行路线
SELECT * FROM cte
WHERE station_id = @destination
ORDER BY route_id, station_order ASC;

如果要查询反向路线（比如8到2），只需要把rs1.station_order < rs2.station_order改成rs1.station_order > rs2.station_order即可；也可以直接去掉这个条件，查询会自动返回所有方向的可行路线，你可以在业务层根据总耗时/总距离排序给用户优先推荐最优路线。

二、可选表结构优化建议 #

如果要后续支持跨线路换乘、提升查询效率，可以做以下调整：

• 给route_station表添加联合索引 idx_route_order(route_id, station_order, station_id)，能大幅提升递归查询的速度
• 如果你不需要单独存储上下行线路（比如样例里route2和route3其实是同一条线的正反方向），可以给routes表新增direction字段（0=上行，1=下行），同一个线路的上下行共用同一个线路基础信息，减少冗余
• 换乘站关联逻辑可以单独拆成station_interchange表，存储同一个换乘站对应的不同线路站点关联，方便后续做多线路换乘路径计算

三、Laravel 中实现代码示例 #

// 接收用户输入的起点终点ID
$source = $request->input('source_station_id');
$destination = $request->input('destination_station_id');

$routes = DB::select("
WITH RECURSIVE
cte AS 
(
    SELECT `route_id`, `station_id`, `next_station_id`, `station_order`, `distance`, `duration`
    FROM `route_station`
    WHERE `station_id` = ?
    AND `route_id` IN (
        SELECT DISTINCT rs1.route_id
        FROM route_station rs1
        JOIN route_station rs2 ON rs1.route_id = rs2.route_id
        WHERE rs1.station_id = ? AND rs2.station_id = ?
        AND rs1.station_order < rs2.station_order
    )
    
    UNION ALL
    
    SELECT 
        cte.`route_id`, 
        rs.`station_id`, 
        rs.`next_station_id`, 
        rs.`station_order`,
        cte.distance + rs.distance,
        ADDTIME(cte.duration, rs.duration)
    FROM `route_station` rs
    JOIN cte ON rs.station_id = cte.next_station_id
        AND rs.route_id = cte.route_id
    WHERE cte.station_id != ?
)
SELECT * FROM cte
WHERE station_id = ?
ORDER BY route_id, station_order ASC;
", [$source, $source, $destination, $destination, $destination]);

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