How to create Mapping of a connected list

šŸŽ¤ General Discussion iOS
1

0 down vote favorite

I have a list of, or we can say a chain of, items in a database table, which are connected with each other like:

>  item_id     connected_item  item_cost            item_type
>      100       200              12                anything
>      200       300              11                anything
>      300       400              14                anything
>      400       500              19                anything

ā€¦ so on

How can I know that if I input 200 it will give me both connections to it, which are 100 and 300. And also, the list of connected items between two items e.g. if first item=100, last item=300 then all items are 100,200,300. And the approach should also work bottom to top e.g. if first item=300, last item=100 then all items are 300,200,100 including cost and type.
It is a SQLite database, there is no problem in loading data I loaded the given data in a NSArray from ItemsDatabase class which is Database manager class.And a object class for this table name 'Items' which has properties having above column names just like the tutorial https://www.raywenderlich.com/913/sqlite-tutorial-for-ios-making-our-app. Now i am just looking for logic to get the solution of my above given problem.
If any further elaboration needed let me know.

Let me define myself more what i am trying to do here

   STAND_ID_FK	CONNECTED_STAND	DISTANCE_BETWEEN_STAND	TIME_BETWEEN_STAND
    101	             102	          1	                     2
    102	             103	        0.8	                   1.5
    103	             104	        1.4	                     2 
    104	             105	        1.007	                    2
    105		     106                1.264	                     2
    106		     107                0.8	                   1.5

this is the database table which is actually a bidirectional graph each bus stand is a vertex . So from here i have to do following things

  1. Get all connected stands with any stand.
  2. Get total time and distance between two stands.
  3. Get a list of stands which contains stands between two stands Foe eg. if source=101 and destination = 105 list of path contains 101,102,103,104,105.
    So i have to create an adjacency list and traverse it when user input any source and destination. Please look at it I am fed up solving the logic.
2

Try the following queries. Iā€™m only showing the query statements here.

This first query will give you all the records where the connected_item is the one you want (xxx).

SELECT item_id, item_cost, item_type
FROM myDatabase
WHERE connected_item = xxx

For item 200 and the info you listed, the results of that query should be:

item_id     item_cost            item_type
   100         12                anything

This next query will give you all the records where the item_id is the one you want.

SELECT connected_item, item_cost, item_type
FROM myDatabase
WHERE item_id = xxx

The results of that query should be:

connected_item    item_cost            item_type
    300              11                anything

To get both sets of info, you can combine those two queries with a UNION.

<query 1>
UNION
<query 2>

Your second request can be handled in the same manner just by changing the WHERE clause to pick the desired range of items.

Have fun!

1 Like
3

It is working well. I was doing all this operation after loading data in class object. But here it is all done by sql. Thanks.