This database is a small example of a networking site where users can watch movies, subscribe to TV shows and comment and rate any of the previous media. Users may follow or block other users, just like any other networking website nowadays.
-
Purpose:
The theme was chosen because of the success these type of webs have all over the world, and because in general their structure can easily and naturally be displayed as a graph with very different types of relationships and very connected data. So, in a nutshell, they can be seen as a very good environment to start working with graph databases.
The following is the script used to create all the nodes of the database and the relationships between them.
CREATE
//Media
//Movies
(toystory:Movie:Media {title:'Toy Story 3', tagline:'No toy gets left behind.', genre:'Animation', runtime_min:103, won:'2 Oscars'}),
(wreckitralph:Movie:Media {title:'Wreck-It Ralph', tagline:'When the arcade closes, the fun begins', genre:'Animation', runtime_min:108}),
(thelmalouise:Movie:Media {title:'Thelma & Louise', genre:'Drama', runtime_min:130, won:'1 Oscar'}),
(thehours:Movie:Media {title:'The Hours', genre:'Drama', runtime_min:114, won:'1 Oscar'}),
(misssunshine:Movie:Media {title:'Little Miss Sunshine', genre:'Comedy', runtime_min:101, won:'2 Oscars'}),
(erinbrockovich:Movie:Media {title:'Erin Brockovich', genre:'Drama', runtime_min:131, won:'1 Oscar'}),
(avengers:Movie:Media {title:'The Avengers', genre:'Action', runtime_min:143}),
//TV SHOWS
(friends:TV_Show:Media {title:'Friends', episode_duration:'22 min', seasons:'10', genre:'Comedy', spin_off:'Joey'}),
(office:TV_Show:Media {title:'The Office', episode_duration:'22 min', seasons:'9', genre:'Comedy'}),
(fringe:TV_Show:Media {title:'Fringe', episode_duration:'60 min', seasons:'5', genre:'Sci-Fi'}),
//USERS
(bradley:User {name:'Bradley', surname:'Green', age:24, city:'Los Angeles'}),
(matthew:User {name:'Matthew', surname:'Cooper', age:36, city:'Los Angeles'}),
(lisa:User {name:'Lisa', surname:'Adams', age:15, city:'New York'}),
(annie:User {name:'Annie', surname:'Behr', age:25, city:'Chicago'}),
(ripley:User {name:'Ripley', surname:'Aniston', city:'Los Angeles'}),
(john:User {name:'John', surname:'Goodman', age:34, city:'New York'}),
(amy:User {name:'Amy', surname:'Cooper', age:22}),
(mark:User {name:'Mark', surname:'McAdams', age:17, city:'Los Angeles'}),
(dennis:User {name:'Dennis', surname:'Lemon', age:42, city:'Los Angeles'}),
//FOLLOWS
(bradley)-[:FOLLOWS]->(matthew),
(bradley)-[:FOLLOWS]->(ripley),
(bradley)-[:FOLLOWS]->(john),
(matthew)-[:FOLLOWS]->(bradley),
(lisa)-[:FOLLOWS]->(matthew),
(lisa)-[:FOLLOWS]->(annie),
(annie)-[:FOLLOWS]->(lisa),
(annie)-[:FOLLOWS]->(matthew),
(annie)-[:FOLLOWS]->(ripley),
(ripley)-[:FOLLOWS]->(annie),
(ripley)-[:FOLLOWS]->(matthew),
(ripley)-[:FOLLOWS]->(john),
(amy)-[:FOLLOWS]->(ripley),
(amy)-[:FOLLOWS]->(mark),
(mark)-[:FOLLOWS]->(amy),
(mark)-[:FOLLOWS]->(dennis),
//BLOCKED
(bradley)-[:BLOCKED {reason:'Too much spam'}]->(amy),
(bradley)-[:BLOCKED]->(annie),
(matthew)-[:BLOCKED]->(lisa),
(annie)-[:BLOCKED]->(john),
(amy)-[:BLOCKED {reason:'Too much spam'}]->(john),
//WATCHED
(bradley)-[:WATCHED {date:'2013-12-01'}]->(wreckitralph),
(bradley)-[:WATCHED {date:'2013-12-05'}]->(toystory),
(matthew)-[:WATCHED {date:'2013-10-10'}]->(wreckitralph),
(matthew)-[:WATCHED {date:'2013-11-20'}]->(toystory),
(matthew)-[:WATCHED {date:'2013-11-25'}]->(misssunshine),
(matthew)-[:WATCHED {date:'2013-12-01'}]->(thehours),
(matthew)-[:WATCHED {date:'2013-12-15'}]->(avengers),
(lisa)-[:WATCHED {date:'2013-05-08'}]->(misssunshine),
(lisa)-[:WATCHED {date:'2013-09-26'}]->(wreckitralph),
(lisa)-[:WATCHED {date:'2013-11-29'}]->(toystory),
(annie)-[:WATCHED {date:'2013-12-10'}]->(misssunshine),
(annie)-[:WATCHED {date:'2013-12-11'}]->(thehours),
(annie)-[:WATCHED {date:'2013-12-12'}]->(toystory),
(annie)-[:WATCHED {date:'2013-12-29'}]->(thelmalouise),
(ripley)-[:WATCHED {date:'2013-06-14'}]->(erinbrockovich),
(john)-[:WATCHED {date:'2013-07-11'}]->(erinbrockovich),
(john)-[:WATCHED {date:'2013-08-18'}]->(avengers),
(dennis)-[:WATCHED {date:'2013-08-19'}]->(avengers),
//SUBSCRIBED
(bradley)-[:SUBSCRIBED]->(friends),
(bradley)-[:SUBSCRIBED]->(office),
(annie)-[:SUBSCRIBED]->(friends),
(lisa)-[:SUBSCRIBED]->(friends),
(lisa)-[:SUBSCRIBED]->(fringe),
(john)-[:SUBSCRIBED]->(office),
(john)-[:SUBSCRIBED]->(fringe),
(ripley)-[:SUBSCRIBED]->(office),
(dennis)-[:SUBSCRIBED]->(office),
//COMMENTED
(bradley)-[:COMMENTED {date:'2013-12-05', comment:'Deftly blending comedy, adventure, and honest emotion, Toy Story 3 is a rare second sequel that really works.'}]->(toystory),
(matthew)-[:COMMENTED {date:'2013-10-11', comment:'Equally entertaining for both kids and parents old enough to catch the references, Wreck-It Ralph is a clever, colorful adventure built on familiar themes and joyful nostalgia.'}]->(wreckitralph),
(matthew)-[:COMMENTED {date:'2013-11-20', comment:'Even with the bar raised high, Toy Story 3 enchanted and moved me so deeply I was flabbergasted that a digitally animated comedy about plastic playthings could have this effect.'}]->(toystory),
(matthew)-[:COMMENTED {date:'2013-12-03', comment:'Deeply moving'}]->(thehours),
(lisa)-[:COMMENTED {date:'2013-05-08', comment:'If Olive had been played by any other little girl, she would not have affected us as mightily as it did'}]->(misssunshine),
(lisa)-[:COMMENTED {date:'2013-09-27', comment:'overplotted and underdeveloped.'}]->(wreckitralph),
(lisa)-[:COMMENTED {date:'2013-12-05', comment:'In the beginning it had the best of The X Files and Lost, but later on it was more of the same and ended up being very repetitive'}]->(fringe),
(annie)-[:COMMENTED {date:'2013-12-19', comment:'Mostly boring and way overrated'}]->(thehours),
(ripley)-[:COMMENTED {date:'2013-06-14', comment:'Roberts has wasted her effervescence on many paltry projects, but she hits the jackpot this time. Erin, single mother of three, a former Miss Wichita who improbably rallies a community to take on a multi-billion-dollar corporation, is the richest role of her career, simultaneously showing off her comic, dramatic and romantic chops'}]->(erinbrockovich),
(john)-[:COMMENTED {date:'2013-08-18', comment:'With a script that never the humanity of its heroes and no shortage of superpowered set pieces, The Avengers lives up to its hype and raises the bar for Marvel at the movies.'}]->(avengers),
//RATED
(bradley)-[:RATED {date:'2013-12-05', score:10}]->(toystory),
(bradley)-[:RATED {date:'2013-12-01', score:9}]->(wreckitralph),
(matthew)-[:RATED {date:'2013-11-20', score:10}]->(toystory),
(matthew)-[:RATED {date:'2013-11-25', score:9}]->(misssunshine),
(lisa)-[:RATED {date:'2013-11-29', score:9}]->(friends),
(lisa)-[:RATED {date:'2013-09-27', score:6}]->(wreckitralph),
(lisa)-[:RATED {date:'2013-11-29', score:8}]->(toystory),
(annie)-[:RATED {date:'2013-12-10', score:9}]->(misssunshine),
(annie)-[:RATED {date:'2013-12-10', score:8}]->(friends),
(annie)-[:RATED {date:'2013-12-12', score:8}]->(toystory),
(ripley)-[:RATED {date:'2013-06-14', score:9}]->(erinbrockovich),
(john)-[:RATED {date:'2013-10-18', score:8}]->(office),
(john)-[:RATED {date:'2013-08-18', score:8}]->(avengers)The following is the graph representing users, media and the connection between all of them.
From now on, in these queries and examples the term "I" refers to the user 'Bradley Green'. Furthermore, an active user is a given user that has watched at least one movie, subscribed to some TV show or both.
This query looks for active users that live in the same city as I do and they are ordered by their activity level, that is, the total number of movies watched and TV Shows subscribed to.
MATCH (me:User {name:'Bradley', surname:'Green'}), (other:User)-[:WATCHED|:SUBSCRIBED]->(media:Media)
WHERE other.city = me.city AND other<>me
RETURN str(other.surname + ", " + other.name) as Other_User, other.city as City, count(other) as Activity_Level ORDER BY Other_User ASC;In any social networking site there exists the possibility of blocking other users in order not to be contacted by those users and not to receive news from them.
Since users have such operation available, it is mandatory that they can view who they have blocked.
In this example, we list those users that we (remember, we are Bradley Green), have blocked and the reason why we blocked them. If there is no reason, 'No reason known' will appear instead.
MATCH (me:User {name:'Bradley', surname:'Green'})-[b:BLOCKED]->(other:User)
RETURN str(other.name + ' ' + other.surname) AS Blocked,
CASE WHEN HAS(b.reason)
THEN b.reason
ELSE 'No reason known' END AS Reason;It’s very common in webs with media content like movies or tv shows such as IMDB, Rotten Tomatoes, FilmAffinity, SeriesLy and whatnot to allow users to list and view the comments and opinions other users have posted about a given item.
In this example, we list all the comments posted about a given movie (Toy Story 3), ordered by the date they were posted.
MATCH (movie:Movie {title:'Toy Story 3'})<-[c:COMMENTED]-(author:User)
WITH c, author
MATCH (me:User{name:'Bradley', surname:'Green'})-[:FOLLOWS]->(other:User)
WITH c, author, collect(other) as Friends
RETURN c.comment AS Comment, str(author.name + ' ' + author.surname) AS Author, c.date AS Date,
CASE WHEN (author) in Friends
THEN str('Following the author')
ELSE str('Not following the author') END AS Following
ORDER BY Date DESC;
Other usual thing to run into not only in these kind of websites, but also in ecommerces is a list of other items visited/liked/bought by users that have also visited the item we’re currently seeing.
In this example we are in the movie 'Toy Story 3' and we list those movies users that watched 'Toy Story 3' also watched. These recommendations are ordered by how many times those movies appear as recommended.
MATCH (target:Movie)<-[:WATCHED]-(u:User)-[:WATCHED]->(p:Movie)
WHERE p.title = 'Toy Story 3'
WITH str(target.title + ' (' + target.genre + ')') AS Suggestion, count(target.title) as Occurrences
RETURN distinct Suggestion, Occurrences ORDER BY Occurrences DESC, Suggestion ASC;Sometimes we are suggested to follow someone because that user and I follow the same people. In this example we show which users we are recommended to follow and which are our common 'links' (people we follow)
MATCH (me:User)-[:FOLLOWS]->(common:User)<-[:FOLLOWS]-(stranger:User)
WHERE NOT (me)-[:BLOCKED]->(stranger) AND me.name = 'Bradley' AND me.surname = 'Green'
AND NOT(me)-[:FOLLOWS]->(stranger)
RETURN str(stranger.name + ' ' + stranger.surname) AS User, COLLECT(str(common.name + ' ' + common.surname)) AS Common_Friends, COUNT(*) AS Occurrences ORDER BY Occurrences DESC;In websites with media content (movies, tv shows, music, …), if we follow someone is because we may be interested in knowing which items that user liked or recommended. There are webs like Filmaffinity or IMDB that allow you to list the rates people you follow have given to media items.
In this example that’s what we’ll do.
MATCH (me:User)-[:FOLLOWS]->(friend:User)-[r:RATED]->(media:Media)
WHERE me.name = 'Bradley' AND me.surname = 'Green'
RETURN r.score as SCORE, friend.name as FRIEND, media.title as TITLE
ORDER BY SCORE;Display those users that have watched movies from all the different genres but not subscribed to a TV Show
This query or example, even though it could be a feature of a given social website, it’s more of a query that allows us to play a little bit with the graph. The purpose is to find users that have watched movies from all the genres but are not subscribed to a TV Show
MATCH (movie:Movie)
WITH COLLECT(DISTINCT movie.genre) AS genres
MATCH (user:User)-[:WATCHED]->(movie:Movie)
WITH user, genres, COLLECT(DISTINCT movie.genre) AS watched
WHERE ALL(x IN genres WHERE x IN watched) AND NOT (user)-[:SUBSCRIBED]->(:TV_Show)
RETURN str(user.name + ' ' + user.surname) AS User;Sometimes in networking websites we want to list our friends, or friends of friends, or even friends of friends of friends. In this example that’s exactly what we’ll do, and we’ll display the relationship between us. (Remember: We are Bradley Green)
MATCH (me:User{name:'Bradley', surname:'Green'})-[p:FOLLOWS*1..3]->(other:User)
WHERE NOT (me)-[:BLOCKED]->(other) AND me<>other
RETURN DISTINCT str(other.name + ' ' + other.surname) AS User,
CASE WHEN LENGTH(shortestPath((me)-[:FOLLOWS*]->(other))) = 1 THEN
str('Friend')
WHEN LENGTH(shortestPath((me)-[:FOLLOWS*]->(other))) = 2 THEN
str('Friend of friend')
ELSE
str('Friend of friend of friend') END AS Relation;Another feature of almost any website is the Top 10, Top 3 or whatever number you want to choose. In our example, we’ll show the Top 3 movies since July 2013. The criteria to be 'higher' in this 'scale' is to have higher ratings, and if two movies have the same rating, it’s higher the one with more views by the users.
MATCH ()-[r:RATED]->(m:Movie)
WHERE r.date >= '2013-07-01'
WITH m, AVG(r.score) AS rating
MATCH path=()-[w:WATCHED]->(m)
WHERE w.date >= '2013-07-01'
WITH m, rating, count(path) as views
ORDER BY rating DESC, views DESC
RETURN str(m.title + ' (' + m.genre + ')') AS Movie, rating, views LIMIT 3;Last, but not least, we’ll write a query that calculates the total amount of minutes people we follow have spend watching movies.
MATCH (me:User{name:'Bradley', surname:'Green'})-[:FOLLOWS]->(other:User)
WITH other
OPTIONAL MATCH (other)-[:WATCHED]->(movie:Movie)
WITH other, COLLECT(movie) AS watched
RETURN str(other.name + ' ' + other.surname) AS User,
reduce(time = 0, mov IN watched | time + mov.runtime_min) AS Total_minutes
ORDER BY Total_minutes DESC;Note : The OPTIONAL MATCH clause is used because otherwise, if the user we follow hasn’t watched any movie, that user will not appear in the result. This way, if the user has watched a movie, he/she will appear, but if that user hasn’t watched any movie, he/she will appear too, and the total amount of time will be 0.
To illustrate this: Every user Bradley Green follows has watched some movie. The critical part appears when we say "me" is Amy, because Amy follows Ripley (who has watched a movie) and Mark (who hasn’t). If OPTIONAL MATCH is just a regular MATCH clause, Mark won’t appear in the result. However, if we write the OPTIONAL MATCH the way it is already written, Mark will appear in the result with a total amount of time of 0 minutes.