Have子句中的未知汇总列 [英] Unknown aggregate column in having clause

问题描述

im试图在PHP + MySQL中构建基于位置的事件搜索(使用Laravel及其雄辩的ORM)

im trying to build a location based event search in PHP + MySQL (Using Laravel and its Eloquent ORM)

这是我正在使用的查询:

This is the query I am using:

select 
    events.*, 
    ( 3959 * acos( cos( radians(50.5) ) * cos( radians( addresses.latitude ) ) * cos( radians( addresses.longitude ) - radians(9.50) ) + sin( radians(50.5) ) * sin( radians( addresses.latitude ) ) ) ) AS distance

 from 
    `events` inner join `addresses` on `events`.`address_id` = `addresses`.`id`
 having 
    `distance` <= 10 
 order by 
    `id` desc limit 15 offset 0

我想知道为什么即使距离列位于"select语句"中，也会弹出此错误.

Im wondering why this error pops up, even though the distance column is in the "select statement".

"SQLSTATE[42S22]: Column not found: 1054 Unknown column 'distance' in 'having clause' (SQL: select count(*) as aggregate from `events` inner join `addresses` on `events`.`address_id` = `addresses`.`id` having `distance` <= 10 order by `id` desc)"

这是我用来将范围添加到基本查询的PHP代码:

This is the PHP Code I use to add the scope to the base query:

    $selectDistance =
        '( 3959 * acos( cos( radians(' . $latitude . ') ) ' .
        '* cos( radians( addresses.latitude ) ) ' .
        '* cos( radians( addresses.longitude ) - radians(' . $longitude . ') ) ' .
        '+ sin( radians(' . $latitude . ') ) ' .
        '* sin( radians( addresses.latitude ) ) ) ) AS distance';

    $query->select(DB::raw('events.*, ' . $selectDistance));
    $query->join('addresses', 'events.address_id', '=', 'addresses.id');
    $query->having('distance', '<=', $km);

非常感谢:)

推荐答案

来自错误消息:

"SQLSTATE [42S22]:找不到列:1054中的未知列'距离' '具有子句'(SQL:选择count(*)作为events内部的聚合 在events上加入addresses.address_id = addresses.id具有 distance< = 10按id desc)"

"SQLSTATE[42S22]: Column not found: 1054 Unknown column 'distance' in 'having clause' (SQL: select count(*) as aggregate from events inner join addresses on events.address_id = addresses.id having distance <= 10 order by id desc)"

我们可以得到以下内容

select count(*) as aggregate 
from `events` inner join `addresses` on `events`.`address_id` = `addresses`.`id` 
having `distance` <= 10 
order by `id` desc

distance不在选择列表中.

这篇关于Have子句中的未知汇总列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！