CODEWITHSUNDEEP
mysqlsqllaraveleloquentspatial-query
Suraj Jadhav
Suraj Jadhav

Reputation: 49

Dynamic scope on related model and then sorting by distance

I have a table where I keep logs of locations of all users from where I need to grab the latest location of all the users and sort it by the distance from provided geometry point.

Locations table

id user_id location time
1 1 POINT timestamp
2 1 POINT timestamp
3 2 POINT timestamp
4 2 POINT timestamp

The result should be

id user_id location time
2 1 POINT timestamp
4 2 POINT timestamp

Then I need to sort users who are nearest to the provided point. I got how I can use MySQL Spatial function to get the distance but unable to sort with result above. I refereed this for getting latest location and this from another stack-overflow answer but having hard time using both together.

I appreciate any help and thanks in advance

Upvotes: 0

Views: 58

Answers (1)

Akina
Akina

Reputation: 42728

WITH cte AS ( SELECT source_table.*, 
                     ROW_NUMBER() OVER (PARTITION BY source_table.user_id 
                                        ORDER BY source_table.time DESC) rn
              FROM source_table )
SELECT cte.*
FROM cte
WHERE cte.rn = 1 
ORDER BY ST_Distance(cte.location, @specified_point)

Upvotes: 2

Related Questions