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) – — Michael George
– Michael George, Commented Oct 18, 2020 at 11:41

Alexandre Strapacao G. Vianna · Accepted Answer · 2020-10-18 13:11:26Z

0

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!

edited Oct 18, 2020 at 13:11

answered Oct 18, 2020 at 12:26

Alexandre Strapacao G. Vianna

3993 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Michael George Over a year ago

No it did not work with me but I get it to work but it not the best solution I used get instead of paginate

Alexandre Strapacao G. Vianna Over a year ago

I just edited to get() instead paginate(). Why is it not a good solution?

Michael George · Accepted Answer · 2020-10-18 13:09:59Z

-1

the problem with me was in paginate so I used get but I don't know why so I any one can explain it will be wonderful

answered Oct 18, 2020 at 13:09

Michael George

3283 silver badges15 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related