Pathfinding in MySQL

user_6050962 0
Viewed 88

Note: I am using MySQL 5.5.36.

I'm working on a game design feature for a game that has rooms but does not have coordinates or relationships with other rooms in any way except exits. I've created a fiddle to illustrate a tiny snip of the data:

https://dbfiddle.uk/?rdbms=mysql_5.5&fiddle=9d4ebc91e10a67e187fb4a4b623728da

So in real application, I may be trying to connect a room to another room dozens of hops or more away. I'm not sure how I can best use the data I have to find a path from room A to room Z.

game-development path-finding mysql
1 Answers

Here's an idea:

SET @room := 555; -- setting variable for first room
SET @troom := 123; -- setting variable for last room

SELECT r1.room_num, r1.target_room, r2.target_room, r3.target_room, r4.target_room, r5.target_room, r6.target_room, r7.target_room
FROM room_map r1 
JOIN room_map r2 ON r1.target_room=r2.room_num
JOIN room_map r3 ON r2.target_room=r3.room_num
JOIN room_map r4 ON r3.target_room=r4.room_num
JOIN room_map r5 ON r4.target_room=r5.room_num
JOIN room_map r6 ON r5.target_room=r6.room_num
JOIN room_map r7 ON r6.target_room=r7.room_num
WHERE r1.room_num=@room -- using first room variable
AND r7.target_room=@troom -- using last room variable ;

In this fiddle: https://dbfiddle.uk/?rdbms=mysql_5.5&fiddle=6fb252fbc9495a34480e3f11175fad3e

This query will give you 3 rows as result - matching first and last room number condition. That is because you have a many-to-many relationship between the columns. What's next is for you to try on this query - add conditions that is feasible for your needs and update your question with new details/findings.

Also, see if this might help.

user_109106920
Related