laravel - 将原始 SQL 查询转换为 Laravel 查询的问题
问题描述
我正在尝试转换此 SQL 查询
SELECT
intermediate_routes.city_id,
intermediate_routes.fk_route_id,
intermediate_routes.start_time,
intermediate_routes.end_time,
routes.bus_id,
lk_city.city_name,
bus.bus_no,
bus.id,
routes.fk_start_stopes_id,
routes.fk_end_stopes_id,
lk_company.company_name
FROM
intermediate_routes,
routes,
lk_city,
bus,lk_company
WHERE
intermediate_routes.city_id = 310
AND routes.id = intermediate_routes.fk_route_id
AND lk_city.id = intermediate_routes.city_id
AND routes.bus_id = bus.id
AND bus.fk_company_id = lk_company.id
AND intermediate_routes.start_time BETWEEN '23:00' and '23:59'
ORDER BY
intermediate_routes.start_time
像这样查询构建器 -
$get = \DB::table('intermediate_routes')
->select("intermediate_routes.city_id as city_id", "intermediate_routes.fk_route_id as route_id", "intermediate_routes.start_time as start_time", "intermediate_routes.end_time as end_time", "routes.bus_id as bus_id", "lk_city.city_name as city_name", "bus.bus_no as bus_no", "bus.id as bus_id", "routes.fk_start_stopes_id as start_stop_id", "routes.fk_end_stopes_id as end_stopes_id", "lk_company.company_name as company_name")
->join('lk_city', 'intermediate_routes.fk_route_id', '=', 'lk_city.id')
->join('routes', function($route){
$route->on('intermediate_routes.fk_stopes_id', '=', 'routes.id')
->join("bus", function($bus){
$bus->on('routes.bus_id', '=', 'bus.id')
->join('lk_company', 'bus.fk_company_id', '=', 'lk_company.id');
});
})
->where('intermediate_routes.city_id', $value)
->whereBetween("intermediate_routes.start_time", ['23:00', '23:59'])
->orderBy('intermediate_routes.start_time')
->get();
这样做我没有看到错误,但是也得到了预期的结果。
在原始查询中,我看到返回一行,但在 Laravel 视图中,我没有看到任何结果。
解决方案
我想我已经解决了这个问题。我现在正在这样做,我看到了结果-
$get = \DB::table('intermediate_routes')
->join('lk_city', 'lk_city.id', '=', 'intermediate_routes.city_id')
->join('routes', 'routes.id', '=', 'intermediate_routes.fk_route_id')
->join('bus', 'routes.bus_id', '=', 'bus.id')
->join('lk_company', 'bus.fk_company_id', '=', 'lk_company.id')
->select("intermediate_routes.city_id as city_id", "intermediate_routes.fk_route_id as route_id", "intermediate_routes.start_time as start_time", "intermediate_routes.end_time as end_time", "routes.bus_id as bus_id", "lk_city.city_name as city_name", "bus.bus_no as bus_no", "bus.id as bus_id", "routes.fk_start_stopes_id as start_stop_id", "routes.fk_end_stopes_id as end_stopes_id", "lk_company.company_name as company_name")
->where('intermediate_routes.city_id', $value)
->whereBetween("intermediate_routes.start_time", ["23:00", "23:59"])
->orderBy('intermediate_routes.start_time')
->get();
推荐阅读
- certificate - 如何在 AWS Certificate Manager 中导入 .pfx 证书?
- html - 单击按钮时打开 v-select 选项
- c++ - 在 sfml 中抛出“std::bad_alloc”实例后调用终止
- c# - 我可以在 C# 中约束构造函数参数吗
- pascal - 在 TChart Lazarus 中添加值时如何使用 Label 而不是 Double
- javascript - 将所选图像显示到另一页
- python - 熊猫阅读Excel和所需数据的拆分部分
- java - 关于在 ANTLR 中查找布尔变量的问题
- arrays - 如何在C中基于线程添加到数组
- android - 有什么方法可以设置首选项对话框的主题?