Laravel :query select 'as' not working with where or have

Question

I am trying to get nearby companies here is my query

$lat = 21.41241750000001;
$long = 39.23094140625001;
return
    $this->model->select('id', 'lat', 'long', DB::raw(sprintf(
    '(6371 * acos(cos(radians(%1$.7f)) * cos(radians(`lat`)) * cos(radians(`long`) - radians(%2$.7f)) + sin(radians(%1$.7f)) * sin(radians(`lat`)))) AS `distance`',
    $lat,
    $long
)))
    ->having('distance', '<', 50)
    ->orderBy('distance', 'asc')
    ->paginate();

and always get me

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'distance' in 'having clause' (SQL: select count(*) as aggregate from companies having distance = 50)

my table schema

+-------------------+----------------------------------------------------+------+-----+---------+----------------+
| Field             | Type                                               | Null | Key | Default | Extra          |
+-------------------+----------------------------------------------------+------+-----+---------+----------------+
| id                | int(10) unsigned                                   | NO   | PRI | NULL    | auto_increment |
| name_en           | varchar(191)                                       | NO   |     | NULL    |                |
| name_ar           | varchar(191)                                       | NO   |     | NULL    |                |
| search_keywords   | text                                               | NO   |     | NULL    |                |
| status            | enum('REJECTED','ACCEPTED','PENDING','DUPLICATED') | NO   | MUL | PENDING |                |
| rejection_reason  | text                                               | NO   |     | NULL    |                |
| industry          | varchar(191)                                       | NO   |     | NULL    |                |
| website           | varchar(191)                                       | NO   |     | NULL    |                |
| city_id           | int(10) unsigned                                   | YES  | MUL | NULL    |                |
| zone_id           | int(10) unsigned                                   | NO   | MUL | NULL    |                |
| size              | int(11)                                            | NO   |     | NULL    |                |
| lat               | varchar(191)                                       | NO   | MUL | NULL    |                |
| long              | varchar(191)                                       | NO   |     | NULL    |                |
| formatted_address | text                                               | NO   |     | NULL    |                |
| street            | varchar(191)                                       | NO   |     | NULL    |                |
| building          | varchar(191)                                       | NO   |     | NULL    |                |
| floor_no          | int(10) unsigned                                   | NO   |     | NULL    |                |
| landmarks         | varchar(255)                                       | NO   |     | NULL    |                |
| company_id        | int(10) unsigned                                   | YES  | MUL | NULL    |                |
| created_at        | timestamp                                          | YES  |     | NULL    |                |
| updated_at        | timestamp                                          | YES  |     | NULL    |                |
+-------------------+----------------------------------------------------+------+-----+---------+----------------+

Note: if I do the query without have and order the distance returned and calculated any help?

id | int(10) unsigned | NO | PRI | NULL | auto_increment | | name_en | varchar(191) | NO | | NULL | | city_id | int(10) unsigned | YES | | NULL | | lat | varchar(191) | NO | MUL | NULL | | | long | varchar(191) – — user10076385

Alexandre Strapacao G. Vianna Alexandre Strapacao G. Vianna · Accepted Answer · 2020-10-18T12:26:51

I had a similar problem. I adapted my code for your context. You may try addSelect. I think this code is a starting point for your solution:

$lat = 21.41241750000001;
$long = 39.23094140625001;
Companies::addSelect(['distance' => DB::raw(sprintf('(6371 * acos(cos(radians(%1$.7f)) * cos(radians(`lat`)) * cos(radians(`long`) - radians(%2$.7f)) + sin(radians(%1$.7f)) * sin(radians(`lat`)))) AS `distance`',
                      $lat,
                      $long))
                      ])                    
          ->havingRaw('distance < 50')
          ->orderBy('distance', 'asc')
          ->get();

I couldn't test it; Send us feedback!

Laravel :query select 'as' not working with where or have

2 Answers