Sort by calculated field

Hello all,

I’m building a classifieds site and would like to be able to display the distance from the person who created the ad to the logged in user.

I’ll simplify and only show relevant fields but basically, my database is set as follows :


Ad table :

- id

- createdBy (user_id)


User table :

- id


Profile table :

- user_id

- city_id


City table :

- id

- latitude

- longitude

I’ve already figured out I can compute the distance in mysql :


SELECT ( 6371 * acos( cos( radians(city1.latitude) ) * cos( radians(city2.latitude) ) * cos( radians(city2.longitude) - radians(city1.longitude)) + sin(radians(city1.latitude)) * sin( radians(city2.latitude)))) AS distance 

FROM city city1 join city city2 

WHERE city1.id = 1 and city2.id = 2 

now i need to get that into the ActiveDataProvider somehow.

I’ve already seen this post :

https://blog.keo.kz/2017/05/19/yii2-spatial-relations/

But can’t seem to get it working for my case.

Does anyone know how to get that done ?

Thanks in advance!!

Hi Benn,

Take a look at the following section of the guide and I hope you will get a clue.

Guide > ActiveRecord > Selecting extra fields (https://www.yiiframework.com/doc/guide/2.0/en/db-active-record#selecting-extra-fields)