问题描述
我需要抓住关系dealer"距离
的车辆200
I need to grab the vehicles whose relation 'dealer' is having distance < 200
Vehicle::join('dealers', 'vehicles.dealer_id', '=', 'dealers.id')
->select(DB::raw("dealers.id, ( cos( radians(latitude) ) * cos( radians( longitude ) ) ) AS distance"))
->havingRaw('distance < 200');
我试图在与关系(belongsTo)经销商的别名距离"上使用hasRaw.但失败并出现错误:
I am trying to use havingRaw on the alias 'distance' from the relation (belongsTo) dealer. But failed with an error:
未找到列:1054 'have 子句'中的未知列'距离'
Column not found: 1054 Unknown column 'distance' in 'having clause'
更新
当我像这样向上面的查询添加分页功能时，实际上会出现这个问题.
The issue actually occurs when I add paginate function to the above query like this.
$vehicle = Vehicle::join('dealers', 'vehicles.dealer_id', '=', 'dealers.id')
->select(DB::raw("dealers.id, ( cos( radians(latitude) ) * cos( radians( longitude ) ) ) AS distance"))
->havingRaw('distance < 200');
$result = $vehicle->paginate(15);
推荐答案
更新
如果你在查询中使用 paginate() ，laravel 将尝试执行以下 SQL 代码来计算可能匹配的总数:
Update
If you use paginate() with your query laravel will try to execute the following SQL code to count the total number of possible matches:
select count(*) as aggregate
from `vehicles` inner join `dealers`
on `vehicles`.`dealer_id` = `dealers`.`id`
having distance < 200
如您所见，此查询中没有这样的列或别名distance.
As you can see, there is no such column or alias distance in this query.
我原来回答中的选项 2 也能解决这个问题.
这似乎是一个 MySQL 严格模式问题.如果您使用 laravel 5.3 严格模式默认启用.您有两个选择:
That seams to be a MySQL-strict-mode issue. If you use laravel 5.3 strict mode is enabled per default. You have two options:
选项 1:在 config/database.php
...
'mysql' => [
...
'strict' => false,
...
],
...
选项 2:使用 WHERE 条件
Option 2: Use a WHERE condtition
Vehicle::join('dealers', 'vehicles.dealer_id', '=', 'dealers.id')
->select(DB::raw("dealers.id, ( cos( radians(latitude) ) * cos( radians( longitude ) ) ) AS distance"))
->whereRaw('cos( radians(latitude) ) * cos( radians( longitude ) ) < 200');
文档:
标准 SQL 的 MySQL 扩展允许在 HAVING 中引用子句到选择列表中的别名表达式.启用ONLY_FULL_GROUP_BY 禁用此扩展，因此需要 HAVING子句使用无别名的表达式编写.
A MySQL extension to standard SQL permits references in the HAVING clause to aliased expressions in the select list. Enabling ONLY_FULL_GROUP_BY disables this extension, thus requiring the HAVING clause to be written using unaliased expressions.
服务器 SQL 模式 - ONLY_FULL_GROUP_BY
这篇关于如何在laravel 5中的关系列上使用“具有"和分页的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！