Yii2: Selecting distance and mapping to model

Question

In my ProjectController, i got the following function:

public function actionFindNearest($latitude, $longitude, $amount){
    $projects = Project::findNearest($latitude, $longitude, $amount);
    $html = '';
    foreach($projects as $project){
        $html .= $this->renderPartial( '/project/preview', array('model'=>$project), true );
    }
    return $html;
}

The method in the model looks like that:

public static function findNearest($latitute, $longitude, $amount){
    $sql = 'SELECT SQRT(
            POW(69.1 * (latitude - '.$latitute.'), 2) +
            POW(69.1 * ('.$longitude.' - longitude) * COS(latitude / 57.3), 2)) AS distance, p.*
            FROM project as p
            ORDER BY distance LIMIT '.$amount;

    $command = Yii::$app->db->createCommand($sql);
    return $command->queryAll();
}

What i get now is an array with 3 objects containing all model attributes plus the distance I want - perfect! In the controller, I pass it to the renderPartial now:

foreach($projects as $project){
        $html .= $this->renderPartial( '/project/preview', array('model'=>$project), true );
    }

In the preview.php template, the distance attribute is lost because now i only have the model object, where the distance isn't an official field.

Edit: the model properties of the project class:

/**
 * This is the model class for table "project".
 *
 * @property integer $id
 * @property string $updated
 * @property string $name
 * @property string $description
 * @property string $teaserimage
 * @property string $goal
 * @property string $current
 * @property string $startdate
 * @property string $enddate
 * @property string $created
 * @property string $headerimage
 * @property integer $category_id
 * @property integer $address_id
 * @property integer $user_id
 * @property string $website_url
 *
 * @property Comment[] $comments
 * @property Donation[] $donations
 * @property Goodie[] $goodies
 * @property Address $address
 * @property Category $category
 * @property User $user
 */

How could I achieve to use the distance in my template file?

Thank you for your suggestions!

Answer 1

You need to add public $distance property into your project model, and then it will be populated automatically for each record

Answer 2

I do something similar, If you want to get all records from a table that are within a range 10 km for example. The approach is to calculate the boundaries minLat, maxLat, minLng, maxLng and filter the records to get only the records into the square, and then do the calculation of distance( only on this records, not for the whole table) for two purposes:

• First, to exclude the records that are into the square(minLat,maxLat,minLong,maxLong) but not into the circle ($MyLocation,5 km).
• Second, to get the distance of each records and for ordering based on distance.

I think that this is the best way, please refer to this article https://www.mullie.eu/geographic-searches/

Notice that engine performs the "where" clause before "select".

Don't forget to add the attribute "distance" to your model, and add it to the overrited method "fields".

class WoPdv extends \yii\db\ActiveRecord{

   public $distance='';
   ...

   public function fields() {

    $fields=parent::fields();

   // unset($fields['distance']);
    $fields[]= "distance";

    return $fields;
   }
   ...
    }

In the Controller

       $range = 10;// km
    // earth's radius in km = ~6371
        $radius = 6371;

        $maxlat = $lat + rad2deg($range / $radius);
        $minlat = $lat - rad2deg($range / $radius);

        $maxlng = $lng + rad2deg($range / $radius / cos(deg2rad($lat)));
        $minlng = $lng - rad2deg($range / $radius / cos(deg2rad($lat)));

        $q = WoPdv::find()->where(['between', 'wo_pdvLat', $minlat, $maxlat])->andWhere(['between', 'wo_pdvLong', $minlng, $maxlng]);

        $q->select(['*',"ROUND((((acos(sin((".$lat."*pi()/180)) * sin((`wo_pdvLat`*pi()/180))+cos((".$lat."*pi()/180)) * cos((`wo_pdvLat`*pi()/180)) * cos(((".$lng."- `wo_pdvLong`)*pi()/180))))*180/pi())*60*1.1515*1.609344),2) as distance"]);//distance in km 
        $q->having('distance <='.$range);
        $q->orderBy('distance');       
     // echo $q->createCommand()->getRawSql();die();
        $POSlist=$q->all();