Complete Intro to Databases

Querying with Match & Constraint

Brian Holt

Brian Holt

SQLite Cloud
Complete Intro to Databases

Check out a free preview of the full Complete Intro to Databases course

The "Querying with Match & Constraint" Lesson is part of the full, Complete Intro to Databases course featured in this preview video. Here's what you'd learn in this lesson:

Brian demonstrates how to query for relationships between nodes using the MATCH command. The MATCH command is used to find who acted in the movie added to the database in the previous segment. How to use the CONSTRAINT command to filter out movies with the same title is also mentioned here.

Preview
Close

Transcript from the "Querying with Match & Constraint" Lesson

[00:00:01]
>> So let's go ahead and write another query now to query for the relationship. So, what I'm gonna do here now after I've done this, we're gonna start looking at Aubrey Plaza because I think she's very funny, and we're going to match, (p:Person)-[:ACTED_IN] -> (m:Movie); And actually, let's just grab that r, and we're going to return r.

[00:00:44]
So in this particular case, we're getting all of the people that act in various different roles inside of Scott Pilgrim Versus the World. So I can actually query directly for these edges or relationships. Okay, let's put a where clause in here, WHERE p.name= 'Aubrey Plaza And then you wanna return p, right?

[00:01:19]
Because we're gonna find the person that acted in Scott Pilgrim Versus the World, so that'll be Aubrey Plaza, right? Or we can look at the r, we can see that Aubrey Plaza in Scott Pilgrim Versus the World played Julie Powers, right? Or again, you can return m which will be the movie, which will be Scott Pilgrim versus the World.

[00:01:44]
So this actually will give you every movie that Aubrey Plaza played. And in this case, we just have one movie in our database, but this would give you all of them. Okay, now what happens if I wanted to find everyone that acted in a film with Aubrey Plaza.

[00:02:02]
Well, you can actually do that as well. So I'm gonna say MATCH (p:Person) ->, [:ACTED_IN]; -> (movie), right? We don't even have to get that variable because we're not gonna refer to it. We're just looking for any movie that someone acted in with Aubrey Plaza and then I can just do an arrow in the other direction here.

[00:02:32]
<-[ :ACTED_IN], and then I'll have another person so we'll just call this person, I don't know, -(q:) is what I have my notes, I don't know why I did q. So another person like that, WHERE p.name = 'Aubrey Plaza' AND, we don't want it to return Aubrey Plaza.

[00:02:59]
Because technically, Aubrey plaza has been in the movies with herself, according to our graph database. So when we say where q.name, and this is how you do, null equal is the angle bracket is just together like that, <> 'Aubrey Plaza' RETURN q.name. So again, I really liked, I messed that up, there's shouldn't be an arrow there, right there I meant.

[00:03:33]
So this is actually not gonna work because there shouldn't be an angle bracket there. There we go, so this is where it is. So p:Person, then you have just a minus sign, acted in movie, and you have this other person that also acted in the movie, and then you just return the name down here.

[00:03:58]
And so you can see these are all the people that played roles in films with Aubrey Plaza. Now imagine that you had 100 films and 1,000 actresses, you would get 1,000 actresses back here. Pretty cool, right? I like Cipher as a query language cuz it reads very well, despite the fact that graphs are kind of complicated, right?

[00:04:21]
There's a lot of interesting things you can do with graphs, but as long as you're just kind of like drawing your relationships between these various different nodes, it's pretty easy to query for them. Like, what if I wanted to find all the roles that these people played with Aubrey Plaza?

[00:04:37]
It's totally possible, so I'm gonna do the same thing here, except I'm gonna describe r here and ACTED_IN. I'm gonna say where p = q.name, and then what I wanna do is I wanna return <> q.name, I wanna return r.roles. And you can see here these are all the roles people played in films with Aubrey Plaza, it's wild to me that that's so easy to query for something like that.

[00:05:05]
All I did is stick a variable here with our r : ACTED_IN, and then all I did was return the property of r.roles. So what if I wanna query for everyone that was younger than Aubrey Plaza that played in a film with her? Again, terribly easy to do, same query up here.

[00:05:29]
Okay, and then here instead of saying where q.name is not equal to Aubrey Plaza, all I have to do here is say where q.born is greater than p.born and then just return q.name. And so these are both Brie Larson, and Anna Kendrick played in a film with Aubrey Plaza, and they are both younger than Aubrey Plaza.

[00:05:58]
Just to kind of show you something so that you know will how to do this. Let's say that we wanted to create a constraint where movie names must be unique. Now we all know that there's like 17 Godzilla films because they keep calling the films Godzilla, but for a moment, fathom for a second that a movie name must be unique.

[00:06:16]
What you can do here is you can say, CREATE CONSTRAINT ON (a: Movie) ASSERT a.title IS unique;. And this is going to create an index actually, where it's gonna force movie titles to be unique. And now if I tried to go create another film called Scott Pilgrim Versus the World is gonna say, you can't do this, I've been told that this is unique, so I'm going to hold this constraint to be true.

[00:06:49]
So that's how you do unique and naming with graph databases. So graph databases actually do have indexes, we'll get to that here in just a second.

Learn Straight from the Experts Who Shape the Modern Web

  • In-depth Courses
  • Industry Leading Experts
  • Learning Paths
  • Live Interactive Workshops
Get Unlimited Access Now