GraphAcademy

Introduction to Graph Databases Workshop

Graph Databases

Cypher

Pipelining with WITH

What is WITH?

In Cypher, the WITH clause is used to:

  1. Chain parts of a query together.

  2. Control query flow.

  3. Aggregate data and pass it to the next part of the query.

  4. Filter results after aggregation.

It’s similar to RETURN, but instead of just returning data, WITH is used to pass data to the next part of the query.

Chaining with WITH

You can chain queries together using WITH.

Find all users who rated "Matrix, The" and then the other movies they rated.

cypher
MATCH (u:User)-[:RATED]->(m:Movie {title: "Matrix, The"})
WITH u, m
MATCH (u)-[r:RATED]->(other:Movie)
WHERE other <> m
RETURN u.name, other.title, r.rating

Filter After Aggregation

You can use WITH to filter results after aggregation.

Get people who have rated more than 5 movies.

cypher
MATCH (u:User)-[:RATED]->(m:Movie)
WITH u, count(m) AS movieCount
WHERE movieCount > 5
RETURN u.name, movieCount

The query:

  • Matches users who rated movies.

  • Uses WITH to count how many movies each person rated.

  • Filters based on the aggregation.

  • Returns the results.

Ordering and limiting

You can use WITH to order and limit results.

Find the top 3 rated movies, then find the actors who acted in them.

cypher
MATCH (m:Movie)<-[r:RATED]-(u:User)
WITH m, avg(r.rating) AS avgRating
ORDER BY avgRating DESC
LIMIT 3

MATCH (p:Person)-[:ACTED_IN]->(m)
RETURN m.title, avgRating, p.name

Challenges

Complete the queries to answer the following questions:

  1. What is the lowest rated movie that has a revenue of more than $1 billion (1000000000)?

    cypher
    MATCH (m:Movie)
WITH ? WHERE m.revenue > 1000000000

MATCH (u:User)-[r:RATED]-(?)
RETURN
    m.title AS title,
    m.revenue AS revenue,
    ???(?.??????) as avgRating
ORDER BY ???????

  2. Of the top 5 users who rated the most movies, who gave the lowest average rating?

    cypher
    MATCH (u:User)-[r:RATED]->(m:Movie)
WITH ?, ?????(?) AS numberOfRatings
ORDER BY ?????? DESC LIMIT ?

MATCH (?)-[r:RATED]->(m:Movie)
RETURN
    u.name AS name,
    numberOfRatings,
    ???(???????) AS ???????
ORDER BY ??????
Click to reveal the answers

  1. What is the lowest rated movie that has a revenue of more than $1 billion (1000000000)?

    cypher
    MATCH (m:Movie)
WITH m WHERE m.revenue > 1000000000

MATCH (u:User)-[r:RATED]-(m)
RETURN
    m.title AS title,
    m.revenue AS revenue,
    avg(r.rating) as avgRating
ORDER BY avg(r.rating)

  2. Of the top 5 users who rated the most movies, who gave the lowest average rating?

    cypher
    MATCH (u:User)-[r:RATED]->(m:Movie)
WITH u, count(r) AS numberOfRatings
ORDER BY numberOfRatings DESC LIMIT 5

MATCH (u)-[r:RATED]->(m:Movie)
RETURN
    u.name AS name,
    numberOfRatings,
    avg(r.rating) AS avgRating
ORDER BY avgRating DESC

Summary

In this lesson, you learned how use WITH to chain queries together, filter results after aggregation, and order and limit results.