Yii2 : sort on dynamically calculated field in gridview

1
votes

I have a gridview in which I show the distance between the registered user's latitude & longitude (set in their profile) and the gridview line's latitude & longitude. I've created a helper class which gives me a function to return said distance My gridview for distance is this :

        [
            'format'=>'raw',
            'attribute'=>'distance (km)',
            'value'=> function ($data) {
                $latFrom = Yii::$app->user->identity->profile->city->latitude;
                $longFrom = Yii::$app->user->identity->profile->city->longitude;
                $latTo = $data->createdBy->profile->city->latitude;
                $longTo = $data->createdBy->profile->city->longitude;

                return GeoHelper::distance($latFrom, $longFrom, $latTo, $longTo);
            },
        ],

I'm trying to add sorting for distance in my gridview, but can't seem to find how to do so. I've tried adding a public $distance property to the search model and setting it as safe and then adding 

    $dataProvider->sort->attributes['distance'] = [
        'asc' => ['distance' => SORT_ASC],
        'desc' => ['distance' => SORT_DESC],
    ];

But no luck

Any ideas ?

3
gridviewyii2
How do you calculate distance? :) – mrateb
public static function distance($latitudeFrom, $longitudeFrom, $latitudeTo, $longitudeTo, $earthRadius = 6371) { // convert from degrees to radians $latFrom = deg2rad($latitudeFrom); $lonFrom = deg2rad($longitudeFrom); $latTo = deg2rad($latitudeTo); $lonTo = deg2rad($longitudeTo); $latDelta = $latTo - $latFrom; $lonDelta = $lonTo - $lonFrom; $angle = 2 * asin(sqrt(pow(sin($latDelta / 2), 2) + cos($latFrom) * cos($latTo) * pow(sin($lonDelta / 2), 2))); return round($angle * $earthRadius); } – Benoît
Distance property sounds like a good approach. What about it is not working? – Patrick
Just the sorting part :) I can't seem to get the header to be clickable to sort the lines by distance – Benoît
@Benoît are you using ActiveDataProvider as your data provider, if yes then I think it is quite impossible. Try to convert your ActiveDataProvider to ArrayDataProvider and it will work. Also, maybe you should not define property, just add it to the rules as "safe" and create getDistance() method in your model. – Sergei Beregov

3 Answers

2
votes

Of course this is not an issue, which could be solved that easily.

The value, that you want to sort is calculated in PHP, but DB has no clue about those values. As @Sergei Beregov said, you can use ArrayDataProvider, yeah, using that way it's achievable. However depending on users count it could be quite expensive (if you have a lot of users)

If your DB supports Geo functions, I would suggest computing distance values in DB itself. For instance in MySQL you can use ST_Distance() to compute the distance between two points and mix it with ActiveRecord relations in Yii2. I've written an experimental staff using Geo functions in my blog Spatial relations in Yii2. Sorry, it's in russian, but I hope it'll be helpful.

As long as you can compute the distance in DB, you can easily sort it in queue in a normal way like 'distance' => SORT_ASC

1
votes

I solved it thanks to Yerke's help ! In Post model, i created a new find function

public static function findWithDistance()
{
    $latitudeFrom = Yii::$app->user->identity->profile->city->latitude;
    $longFrom = Yii::$app->user->identity->profile->city->longitude;

    return parent::find()
        ->addSelect([
            "post.*",
            "round(6371 * acos( cos( radians(postcity.latitude) ) * cos( radians({$latitudeFrom}) ) * cos( radians({$longFrom}) - radians(postcity.longitude)) + sin(radians(postcity.latitude)) * sin( radians({$latitudeFrom})))) as distance",
        ])
        ->leftJoin('user', 'post.created_by = user.id')
        ->leftJoin('profile', 'user.id = profile.user_id')
        ->leftJoin('city postcity', 'profile.location = postcity.id');
}

which I call in PostSearch model if the user is logged in:

public function search($params)
{
    $query = (!Yii::$app->user->isGuest && Yii::$app->user->identity->profile->city!=null) ? Post::findWithDistance() : Post::find();
...
0
votes

In your gridview column you define the attribute as

'attribute'=>'distance (km)',

but in your provider you call it 'distance'

$dataProvider->sort->attributes['distance']

Try setting the column in your gridview like this:

'attribute'=>'distance',

If you need the 'km' to show up in the header, set the 'label' property of the column.