Efficient pattern for access the latest data in many-to-many relationship?

Question

I have two objects with MTM relationship

User
------------
id
name

Location
------------
id
latitude
longitude
date_created

userlocation
------------
id
fk_user_id
fk_location_id

When accessing the data, I usually just need to know the latest location associated with a user but I do want to store all of user's previous locations. The write to read ratio is about 1:10

I believe this is a pretty common persistence pattern, I wonder what's the most standard way to approach it? Is it to do a JOIN and form a new View?

You can't, since the userlocation table has no usable timestamp. — wildplasser, Aug 28 '16 at 23:30

score 0 · Answer 1 · answered Aug 29 '16 at 02:29

The pattern would be JOIN table userlocation on itself:

SELECT t1.* 
    FROM userlocation AS t1 
    LEFT JOIN userlocation AS t2 ON t1.fk_user_id = t2.fk_user_id AND t1.fk_location_id = t2.fk_location_id AND t1.id> t2.id 
WHERE 
    t2.id IS null

Basically what happens there is that you try to join to every row in table userlocation another row from the same table but with smaller key. If it is not first record for fk_user_id/fk_location_id combination than there will be some other record inserted with smaller key. If it is smallest key in group than jointed column will return NULL as there are no previous keys.

At this stage you just filter out rows from first table where expected column with smaller key in NULL. Having this you can easily join needed information on particular user or location to extend query as you need it.

score 0 · Answer 2 · edited May 23 '17 at 12:22

I think your question is already answered here: SQL join: selecting the last records in a one-to-many relationship

There are attendant issues with that approach as the userlocation table gets very large.

But personally, I would have approached it by creating an extra column in User called last_userlocation_id that will be set to the value of id (autoincrement) when a new row is added to userlocation (during the WRITE)

If you do that then your SQL for READ is greatly simplified to:

SELECT User.name, Location.latitude, Location.longitude FROM
User 
LEFT JOIN userlocation ON userlocation.id = User.last_userlocation_id
LEFT JOIN Location ON Location.id = userlocation.fk_location_id

The reason for using a LEFT JOIN instead of an INNER JOIN is to cater for Users that don't have any recorded location yet. In that case latitude, and longitude will be NULL

Efficient pattern for access the latest data in many-to-many relationship?

2 Answers2