Learn cypher with scenarion ; train book system

General Newbie Questions
1

// Create Trains
CREATE (tX1:Train {ids: 1, name: "Train X1"}),
(tX2:Train {ids: 2, name: "Train X2"});

// Create Wagons for Train X1
MATCH (tX1:Train {ids: 1})
FOREACH (i IN range(1, 4) |
CREATE (w:Wagon {ids: i, class: CASE WHEN i < 3 THEN 'Executive' WHEN i = 3 THEN 'Business' ELSE 'Economy' END})-[:BELONGS_TO]->(tX1)
);

// Create Wagons for Train X2
MATCH (tX2:Train {ids: 2})
FOREACH (i IN range(5, 8) |
CREATE (w:Wagon {ids: i, class: CASE WHEN i < 7 THEN 'Executive' WHEN i = 7 THEN 'Business' ELSE 'Economy' END})-[:BELONGS_TO]->(tX2)
);

// Create Seats for Wagons
MATCH (w:Wagon)
FOREACH (j IN range(1, 40) |
CREATE (s:Seat {ids: j, state: CASE WHEN j <= 4 THEN 'blocked' ELSE 'available' END})-[:PART_OF]->(w)
);

// Create Stations
FOREACH (i IN range(1, 20) |
CREATE (:Station {ids: i, name: "Station " + i})
);

// Create Routes
MATCH (s1:Station {ids: 1}), (s2:Station {ids: 2}), (s3:Station {ids: 3}), (s4:Station {ids: 4}),
(s5:Station {ids: 5}), (s6:Station {ids: 6}), (s7:Station {ids: 7}), (s8:Station {ids: 8}),
(s9:Station {ids: 9}), (s10:Station {ids: 10}), (s11:Station {ids: 11}), (s12:Station {ids: 12}),
(s13:Station {ids: 13}), (s14:Station {ids: 14}), (s15:Station {ids: 15}), (s16:Station {ids: 16}),
(s17:Station {ids: 17}), (s18:Station {ids: 18}), (s19:Station {ids: 19}), (s20:Station {ids: 20})
CREATE (rA:Route {ids: 1, name: "Route A"})
-[:CONNECTS]->(s1)-[:TO {distance: 10}]->(s2)
-[:TO {distance: 10}]->(s3)
-[:TO {distance: 10}]->(s4),

  (rB:Route {ids: 2, name: "Route B"})
  -[:CONNECTS]->(s2)-[:TO {distance: 10}]->(s3)
  -[:TO {distance: 10}]->(s4)
  -[:TO {distance: 10}]->(s5)
  -[:TO {distance: 10}]->(s6)
  -[:TO {distance: 10}]->(s7)
  -[:TO {distance: 10}]->(s8)
  -[:TO {distance: 10}]->(s9),

  (rC:Route {ids: 3, name: "Route C"})
  -[:CONNECTS]->(s4)-[:TO {distance: 10}]->(s5)
  -[:TO {distance: 10}]->(s6)
  -[:TO {distance: 10}]->(s7)
  -[:TO {distance: 10}]->(s8)
  -[:TO {distance: 10}]->(s9)
  -[:TO {distance: 10}]->(s10)
  -[:TO {distance: 10}]->(s11)
  -[:TO {distance: 10}]->(s12)
  -[:TO {distance: 10}]->(s13)
  -[:TO {distance: 10}]->(s14)
  -[:TO {distance: 10}]->(s15)
  -[:TO {distance: 10}]->(s16)
  -[:TO {distance: 10}]->(s17)
  -[:TO {distance: 10}]->(s18)
  -[:TO {distance: 10}]->(s19)
  -[:TO {distance: 10}]->(s20),

  (rD:Route {ids: 4, name: "Route D"})
  -[:CONNECTS]->(s2)-[:TO {distance: 10}]->(s3)
  -[:TO {distance: 10}]->(s4)
  -[:TO {distance: 10}]->(s5)
  -[:TO {distance: 10}]->(s6)
  -[:TO {distance: 10}]->(s7)
  -[:TO {distance: 10}]->(s8)
  -[:TO {distance: 10}]->(s9)
  -[:TO {distance: 10}]->(s10)
  -[:TO {distance: 10}]->(s11)
  -[:TO {distance: 10}]->(s12)
  -[:TO {distance: 10}]->(s13)
  -[:TO {distance: 10}]->(s14)
  -[:TO {distance: 10}]->(s15),

  (rE:Route {ids: 5, name: "Route E"})
  -[:CONNECTS]->(s10)-[:TO {distance: 10}]->(s11)
  -[:TO {distance: 10}]->(s12)
  -[:TO {distance: 10}]->(s13)
  -[:TO {distance: 10}]->(s14)
  -[:TO {distance: 10}]->(s15)
  -[:TO {distance: 10}]->(s16)
  -[:TO {distance: 10}]->(s17)
  -[:TO {distance: 10}]->(s18);

// Create Fares
CREATE (fare1:Fare {ids: 1, class: "Economy", price: 2200}),
(fare2:Fare {ids: 2, class: "Business", price: 3100}),
(fare3:Fare {ids: 3, class: "Executive", price: 15000}),
(fare4:Fare {ids: 4, class: "Economy", price: 3500}),
(fare5:Fare {ids: 5, class: "Business", price: 4400}),
(fare6:Fare {ids: 6, class: "Executive", price: 16000}),
(fare7:Fare {ids: 7, class: "Economy", price: 4300}),
(fare8:Fare {ids: 8, class: "Business", price: 5600}),
(fare9:Fare {ids: 9, class: "Executive", price: 17000}),
(fare10:Fare {ids: 10, class: "Economy", price: 5100}),
(fare11:Fare {ids: 11, class: "Business", price: 6100}),
(fare12:Fare {ids: 12, class: "Executive", price: 18000}),
(fare13:Fare {ids: 13, class: "Economy", price: 6600}),
(fare14:Fare {ids: 14, class: "Business", price: 7500}),
(fare15:Fare {ids: 15, class: "Executive", price: 19000});

// Create Trips with Associated Routes and Prices
MATCH (tX1:Train {ids: 1}), (tX2:Train {ids: 2}),
(rA:Route {ids: 1}), (rB:Route {ids: 2}), (rC:Route {ids: 3}), (rD:Route {ids: 4}), (rE:Route {ids: 5}),
(fare1:Fare {ids: 1}), (fare2:Fare {ids: 2}), (fare3:Fare {ids: 3}),
(fare4:Fare {ids: 4}), (fare5:Fare {ids: 5}), (fare6:Fare {ids: 6}),
(fare7:Fare {ids: 7}), (fare8:Fare {ids: 8}), (fare9:Fare {ids: 9}),
(fare10:Fare {ids: 10}), (fare11:Fare {ids: 11}), (fare12:Fare {ids: 12}),
(fare13:Fare {ids: 13}), (fare14:Fare {ids: 14}), (fare15:Fare {ids: 15})
CREATE (trip1:Trip {ids: 1})-[:USES]->(tX1)-[:ON]->(rA)-[:HAS_FARE]->(fare1),
(trip2:Trip {ids: 2})-[:USES]->(tX1)-[:ON]->(rB)-[:HAS_FARE]->(fare2),
(trip3:Trip {ids: 3})-[:USES]->(tX1)-[:ON]->(rC)-[:HAS_FARE]->(fare3),
(trip4:Trip {ids: 4})-[:USES]->(tX1)-[:ON]->(rD)-[:HAS_FARE]->(fare4),
(trip5:Trip {ids: 5})-[:USES]->(tX2)-[:ON]->(rE)-[:HAS_FARE]->(fare5),
(trip6:Trip {ids: 6})-[:USES]->(tX2)-[:ON]->(rC)-[:HAS_FARE]->(fare6),
(trip7:Trip {ids: 7})-[:USES]->(tX2)-[:ON]->(rD)-[:HAS_FARE]->(fare7),
(trip8:Trip {ids: 8})-[:USES]->(tX2)-[:ON]->(rA)-[:HAS_FARE]->(fare8),
(trip9:Trip {ids: 9})-[:USES]->(tX1)-[:ON]->(rC)-[:HAS_FARE]->(fare9),
(trip10:Trip {ids: 10})-[:USES]->(tX1)-[:ON]->(rD)-[:HAS_FARE]->(fare10),
(trip11:Trip {ids: 11})-[:USES]->(tX1)-[:ON]->(rB)-[:HAS_FARE]->(fare11),
(trip12:Trip {ids: 12})-[:USES]->(tX2)-[:ON]->(rE)-[:HAS_FARE]->(fare12),
(trip13:Trip {ids: 13})-[:USES]->(tX2)-[:ON]->(rC)-[:HAS_FARE]->(fare13),
(trip14:Trip {ids: 14})-[:USES]->(tX2)-[:ON]->(rD)-[:HAS_FARE]->(fare14),
(trip15:Trip {ids: 15})-[:USES]->(tX2)-[:ON]->(rB)-[:HAS_FARE]->(fare15);

//Verify the routes and their connections:
// this query always eat alotof memory hence error returned
MATCH (r:Route)-[:CONNECTS]->(s:Station)-[:TO*]->(s2:Station)
WHERE r.ids IN [1]
RETURN r.name AS Route, collect(s2.name) AS Stations
;

// find available seats
MATCH (trip1:Trip {ids: 1})-[:USES]->(tX1:Train {ids: 1})-[:ON]->(rA:Route {ids: 1})-[:CONNECTS]->(:Station {ids: 1})-[:TO]->(:Station {ids: 3}),
(seat:Seat {state: "available"})
RETURN count(seat) AS available_seats;

// find available seats
MATCH (trip1:Trip {ids: 1})-[:USES]->(tX1:Train {ids: 1}),
(trip1)-[:ON]->(rA:Route {ids: 1})-[:CONNECTS]->(:Station {ids: 1})-[:TO]->(:Station {ids: 3}),
(seat:Seat {state: "available"})
RETURN count(seat) AS available_seats;

// find available seats
MATCH (trip1:Trip {ids: 1})-[:USES]->(tX1:Train {ids: 1}),
(trip1)-[:ON]->(rA:Route {ids: 1})-[:CONNECTS]->(:Station {ids: 1})-[:TO]->(:Station {ids: 3}),
(seat:Seat {state: "available"})-[:PART_OF]->(wagon:Wagon)-[:BELONGS_TO]->(tX1)
RETURN count(seat) AS available_seats;

// Find Available Seats for a Specific Trip Segment
MATCH (trip1:Trip {ids: 1})-[:USES]->(tX1:Train),
(trip1)-[:ON]->(rA:Route)-[:CONNECTS]->(s1:Station {ids: 1})-[:TO*1..]->(s3:Station {ids: 3}),
(seat:Seat {state: "available"})-[:PART_OF]->(wagon:Wagon)-[:BELONGS_TO]->(tX1)
RETURN count(seat) AS available_seats;

those "find available seats" queries are return 0; am I missing something?

2

I see some issues with your CREATE (rA:Route Cypher. I am working on it and will send you the fully query soon.

3 
// Create Trains
CREATE (tX1:Train {ids: 1, name: "Train X1"}),
(tX2:Train {ids: 2, name: "Train X2"});

// Create Wagons for Train X1
MATCH (tX1:Train {ids: 1})
FOREACH (i IN range(1, 4) |
CREATE (w:Wagon {ids: i, class: CASE WHEN i < 3 THEN 'Executive' WHEN i = 3 THEN 'Business' ELSE 'Economy' END})-[:BELONGS_TO]->(tX1)
);

// Create Wagons for Train X2
MATCH (tX2:Train {ids: 2})
FOREACH (i IN range(5, 8) |
CREATE (w:Wagon {ids: i, class: CASE WHEN i < 7 THEN 'Executive' WHEN i = 7 THEN 'Business' ELSE 'Economy' END})-[:BELONGS_TO]->(tX2)
);

// Create Seats for Wagons
MATCH (w:Wagon)
FOREACH (j IN range(1, 40) |
CREATE (s:Seat {ids: j, state: CASE WHEN j <= 4 THEN 'blocked' ELSE 'available' END})-[:PART_OF]->(w)
);

// Create Stations
FOREACH (i IN range(1, 20) |
CREATE (:Station {ids: i, name: "Station " + i})
);

// Create Routes
MATCH (s1:Station {ids: 1}), (s2:Station {ids: 2}), (s3:Station {ids: 3}), (s4:Station {ids: 4}),
(s5:Station {ids: 5}), (s6:Station {ids: 6}), (s7:Station {ids: 7}), (s8:Station {ids: 8}),
(s9:Station {ids: 9}), (s10:Station {ids: 10}), (s11:Station {ids: 11}), (s12:Station {ids: 12}),
(s13:Station {ids: 13}), (s14:Station {ids: 14}), (s15:Station {ids: 15}), (s16:Station {ids: 16}),
(s17:Station {ids: 17}), (s18:Station {ids: 18}), (s19:Station {ids: 19}), (s20:Station {ids: 20})

with s1, s2, s3, s4, s5, s6, s7, s8, s9, s10, s11, s12, s13, s14, s15, s16, s17, s18, s19, s20


MERGE (rA:Route {ids: 1, name: "Route A"})
-[:CONNECTS {route: "A"}]->(s1)-[:TO {distance: 10, route: "A"}]->(s2)
-[:TO {distance: 10, route: "A"}]->(s3)
-[:TO {distance: 10, route: "A"}]->(s4)

MERGE (rB:Route {ids: 2, name: "Route B"})
  -[:CONNECTS {route: "B"}]->(s2)-[:TO {distance: 10, route: "B"}]->(s3)
  -[:TO {distance: 10, route: "B"}]->(s4)
  -[:TO {distance: 10, route: "B"}]->(s5)
  -[:TO {distance: 10, route: "B"}]->(s7)
  -[:TO {distance: 10, route: "B"}]->(s8)
  -[:TO {distance: 10, route: "B"}]->(s9)

MERGE (rC:Route {ids: 3, name: "Route C"})
  -[:CONNECTS {route: "C"}]->(s4)-[:TO {distance: 10, route: "C"}]->(s5)
  -[:TO {distance: 10, route: "C"}]->(s6)
  -[:TO {distance: 10, route: "C"}]->(s7)
  -[:TO {distance: 10, route: "C"}]->(s8)
  -[:TO {distance: 10, route: "C"}]->(s9)
  -[:TO {distance: 10, route: "C"}]->(s10)
  -[:TO {distance: 10, route: "C"}]->(s11)
  -[:TO {distance: 10, route: "C"}]->(s12)
  -[:TO {distance: 10, route: "C"}]->(s13)
  -[:TO {distance: 10, route: "C"}]->(s14)
  -[:TO {distance: 10, route: "C"}]->(s15)
  -[:TO {distance: 10, route: "C"}]->(s16)
  -[:TO {distance: 10, route: "C"}]->(s17)
  -[:TO {distance: 10, route: "C"}]->(s18)
  -[:TO {distance: 10, route: "C"}]->(s19)
  -[:TO {distance: 10, route: "C"}]->(s20)

MERGE (rD:Route {ids: 4, name: "Route D"})
  -[:CONNECTS {route: "D"}]->(s2)-[:TO {distance: 10, route: "D"}]->(s3)
  -[:TO {distance: 10, route: "D"}]->(s4)
  -[:TO {distance: 10, route: "D"}]->(s5)
  -[:TO {distance: 10, route: "D"}]->(s6)
  -[:TO {distance: 10, route: "D"}]->(s7)
  -[:TO {distance: 10, route: "D"}]->(s8)
  -[:TO {distance: 10, route: "D"}]->(s9)
  -[:TO {distance: 10, route: "D"}]->(s10)
  -[:TO {distance: 10, route: "D"}]->(s11)
  -[:TO {distance: 10, route: "D"}]->(s12)
  -[:TO {distance: 10, route: "D"}]->(s13)
  -[:TO {distance: 10, route: "D"}]->(s14)
  -[:TO {distance: 10, route: "D"}]->(s15)

MERGE (rE:Route {ids: 5, name: "Route E"})
  -[:CONNECTS {route: "E"}]->(s10)-[:TO {distance: 10, route: "E"}]->(s11)
  -[:TO {distance: 10, route: "E"}]->(s12)
  -[:TO {distance: 10, route: "E"}]->(s13)
  -[:TO {distance: 10, route: "E"}]->(s14)
  -[:TO {distance: 10, route: "E"}]->(s15)
  -[:TO {distance: 10, route: "E"}]->(s16)
  -[:TO {distance: 10, route: "E"}]->(s17)
  -[:TO {distance: 10, route: "E"}]->(s18);
  
// Create Fares
MERGE (fare1:Fare {ids: 1, class: "Economy", price: 2200})
MERGE (fare2:Fare {ids: 2, class: "Business", price: 3100})
MERGE (fare3:Fare {ids: 3, class: "Executive", price: 15000})
MERGE (fare4:Fare {ids: 4, class: "Economy", price: 3500})
MERGE (fare5:Fare {ids: 5, class: "Business", price: 4400})
MERGE (fare6:Fare {ids: 6, class: "Executive", price: 16000})
MERGE (fare7:Fare {ids: 7, class: "Economy", price: 4300})
MERGE (fare8:Fare {ids: 8, class: "Business", price: 5600})
MERGE (fare9:Fare {ids: 9, class: "Executive", price: 17000})
MERGE (fare10:Fare {ids: 10, class: "Economy", price: 5100})
MERGE (fare11:Fare {ids: 11, class: "Business", price: 6100})
MERGE (fare12:Fare {ids: 12, class: "Executive", price: 18000})
MERGE (fare13:Fare {ids: 13, class: "Economy", price: 6600})
MERGE (fare14:Fare {ids: 14, class: "Business", price: 7500})
MERGE (fare15:Fare {ids: 15, class: "Executive", price: 19000});

// Create Trips with Associated Routes and Prices

MATCH (tX1:Train {ids: 1}), (tX2:Train {ids: 2}),
(rA:Route {ids: 1}), (rB:Route {ids: 2}), (rC:Route {ids: 3}), (rD:Route {ids: 4}), (rE:Route {ids: 5}),
(fare1:Fare {ids: 1}), (fare2:Fare {ids: 2}), (fare3:Fare {ids: 3}),
(fare4:Fare {ids: 4}), (fare5:Fare {ids: 5}), (fare6:Fare {ids: 6}),
(fare7:Fare {ids: 7}), (fare8:Fare {ids: 8}), (fare9:Fare {ids: 9}),
(fare10:Fare {ids: 10}), (fare11:Fare {ids: 11}), (fare12:Fare {ids: 12}),
(fare13:Fare {ids: 13}), (fare14:Fare {ids: 14}), (fare15:Fare {ids: 15})

MERGE (trip1:Trip {ids: 1})-[:USES]->(tX1)-[:ON]->(rA)-[:HAS_FARE]->(fare1)
MERGE (trip2:Trip {ids: 2})-[:USES]->(tX1)-[:ON]->(rB)-[:HAS_FARE]->(fare2)
MERGE (trip3:Trip {ids: 3})-[:USES]->(tX1)-[:ON]->(rC)-[:HAS_FARE]->(fare3)
MERGE (trip4:Trip {ids: 4})-[:USES]->(tX1)-[:ON]->(rD)-[:HAS_FARE]->(fare4)
MERGE (trip5:Trip {ids: 5})-[:USES]->(tX2)-[:ON]->(rE)-[:HAS_FARE]->(fare5)
MERGE (trip6:Trip {ids: 6})-[:USES]->(tX2)-[:ON]->(rC)-[:HAS_FARE]->(fare6)
MERGE (trip7:Trip {ids: 7})-[:USES]->(tX2)-[:ON]->(rD)-[:HAS_FARE]->(fare7)
MERGE (trip8:Trip {ids: 8})-[:USES]->(tX2)-[:ON]->(rA)-[:HAS_FARE]->(fare8)
MERGE (trip9:Trip {ids: 9})-[:USES]->(tX1)-[:ON]->(rC)-[:HAS_FARE]->(fare9)
MERGE (trip10:Trip {ids: 10})-[:USES]->(tX1)-[:ON]->(rD)-[:HAS_FARE]->(fare10)
MERGE (trip11:Trip {ids: 11})-[:USES]->(tX1)-[:ON]->(rB)-[:HAS_FARE]->(fare11)
MERGE (trip12:Trip {ids: 12})-[:USES]->(tX2)-[:ON]->(rE)-[:HAS_FARE]->(fare12)
MERGE (trip13:Trip {ids: 13})-[:USES]->(tX2)-[:ON]->(rC)-[:HAS_FARE]->(fare13)
MERGE (trip14:Trip {ids: 14})-[:USES]->(tX2)-[:ON]->(rD)-[:HAS_FARE]->(fare14)
MERGE (trip15:Trip {ids: 15})-[:USES]->(tX2)-[:ON]->(rB)-[:HAS_FARE]->(fare15);

Screenshot 2024-07-06 at 3.54.43 PM
Screenshot 2024-07-06 at 3.54.43 PM1790×1252 142 KB

match (a:Route)
with a
CALL
{
    with a 
    match p = (a:Route)-[*..7]->(d:Station)
where a.name = "Route A" and all(rel in relationships(p) WHERE rel.route = "A")
return a.name as route, collect(distinct d.name) as sts

UNION

with a 
    match p = (a:Route)-[*..7]->(d:Station)
where a.name = "Route B" and all(rel in relationships(p) WHERE rel.route = "B")
return a.name as route, collect(distinct d.name) as sts

UNION

with a 
    match p = (a:Route)-[*..7]->(d:Station)
where a.name = "Route C" and all(rel in relationships(p) WHERE rel.route = "C")
return a.name as route, collect(distinct d.name) as sts

UNION

with a 
    match p = (a:Route)-[*..7]->(d:Station)
where a.name = "Route D" and all(rel in relationships(p) WHERE rel.route = "D")
return a.name as route, collect(distinct d.name) as sts

UNION

with a 
    match p = (a:Route)-[*..7]->(d:Station)
where a.name = "Route E" and all(rel in relationships(p) WHERE rel.route = "E")
return a.name as route, collect(distinct d.name) as sts

}

return route, sts

Screenshot 2024-07-06 at 4.33.04 PM
Screenshot 2024-07-06 at 4.33.04 PM1236×376 73.9 KB

4

usually user will search based on origin station to destination station to get which train, trip, fare and available seats, trying to find anything in between station with this query, but only route displayed...

MATCH path=(s:Station {ids: 3})-[*..1000]->(s2:Station {ids: 13})
WITH nodes(path) AS nodes
UNWIND nodes AS node
WITH distinct node
RETURN node;