I have this problem I think you may help me with.
P.S. I'm not sure how to call this, so if anyone finds a more appropriate title, please do edit.
Background
I'm making this application for searching bus transit lines.
Bus lines are a 3 digit number, and is unique and will never change.
The requirement is to be able to search for lines from stop A to stop B.
The user interface is already successful in hinting the user to only use valid stop names.
The requirement is to be able to display if a route has a direct line, and if not, display a 2-line and even 3-line combination.
Example:
I need to get from point A to point D. The program should show:
If there's a direct line A-D.
If not, display alternative, 2 line combos, such as A-C, C-D.
If there aren't any 2-line combos, search for 3-line combos: A-B, B-C, C-D.
Of course, the app should display bus line numbers, as well as when to switch buses.
What I have:
My database is structured as follows (simplified, actual database includes locations and times and whatnot):
+-----------+
| bus_stops |
+----+------+
| id | name |
+----+------+
+-------------------------------+
| lines_stops_relationship |
+-------------+---------+-------+
| bus_line | stop_id | order |
+-------------+---------+-------+
Where lines_stops_relationship describe a many-to-many relationship between the bus lines and the stops.
Order, signifies the order in which stops appear in a single line. Not all lines go back and forth, and order has meaning (point A with order 2 comes after point B with order 1).
The Problem
We find out if a line can pass through the route easily enough. Just search for a single line which passes through both points in the correct order.
How can I find if there's a 2/3 line combo? I was thinking to search for a line which matches the source stop, and one for the destination stop, and see if I can get a common stop between them, where the user can switch buses. How do I remember that stop?
3 line combo is even trickier, I find a line for the source, and a line for the destination, and then what? Search for a line which has 2 stops I guess, but again, How do I remember the stops?
tl;dr
How do I remember results from a query to be able to use it again? I'm hoping to achieve this in a single query (for each, a query for 1-line routes, a query for 2, and a query for 3-line combos).
Note: I don't mind if someone suggests a completely different approach than what I have, I'm open to any solutions.
Will award any assistance with a cookie and an upvote. Thanks in advance!
