Count Zero Count Zero - 7 days ago 7
MySQL Question

Return a value from a MySQL select with function using a Yii2 Query

I have a Yii2 searchQuery setup which works very well for calculating those records not within the desired radius of a given point.

public function search($params)
{
$query = TrainerExtend::find();
$dataProvider = new ActiveDataProvider([
'query' => $query,
'pagination' => [
'pageSize' => $this->pageSize,
],
]);
$this->load($params);
$query->having([
'<',
'ST_Distance_Sphere(POINT(' . $this->longitude . ',' . $this->latitude . '),
PointFromText(CONCAT(\'POINT(\',longitude,\'\',latitude,\')\')))',
$this->radius * 1000
]);
}
$rows = $query->distinct();

return $dataProvider;
}

public function actionFind()
{
$searchModel = new TrainerSearch();
$dataProvider = $searchModel->search(Yii::$app->request->queryParams);

$trainers = $dataProvider->getModels();

if (array_key_exists('trainerSearch',Yii::$app->request->queryParams)
&& !empty($trainerSearch = Yii::$app->request->queryParams['TrainerSearch']))
{
if (array_key_exists('specialties',$trainerSearch) && !empty($specialities = $trainerSearch['specialties']))
{
foreach ($trainers as $trainer) {
$specialties = $trainer->trainerSpecialtiesArray;
$trainer->match = $this->caluclateRelevance($specialties);
}
}
}
return $this->render('find', [
'searchModel' => $searchModel,
'dataProvider' => $dataProvider,
]);
}


resulting query

SELECT DISTINCT * FROM `trainer` WHERE `gender`='0' HAVING ST_Distance_Sphere(POINT(-123.3836214,48.418248899999995), PointFromText(CONCAT('POINT(',longitude,' ',latitude,')'))) < 59000 LIMIT 2`


Resulting record is bang on for radius calculations, however I need the distance calculated by the geo function as well. I have tried using an as keyword, but it keeps breaking the query.

Answer

For calculated column you must add the proper select code in you query

$query->->addSelect(["*",
         "ST_Distance_Sphere(POINT($this->longitude ,
          $this->latitude ), 
          PointFromText(CONCAT('POINT(',longitude,' ',latitude,')')))as distance"]);

then for show the result

first you should add a public var in you model eg:

  class TrainerExtend extends \yii\db\ActiveRecord
  {

      public $distance;  // codice  di stato del codice fiscale 
      ......

      public static function tableName()
      {
      ....

and second you can use distance as a model attribute in your views eg: gridview

  <?= GridView::widget([
    'dataProvider' => $dataProvider,
     ......
    'columns' => [
        .....
        'distance', 
        ...
Comments