Many-to-Many Relationships

[00:00:00]
>> So we just did one-to-many relationships, now we're gonna do many-to-many. And we already discussed what it is, it's a recipe contains many ingredients, and ingredients belongs to many recipes. So how de we model those things together, right? There's not a one table to one table relationship where if I have something here, it exists over here, I need another intermediary, so we're actually gonna add a third table to model that.

[00:00:26]
So let's go ahead and do that. We're gonna create a table called recipe_ingredients, It's gonna have a recipe_id, That is an integer that references recipes(recipe_id) ON DELETE NO ACTION. Why am I putting ON DELETE NO ACTION here? If I have an ingredient, And I delete the connection between the recipe and the ingredient, I don't wanna delete both recipes and the ingredient, which then have cascading deletes, which then go back and delete things.

[00:01:17]
You'd eventually kinda wipe out your entire database accidentally. So if you're trying to delete a recipe, basically, what you're saying is you'd have to go clear out the recipes ingredients table first, all the connections first, before I let you delete the recipe. So you would start by deleting things out of this table first before you go delete recipes or ingredients.

[00:01:39]
Okay, I'm gonna have an ingredient_id INT REFERENCES ingredients. On its (id) ON DELETE NO ACTION. Okay, you need some sort of primary key cuz every table has a primary key, but I could just insert a connection ID or something like that or just give it a normal ID.

[00:02:10]
However, we're never going to query this table for a specific connection. We actually don't really care about the individual connection objects. We care about all about is just joining the two tables using this intermediary table. So instead of having some third ID, what I'm gonna do is I'm gonna have a combined or a compound index that combines them together and asserts their uniqueness.

[00:02:33]
So I'm gonna say CONSTRAINT, recipe_ingredients, I'm giving it a name, that's what this is. Pk for primary key, PRIMARY KEY of recipe_id and ingredient_id. You need a comma. So what this line is asserting is the primary key of this table is the combination of recipe_id and ingredient_id. That should be guaranteed unique in this table, because it doesn't make sense that flour would be the same ingredients twice in one recipe, it's just the one recipe, right?

[00:03:42]
So if I try and insert egg twice into the cookie recipe, it's gonna say you already have this in here, there's no need to connect it again. You don't need multiple of one connections, you have one connection. That makes sense? Okay, so now if I look at my recipe ingredients, I have this recipe_id and ingredient_id.

[00:04:07]
And then my foreign key constraint is that recipe_id exists in recipe, the ingredient exists in the ingredient one, and the combination of the two is unique. This is how you model many-to-many relationships using SQL. All right, let's go ahead and go insert some things into that database. I'm just gonna copy and paste this cuz it's not that interesting.

[00:04:45]
Okay, so now if I SELECT star FROM recipe_ingredients, I have some rows in here, these are guaranteed to be in ingredients, these are guaranteed to be in recipe. However, the connections themselves are wholly uninteresting, right? I would never wanna return this to a user, I wanna see what's in the recipe and I wanna see what the ingredient is.

[00:05:16]
But notice I have 1, 1, 1, so these are all the same, then I have 13 and 13 here. So ingredients can exist in multiple recipes, recipes can have multiple of the same ingredient, it's just the combination of the two that's unique. Okay, now let's say instead of having recipe_id 1 here, I wanna have the actual recipe.

[00:05:41]
Instead of having the ingredient_id here, I wanna have the ingredient. How do I do that? Joins, you might have guessed that, good job, but the second thing is we have to do multiple joins, we have to join a couple of times. So we're going to select i.title, and you can actually give them names if it's a little bit easier for you, so that's what this AS keyword does, select the ingredient title AS ingredient_title, if that's clearer to you.

[00:06:16]
This is just gonna be the name in the table that it returns to you, right? I.image AS ingredient_image. I.type AS ingredient_type. Okay, so first thing, I'm just gonna do the ingredient part first, and then we'll go back and we'll do the recipe one. So FROM recipe_ingredients ri, INNER JOIN, ingredients i, ON, i.id = ri.ingredient_id.

[00:07:16]
WHERE, we'll just grab one of them, ri.recipe_id = 1. And what did I miss up here? Recipes_ingredients, yeah, I mess this up all the time, it's recipe_ingredient. There we go. So I went and grabbed all the ingredients for recipe 1, which I think is cookies. So in case you didn't know, carrots, cauliflowers, and chicken go into cookies.

[00:07:48]
These are nonsensical recipes, so don't worry too much about that. So this got us all the ingredients, but it doesn't tell us what the recipes are, right? So how do we get the recipes in here? We're gonna have to do another INNER JOIN. So let's do that. We're gonna do, Okay, let's do this in code, because this is gonna be so much easier for you to visualize this, Change Language Mode, SQL.

[00:08:27]
So I'm gonna do SELECT, i.title AS ingredient_title, we're gonna do i.image AS ingredient_image, and then we're gonna do i.type AS ingredient_type. But we're also gonna grab from r, which is gonna be recipe, we're gonna grab r.title, AS recipe_title, and what else do we have in that? Recipes, the body, so we'll grab the body as well.

[00:09:12]
So we grab r.body AS recipe_body, and if you want, you can grab the IDs as well. r.id AS rid, and i.id, okay, so this is recipe_id, recipe_id, and i.id AS iid, okay? We're gonna do a FROM, we're gonna do recipe_ingredients ri. Okay, you have to INNER JOIN. I'm gonna try and space these out a little bit so it's a little bit easier to read.

[00:10:04]
INNER JOIN, ingredients i, ON. I.id = ri.ingredient_id. Okay, INNER JOIN again, recipes r, ON, r.recipe_id = ri.recipe_id. In theory, you could do a natural JOIN here but don't [LAUGH]. And after that, you could put your WHERE clause if you wanted to, you could say WHERE, ri, whatever, blah, blah, blah.

[00:11:03]
We're not gonna do that, we'll just grab all of them and let's see if that works. And there you go. So the recipe here, cookies, cookies, cookies, this is coming from the recipe table. The ingredients_type is coming from the ingredients table. And we have all of these connections that exist because of that recipe_ingredients table that exist there in the middle, right?

[00:11:39]
If you remember, we inserted, Where are we? Up here, we inserted five different rows of just numbers that correspond to both tables. And then we used the joins here to grab the information from both tables, so therefore we can see all of the ingredients and all these recipes.

[00:11:59]
So five insertions, we have five rows here, that matches up. It's a lot, I know. But this is kinda how you use these relational tables to join information to build each other up into these big things. And then using foreign key constraints, we can kind of guarantee that everything stays in sync.

[00:12:19]
You can see here when you start joining a lot of tables, it's really useful to have these short table names. Frequently, people make them one to two letters, it's probably a little bit excessive. In a very short query like this, it's probably fine, but if you're writing 100-line queries, which is not that uncommon, you probably wanna have a little bit more description.

[00:12:41]
I might call this i and g in recipe or something like that. But typing out recipe_ingredients 50 times sounds wholly unappealing to me, so I don't know, it's kind of up to you what you wanna do. But yeah, this is how you model many-to-many relationships.