[Neo4j] Example for using unwind and shortestPath in Cypher

Scenario
以下是 Neo4j graph database 中的資料，假設我想：
(1) 找出某段時間，看診次數超過 3 次的 patients
(2) 找出 patients 間，關係長度為 2 的 patients

How-To

// 找出某段時間，看診次數超過 3 次的 patients
match (pat:Patient)-[r:VISIT]->(doc:Doctor)
where r.date >= "20180401" and r.date <= "20180430"
with pat, count(r.date) as count
where count >= 3

// 將 patient collection UNWIND 成一筆一筆資料
with collect(pat) as ps
unwind ps as p1
unwind ps as p2

// 找出 patients 間，關係長度為 2 的 patients
match  p = shortestPath((p1)-[*]-(p2))
where p1.name <> p2.name
with p1.name as patient1, p2.name as patient2, length(p) as length
where length = 2
return patient1, patient2, length

[Neo4j] How to implement having clause in cypher?

假設我透過以下 Cypher 語法，查詢到在某時間區間內，每個醫生、開立某種藥物以及次數

match (doc:Doctor)-[r1:PRESCRIBES]->(med:Medicine)
where r1.date >= "20180401" and r1.date <= "20180415"     
with doc.name as doctor, med.name as medicine, count(med.name) as prescription_frequency
return doctor, medicine, prescription_frequency
order by prescription_frequency desc

如果我想要撈出 prescription_frequency >= 3 的資料，語法修正如下：

match (doc:Doctor)-[r1:PRESCRIBES]->(med:Medicine)
where r1.date >= "20180401" and r1.date <= "20180415"     
with doc.name as doctor, med.name as medicine, count(med.name) as prescription_frequency 
where prescription_frequency >= 3
return doctor, medicine, prescription_frequency
order by prescription_frequency desc

[Spring] [Neo4j] How to create customized query in repository

在 graph database 中有以下幾筆 Movie 的資料，假設我要找出片名有 Fa 此字眼的 Movie

Movie node entity 內容如下：

package neo4j.springdata.vo;

import org.neo4j.ogm.annotation.GeneratedValue;
import org.neo4j.ogm.annotation.Id;
import org.neo4j.ogm.annotation.Index;
import org.neo4j.ogm.annotation.NodeEntity;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.NonNull;
import lombok.RequiredArgsConstructor;

@NodeEntity
@Data
@NoArgsConstructor
@RequiredArgsConstructor
@AllArgsConstructor
public class Movie {

    @Id
    @GeneratedValue
    private Long id;

    @Index(unique = true)
    @NonNull
    private String name;

}

Repository interface / class 內容如下：

package neo4j.springdata.repository;

import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;

import neo4j.springdata.repository.custom.MovieRepositoryCustom;
import neo4j.springdata.vo.Movie;

@Repository
public interface MovieRepository extends CrudRepository<Movie, Long>, MovieRepositoryCustom {
    
}

package neo4j.springdata.repository.custom;

import java.util.List;

import neo4j.springdata.vo.Movie;

public interface MovieRepositoryCustom {
    
    List<Movie> findSimilarName(String name);
    
}

package neo4j.springdata.repository.impl;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.neo4j.ogm.session.Session;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import com.google.common.collect.ImmutableList;

import neo4j.springdata.repository.custom.MovieRepositoryCustom;
import neo4j.springdata.vo.Movie;

@Component
public class MovieRepositoryImpl implements MovieRepositoryCustom {

    @Autowired
    private Session session;

    @Override
    public List<Movie> findSimilarName(String name) {
        String cypher = "match (m:Movie) where m.name =~ {name} return m";

        Map<String, Object> params = new HashMap<>();
        params.put("name", ".*" + name + ".*");
        
        Iterable<Movie> result = session.query(Movie.class, cypher, params);
        
        return ImmutableList.copyOf(result);
    }

}

Service class 內容如下：

package neo4j.springdata.service;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import neo4j.springdata.repository.MovieRepository;
import neo4j.springdata.vo.Movie;

@Service
public class MovieService {

    @Autowired
    private MovieRepository movieRepo;

    @Transactional
    public void createMovies(List<Movie> movies) {
        movieRepo.saveAll(movies);
    }

    public List<Movie> findSimilarName(String name) {
        return movieRepo.findSimilarName(name);
    }

}

Client class 內容如下：

package neo4j.springdata;

import java.util.List;

import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.domain.EntityScan;
import org.springframework.context.annotation.Bean;
import org.springframework.data.neo4j.repository.config.EnableNeo4jRepositories;

import lombok.extern.slf4j.Slf4j;
import neo4j.springdata.service.MovieService;
import neo4j.springdata.vo.Movie;

@SpringBootApplication
@EnableNeo4jRepositories("neo4j.springdata.repository")
@EntityScan(basePackages = "neo4j.springdata.vo")
@Slf4j
public class App {

    public static void main(String[] args) {
        SpringApplication.run(App.class, args);
    }

    @Bean
    CommandLineRunner createCustomQuery(MovieService movieService) {
        return args -> {
            List<Movie> movies = movieService.findSimilarName("Fa");
            log.info("movies = " + movies.toString());
        };

    }

}

pom.xml 內容如下：

<project xmlns="http://maven.apache.org/POM/4.0.0"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>neo4j</groupId>
    <artifactId>springdata</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>jar</packaging>

    <name>springdata</name>
    <url>http://maven.apache.org</url>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <java.version>1.8</java.version>
        <maven.compiler.source>1.8</maven.compiler.source>
        <maven.compiler.target>1.8</maven.compiler.target>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-neo4j</artifactId>
            <version>2.0.0.RELEASE</version>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.16.18</version>
        </dependency>

        <dependency>
            <groupId>com.google.guava</groupId>
            <artifactId>guava</artifactId>
            <version>23.0</version>
        </dependency>

        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-validator</artifactId>
            <version>6.0.7.Final</version>
        </dependency>

        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>3.8.1</version>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <repositories>
        <repository>
            <id>spring-libs-release</id>
            <name>Spring Releases</name>
            <url>https://repo.spring.io/libs-release</url>
            <snapshots>
                <enabled>false</enabled>
            </snapshots>
        </repository>
    </repositories>

</project>

[Neo4j] Cypher example

以下有個朋友與電影的關係圖：

若想找出 John Johnson 的朋友：

// 找出 John Johnson 的朋友
match(u:User) where u.name="John Johnson"
match (u)-[r:IS_FRIEND_OF]-(n:User)
return n as friends

Neo4j browser 執行結果：

若想找出 John Johnson 看過的電影，且找出看過此部電影的人，也看過哪部電影：

// 找出 John Johnson 看過的電影，且找出看過此部電影的人，也看過哪部電影
match(u1:User)-[:HAS_SEEN]->(m1:Movie)<-[:HAS_SEEN]-(u2:User)-[:HAS_SEEN]->(m2:Movie)
where u1.name="John Johnson" and not((u1)-[:HAS_SEEN]->(m2))
return u1 as user, collect(m2.name) as movies

Neo4j browser 執行結果：

若想找出 John Johnson 的朋友看過，但是 John Johnson 沒看過的電影：

// 找出 John Johnson 的朋友看過，但是 John Johnson 沒看過的電影
match (m1:Movie)<-[r1:HAS_SEEN]-(u1:User)-[:IS_FRIEND_OF*..2]-(u2:User)-[r2:HAS_SEEN]->(m2:Movie)
where u1.name="John Johnson" and not((u1)-[:HAS_SEEN]->(m2))
return m2 as movie, r2.stars as stars

Neo4j browser 執行結果：

若想找出 John Johnson 的朋友看過，但是 John Johnson 沒看過的電影，且朋友對此部電影的評價要超過 3 顆星：

// 找出 John Johnson 的朋友看過，但是 John Johnson 沒看過的電影，且朋友對此部電影的評價要超過 3 顆星
match (m1:Movie)<-[r1:HAS_SEEN]-(u1:User)-[:IS_FRIEND_OF*..2]-(u2:User)-[r2:HAS_SEEN]->(m2:Movie)
where u1.name="John Johnson" and not((u1)-[:HAS_SEEN]->(m2)) and r2.stars > 3
return m2 as movie, r2.stars as stars

Neo4j browser 執行結果：

於 Spring Data 中， repository class 定義如下：

package neo4j.springdata.repository;

import java.util.List;

import org.springframework.data.neo4j.annotation.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import neo4j.springdata.vo.User;
import neo4j.springdata.vo.result.UserQueryResult;

@Repository
public interface UserRepository extends CrudRepository<User, Long> {

    @Query(" match(u:User) where u.name={name} " + " match (u)-[r:IS_FRIEND_OF]-(n:User) " + " return n as friends ")
    List<UserQueryResult> findFriends(@Param("name") String name);

    @Query(" match(u1:User)-[:HAS_SEEN]->(m1:Movie)<-[:HAS_SEEN]-(u2:User)-[:HAS_SEEN]->(m2:Movie) "
            + " where u1.name={name} and not((u1)-[:HAS_SEEN]->(m2)) "
            + " return u1 as user, collect(m2.name) as movies ")
    List<UserQueryResult> findRecommendedMovies(@Param("name") String name);

    @Query(" match (m1:Movie)<-[r1:HAS_SEEN]-(u1:User)-[:IS_FRIEND_OF*..2]-(u2:User)-[r2:HAS_SEEN]->(m2:Movie) "
            + " where u1.name={name} and not((u1)-[:HAS_SEEN]->(m2)) " + " return m2 as movie, r2.stars as stars")
    List<UserQueryResult> findUnwatchedMovies(@Param("name") String name);

    @Query(" match (m1:Movie)<-[r1:HAS_SEEN]-(u1:User)-[:IS_FRIEND_OF*..2]-(u2:User)-[r2:HAS_SEEN]->(m2:Movie) "
            + " where u1.name={name} and not((u1)-[:HAS_SEEN]->(m2)) and r2.stars > 3 "
            + " return m2 as movie, r2.stars as stars ")
    List<UserQueryResult> findUnwatchedGoodMovies(@Param("name") String name);

}

Query result bean 定義如下：

package neo4j.springdata.vo.result;

import java.util.List;

import org.springframework.data.neo4j.annotation.QueryResult;

import lombok.Data;
import lombok.ToString;
import neo4j.springdata.vo.Movie;
import neo4j.springdata.vo.User;

@QueryResult
@Data
@ToString
public class UserQueryResult {

    private User friends;
    
    private User user;
    private List<String> movies;
    
    private Movie movie;
    private Integer stars;
    
}

Service class 定義如下：

package neo4j.springdata.service;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import neo4j.springdata.repository.UserRepository;
import neo4j.springdata.vo.result.UserQueryResult;

@Service
public class UserService {

    @Autowired
    private UserRepository userRepo;
  
    public List<UserQueryResult> findFriends(String name) {
        return userRepo.findFriends(name);
    }

    public List<UserQueryResult> findRecommendedMovies(String name) {
        return userRepo.findRecommendedMovies(name);
    }

    public List<UserQueryResult> findUnwatchedMovies(String name) {
        return userRepo.findUnwatchedMovies(name);
    }

    public List<UserQueryResult> findUnwatchedGoodMovies(String name) {
        return userRepo.findUnwatchedGoodMovies(name);
    }
}

Client code:

package neo4j.springdata;

import java.util.List;

import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.Bean;
import org.springframework.data.neo4j.repository.config.EnableNeo4jRepositories;

import com.google.common.base.Joiner;

import lombok.extern.slf4j.Slf4j;
import neo4j.springdata.service.UserService;
import neo4j.springdata.vo.Movie;
import neo4j.springdata.vo.result.UserQueryResult;

@SpringBootApplication
@EnableNeo4jRepositories
@Slf4j
public class App {

    public static void main(String[] args) {
        SpringApplication.run(App.class, args);
    }
    
    @Bean
    CommandLineRunner queryTest(UserService service) {
        return args -> {
            String name = "John Johnson";
            
            log.info("找出 John Johnson 的朋友:");
            List<UserQueryResult> friends = service.findFriends(name);
            friends.forEach(f->log.info(f.toString()));
            
            log.info("找出 John Johnson 看過的電影，且找出看過此部電影的人，也看過哪部電影");
            List<UserQueryResult> recommendedMovies = service.findRecommendedMovies(name);
            recommendedMovies.forEach(r -> {
                String uName = r.getUser().getName();
                List<String> movies = r.getMovies();
                log.info("user name = " + uName + ", watched " + Joiner.on(",").join(movies));
            });
            
            log.info("找出 John Johnson 的朋友看過，但是 John Johnson 沒看過的電影");
            List<UserQueryResult> unwatchedMovies = service.findUnwatchedMovies(name);
            unwatchedMovies.forEach(u -> {
                Movie movie = u.getMovie();
                Integer stars = u.getStars();
                log.info("movie name = " + movie.getName() + ", stars = " + stars);
            });
            
            log.info("找出 John Johnson 的朋友看過，但是 John Johnson 沒看過的電影，且朋友對此部電影的評價要超過 3 顆星");
            List<UserQueryResult> unwatchedGoodMovies = service.findUnwatchedGoodMovies(name);
            unwatchedGoodMovies.forEach(u -> {
                Movie movie = u.getMovie();
                Integer stars = u.getStars();
                log.info("Good movie name = " + movie.getName() + ", stars = " + stars);
            });
        };
    }

}