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 );
    }

Correct. But it's for a webservice that will eventually output the entire set as a json object. — TMorgan, Sep 28 '16 at 22:11

soju · Accepted Answer · 2016-09-28T22:48:50.107

7

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.

edited Sep 28 '16 at 22:48

answered Sep 28 '16 at 21:04

soju

24,068
3
63
66

That seems like a more elegant solution, but my controller needs to be able to produce a json feed with these objects (question has been edited to reflect that). When I set the response format to json and return an object with a public $Distance property, the property is omitted, because it isn't an attribute, like all the other fields. But when I json_encode the result set, I get an array of objects with nothing but Distance properties (the attributes are omitted). – TMorgan Sep 28 '16 at 22:19
1

That did add a distance field, but the value was empty for every record. It turns out that I needed to get rid of the Distance public property from the previous version of this answer. As written, it works correctly. – TMorgan Sep 29 '16 at 00:46

score 0 · Answer 2 · answered Sep 28 '16 at 21:05

Shortly after asking this question I found a slightly inelegant answer.

I had to override the populate record and hasAttribute methods in my Location model.

     /**
     * Adds "Distance" field to records if applicable. 
     */
    public static function populateRecord($record, $row)
    {
        $columns = static::getTableSchema()->columns;

        foreach ($row as $name => $value) {
            if (isset($columns[$name])) {
                $row[$name] = $columns[$name]->phpTypecast($value);
            }
        }
        parent::populateRecord($record, $row);

        if( isset( $row['Distance'] ) ) {
            $record->setAttribute( 'Distance', $row['Distance'] );
        }
    }

    // Needed to convince the powers that be that "Distance" really is a field.
    public function hasAttribute($name)

    {
        if($name == 'Distance') return true;
        return parent::hasAttribute($name);
    }

Once these were added, the Distance property began appearing in the models.

Add field calculated from SQL query to Yii2 ActiveRecord model

2 Answers2