Cannot fix Relationship Issue in Spring Boot with Neo4j

[sngermiyanoglu]

I have a problem about saving route with City in Spring Boot. I think my issue is located at CityRepository and RouteRepository.

public interface CityRepository extends Neo4jRepository<City,UUID> {

    @Query("MATCH (city:City) OPTIONAL MATCH (city)-[r:ROUTES]->(route:Route) RETURN city, collect(r), collect(route)")
    List<City> listAll();

    @Query("MATCH (city:City {id: $cityId}) OPTIONAL MATCH (city)-[r:ROUTES]->(route:Route) RETURN city, collect(r), collect(route)")
    City getById(UUID cityId);

    @Query("MATCH (city:City {name: $cityName}) RETURN city")
    City getByCityName(String cityName);

    @Query("CREATE (city:City {id: randomUUID(), name: $cityName}) RETURN city")
    City saveCity(String cityName);

    @Query("MATCH (city:City {id: $cityId}) SET city.name = $cityName RETURN city")
    City updateCity(UUID cityId, String cityName);

    @Query("MATCH (city:City {id: $cityId}) DELETE city")
    void deleteCity(UUID cityId);
}

public interface RouteRepository extends Neo4jRepository<Route,UUID> {

    @Query("MATCH (city:City {id: $cityId})-[:ROUTES]->(route:Route) RETURN route")
    List<Route> listAllByCityId(UUID cityId);

    @Query("MATCH (route:Route {id: $routeId}) RETURN route")
    Route getById(UUID routeId);

    @Query("CREATE (city:City {id: $cityId})-[:ROUTES]->(route:Route {id: randomUUID(), from: $from, destination: $destination, departureTime: $departureTime," +
            "arriveTime: $arriveTime, duration: $duration}) " +
            "RETURN route")
    Route saveRoute(UUID cityId, String from, String destination, String departureTime,
                    String arriveTime, double duration);

    @Query("MATCH (city:City {id: $cityId})-[:ROUTES]->(route:Route {id: $routeId}) " +
            "SET route.from = $from, route.destination = $destination,route.departureTime = $departureTime," +
            "route.arriveTime = $arriveTime, route.duration = $duration RETURN route")
    Route updateRoute(UUID cityId, UUID routeId, String from, String destination,String departureTime,
                      String arriveTime,double duration);

    @Query("MATCH (city:City {id: $cityId})-[r:ROUTES]->(route:Route {id: $routeId}) DELETE r, route")
    void deleteRoute(UUID cityId, UUID routeId);
}

After calling saveRoute of RouteRepository and listAll of CityRepository , I get this result shown below.

[

    {

        "id": "2c83b1c1-7f62-4584-b271-d40986ea6414",

        "name": "London",

        "routes": []

    },

    {

        "id": "2c83b1c1-7f62-4584-b271-d40986ea6414",

        "name": null,

        "routes": [

            {

                "id": "8c694742-8311-4b1b-a8b6-0724b019d002",

                "from": "London",

                "destination": "Berlin",

                "departureTime": "9:00",

                "arriveTime": "11:30",

                "duration": 2.5

            }

        ]

    }

]

Here is my stackoverflow question : Question Link 

Here is my github repository : Project Link 

[sngermiyanoglu · Accepted solution]

Here is my solution

@Query("MATCH (city:City {id: $cityId}) " +
        "MERGE (city)-[:ROUTES]->(route:Route {id: randomUUID(), from: $from, destination: $destination, " +
        "departureTime: $departureTime," +
        "arriveTime: $arriveTime, duration: $duration}) " +
        "RETURN route")
Route saveRoute(UUID cityId, String from, String destination, String departureTime,
                String arriveTime, double duration);

How can I write this query without writing its custom query?

[bennu_neo]

Hi @sngermiyanoglu 

You should probably MERGE instead of CREATE, shouldn't you?

[sngermiyanoglu]

I just tried it but it didn't help me solve my issue.

Is it possible to look through my City Repository and RouteRepository?

[bennu_neo]

What did you try? Can you share your new MERGE statements?

saveCity should look like:

@Query("MERGE(city:City {name: $cityName} ON CREATE SET city.id = randomUUID(),) RETURN city")
    City saveCity(String cityName);

 What about your 

updateRoute

?

[sngermiyanoglu]

I think there is no issue in CityRepository. After calling a saveCity method of CityRepository, the city is saved as a new entity as I have to use CREATE. After that, I call a saveRoute method of RouteRepository, the route is saved. When I call a listAll method of CityRepository, I get this result shown in my post. 

[bennu_neo]

It's important to properly MERGE on the name. Have you call the *saveCity* several times with the same name?

About *saveRoute*, have you check how does your DB looks after this call? Is the route created in the right place? Your save route should look like

@Query("MATCH (city:City {id: $cityId}) WITH city CREATE(city)-[:ROUTES]->(route:Route {id: randomUUID(), from: $from, destination: $destination, departureTime: $departureTime," +
        "arriveTime: $arriveTime, duration: $duration}) " +
        "RETURN route")
Route saveRoute(UUID cityId, String from, String destination, String departureTime,
                String arriveTime, double duration);

 PS: It's up to your implementation, but you can easily do all of this without customQueries. Give it a try!

[sngermiyanoglu · Accepted solution]

Here is my solution

@Query("MATCH (city:City {id: $cityId}) " +
        "MERGE (city)-[:ROUTES]->(route:Route {id: randomUUID(), from: $from, destination: $destination, " +
        "departureTime: $departureTime," +
        "arriveTime: $arriveTime, duration: $duration}) " +
        "RETURN route")
Route saveRoute(UUID cityId, String from, String destination, String departureTime,
                String arriveTime, double duration);

How can I write this query without writing its custom query?

[sngermiyanoglu]

I checked other custom queries and I think all these work properly.

Is it possible to check it again?

I maybe ignore something else.

Please let me know.

[bennu_neo · Accepted solution]

Hi @sngermiyanoglu ,

Well,

Adding a constructor for Route like 

public Route(String from, String destination, String departureTime, String arriveTime, Double duration) {
        this.id = UUID.randomUUID();
        this.from = from;
        this.destination = destination;
        this.departureTime = departureTime;
        this.arriveTime = arriveTime;
        this.duration = duration;
    }

Inside of RouteServiceImpl you can:

1. Add a: 

private final CityRepository cityRepository;

2. Change save into:

public Route save(UUID cityId, RouteDTO routeDTO) {

    String from = routeDTO.getFrom();
    String destination = routeDTO.getDestination();
    String departureTime = routeDTO.getDepartureTime();
    String arriveTime = routeDTO.getArriveTime();
    double duration = routeDTO.getDuration();
    City city = cityRepository.getById(cityId);

    Route route = new Route(from,destination,departureTime, arriveTime,duration);
    city.getRoutes().add(route);
    cityRepository.save(city);
    return route;
}

So you are executing the save without even creating any custom queries. 

[sngermiyanoglu]

Thank you for your response.

What about my another issue? I cannot fix it?

Here is the link : Question Link