yii2使用自定义列查询：'having子句'中的未知列'columnname'

Question

i am caught up in a situation here

i have a small custom search model's code here

    $query = Ad::find();

    $dataProvider = new ActiveDataProvider([
        'query' => $query,
    ]);

    if($subcategorymodel){
        $query->andFilterWhere([
            'sub_category_id' => $subcategorymodel->id,
        ]);
    }
    if($adcategorymodel){
        $query->andFilterWhere([
            'ad_category_id' => $adcategorymodel->id,
        ]);
    }
    if($nearmodel){
        $lat = $nearmodel->latitude;
        $long = $nearmodel->longitude;
        $query->select('*, (
        (
        ACOS( SIN( '.$lat.' * PI( ) /180 ) * SIN( latitude * PI( ) /180 ) + COS( '.$lat.' * PI( ) /180 ) * COS( latitude * PI( ) /180 ) * COS( ( '.$long.' - `longitude` ) * PI( ) /180 ) ) *180 / PI( )
        ) *60 * 1.1515 * 1.609344
        ) AS distance');

        $query->having('distance <=100'); //condition for my custom column
    }

    $query->andFilterWhere([
        'status' => 1,
    ]);

    return $dataProvider;

which runs fine with the $query->having('distance <=100'); clause but i get this error

   SQLSTATE[42S22]: Column not found: 1054 Unknown column 'distance' in 'having clause'
   The SQL being executed was: SELECT COUNT(*) FROM `tbl_ad` WHERE ((`sub_category_id`=1) AND    (`ad_category_id`=1)) AND (`status`=1) HAVING distance <=100
   Error Info: Array
   (
     [0] => 42S22
     [1] => 1054
     [2] => Unknown column 'distance' in 'having clause'

at the view what i have is

<?php 
    echo ListView::widget([
     'dataProvider' => $dataProvider,
     'itemOptions' => ['class' => 'item'],
     'itemView' => '_item_view',
     'pager' => ['class' => \kop\y2sp\ScrollPager::className()]
]);
?>

and on inspection of the error stack, somewhere around the codes, yii2 calls this function here (i guess to play with the pagination data)

public function count($q = '*', $db = null)
{
    return $this->queryScalar("COUNT($q)", $db);
}

so, is there a solution/work-around for this?

Answer 1

Is this a hard query to run? do you have a lot of records as the dumbest solution would be to declare

$query->having('(
        (
        ACOS( SIN( '.$lat.' * PI( ) /180 ) * SIN( latitude * PI( ) /180 ) + COS( '.$lat.' * PI( ) /180 ) * COS( latitude * PI( ) /180 ) * COS( ( '.$long.' - `longitude` ) * PI( ) /180 ) ) *180 / PI( )
        ) *60 * 1.1515 * 1.609344
        ) <=100');

Another solution would be to create your own Query class and overwrite count with your own version that takes the having into consideration.