如何在laravel 5中的关系列上使用“具有"和分页 [英] How to use 'having' with paginate on relationship's column in laravel 5

问题描述

我需要抓住关系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中的关系列上使用“具有"和分页的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！