Add field calculated from SQL query to Yii2 ActiveRecord model

Question

I'm writing code to return a list of locations that are within a specified region, and close to a certain latitude and longitude. The database layout is that there is a Location table that contains the businesses, a Region table that defines the regions (with UrlName being a slug that identifies the region), and a regionLocation table that maps regions to Locations.

The SQL query is pretty hairy, but it calculates a virtual column named "Distance" that I would like to have accessible in the model returned.

Here is a shortened version of the code that appears in my Location model:

        public static function getByRegionAndLatLong( $regionName, $lat, $long ) {

        $sql = "SELECT
            `Location`.`LocationId`,
            `Location`.`Latitude`,
            `Location`.`Longitude`,
                    (
                    3959 * acos (
                    cos ( radians( :Latitude ) )
                    * cos( radians( latitude ) )
                    * cos( radians( longitude ) - radians( :Longitude ) )
                    + sin ( radians( :Latitude ) )
                    * sin( radians( latitude ) )
                    )
                  ) AS Distance
                FROM Location
                    LEFT JOIN RegionLocation RL
                    ON RL.LocationId = Location.LocationId
                    LEFT JOIN Region R
                    ON R.RegionId = RL.RegionId
                WHERE R.UrlName= :UrlName
                ORDER BY Distance ;
        ";

        return Location::findBySql( $sql, [
            ':Latitude' => $lat,
            ':Longitude' => $long,
            ':UrlName' => $UrlName
        ] )->all();
    }

The problem is that when I run the query, I would like for that calculated "Distance" column to be included in the results. But, since there is no actual field in the database named "Distance", Yii2's ActiveRecord class will not add the field to the generated models.

I can get around it by creating a column in the Location table, named "Distance", but I'm hoping that there is a way to do this without making database changes.

My ultimate intention was to have the model return an array of Location objects, with each Location object having a "Distance" attribute. Then, the controller would produce a json feed using code similar to the following:

    public function actionGetStudiosByLatLong( $regionName = '', $latitude='', $longitude='') {
        \Yii::$app->response->format = 'json';

        return Location::getByRegionAndLatLong( $regionName, $latitude, $longitude );
    }

Answer 1

You should simply add this attribute to your class :

class Location extends \yii\db\ActiveRecord
{

    public function attributes()
    {
        // add distance attribute (will work for json output)
        return array_merge(parent::attributes(), ['Distance']);
    }

    // ...
}

Read more about Selecting extra fields.