Recipes Solution

[00:00:00]
>> So the first thing I'm gonna do here, I don't wanna write it out again. So I'm just gonna copy and paste the exact same pool kind of thing here, and we're gonna put that right here. So now I have this pool that I can query from. Again, make sure you are, whatever you call your database, right?

[00:00:23]
Make sure your database is running, make sure it has all the data that you expect. Now, this is gonna work until that works. Okay, here we're gonna put the search functionality or list functionalities, it was aptly pointed out to me since they were not actually filtering It all.

[00:00:43]
Okay, then I'm gonna say const rows = await pool.query. And then here, I'm gonna put this on multiple lines. If you use backticks in JavaScript, you can put things on multiple lines, and that can be helpful for me. SELECT DISTINCT ON recipe_id. Say r.recipe_id, just to be extra specific.

[00:01:15]
We're gonna say r.recipe_id, r.tittle. We're gonna COALESCE (rp.url, "default.jpg) as URL, Okay, FROM recipes r, we need to join but we also need to include all recipes, not just ones that have images. So we're gonna do a LEFT JOIN to include everything from the FROM table. We're gonna LEFT JOIN from recipes_photos rp, ON, R.recipe_id = rp.recipe_id.

[00:02:23]
No parameters here because we're just listing everything in the database in this particular case. A useful thing to do later if you wanted to go and expand your knowledge is you could do pagination on this. And then here I'm just gonna say res.json rows, which is what came back here.

[00:02:48]
And then we get rid of all this. Cool, take a look and make sure I'm not out of my mind. So recipes, and I crashed it. Where did I crash? This needs an extra, let's try that, I forgot a, No, that's not it, because that's what it's supposed to be.

[00:03:20]
So let's actually see what I did wrong. Unterminated string, which is the default.jpg, that is actually what's wrong. So make sure you have both of the single quotes there. Now, let's try that again, and there you go. So see these have photos, the ones that do. And, You can see some of these have this photo that looks like this one, that is the default photo.

[00:03:58]
Whatever, that's when your address is giving up, it says whatever, I don't care. Let's hop into the get. So again, I broke this into two photo, not two photo, I broke this into two queries. And now just make sense to me. But just like programming and there's like a ton of ways to do the one thing, same thing in SQL, there's a lot of different ways that you could have done this.

[00:04:27]
You could have definitely done this in three queries, right? One of the recipe table, one of the recipe photos table, and one of the ingredients table. And I would have definitely accepted that as correct as well. Const ingredientsPromise = pool.query, this is just how I like to code JavaScript, you don't have to do it the same way, you could definitely do an await here.

[00:04:51]
But here I'm actually just gonna grab the promise. And then I'm gonna say SELECT i.title as ingredient_title, i.image, as ingredient_image, and i.type as ingredient_type. I'll grab that, FROM recipe_ingredients ri. INNER JOIN, I like to put these extra wide space things, just to let me know I'm kinda starting another little clause, right?

[00:05:39]
You can basically think this is like a comma in my speech. Ingredients i, ON i.id = ri.ingredient_id, WHERE ri.recipe_id = 1. We've more or less already written this out together, right? But the idea here is we have the ingredients table. We're grabbing all of the various ingredients that exist for any one recipe based on what the recipe ID is.

[00:06:26]
So here we're just gonna give it the recipeId. Okay, this is the first query that I chose to write. The second query that I'm going to choose to write, Is the photos one, const photosPromise = pool.query. SELECT r.title, r.body. This is what I'm saying, this part could actually could have been a third query where you just get the recipe details.

[00:07:07]
I chose to put it together with the photos cuz that made sense to me, it's one query wasn't that hard to do. Here I'm gonna say COALESCE, Rp.url, to default.jpg like we saw above, As url. FROM recipes r. You can put that on a new line or not, either way.

[00:07:42]
Here we're gonna say LEFT JOIN, right? Cuz we wanna grab all recipes no matter what. Or rather this is like a single detail page. If there is no photos we still want the recipe, that's what the LEFT JOIN means in this particular context. Recipes_photos rp, ON rp.recipe_id = r.recipe_id.

[00:08:12]
And then lastly we need our WHERE r.recipe_id = $1. And then need to give it the recipe ID as well. Okay, so now I have these two promises. Here's my thinking here. You can say await here and await here. The way that await works is it's going to pause execution until this happens.

[00:08:43]
Then it's gonna go here and it's gonna pause execution to this happens. Actually, want both of those queries to go out at the same time, right? Because it doesn't matter what order they come back to me, and I wanna pause and wait for both of them, right? So that makes sense?

[00:08:56]
So you can do this with something called promise.all. This is just a JavaScript thing and how I would write this code. So I can say this is gonna be photosResponse, and then ingredientsResponse = Promise.all, and you're just given an array of promises. So this would be photosPromise, and then ingredientsPromise.

[00:09:34]
And then all I have to do here is, I say, await. So here's what's gonna happen kind of in real time. It's going to call this but it's not going to wait. It's going to immediately call this one as well. So two queries are going out, not simultaneously but in quick order of execution, right?

[00:09:53]
It's not going to wait to call photosPromise for this one to complete. Here, when I say await Promise.all, it's going to wait for both of these to finish. Once both of these have finished, then the execution will resume, okay? And then now I have photosResponse here and ingredientsResponse here.

[00:10:18]
And I can just say const photosRow = photosResponse.rows, and const ingredients, Rows = ingredientsResponse.rows, right? Then down here, res.json, Ingredients, And it's gonna give ingredientRows. Photos. So if you remember here on the photo rows, if you look at the code here in the detail.js, it's expecting them to just be direct images, just URLs.

[00:11:17]
It's gonna give them back to as objects, so I need to transform them. There's a couple of ways you could totally do this, you could say for, and I'll just leave this, this might be the easiest way. Let i = 0, i is less than photosRows.length, i++, we'll just call this const photos = [].

[00:11:45]
And we'll just say photos.push (photoRows[i]) url, right? So then this photos array will just be URLs, right? It's just gonna be strings instead of objects. And then here I can just say photos, photos. Right, these photos that I created up here or I can just say, photos like that.

[00:12:14]
Title, in photosRows, we can just grab the first one, right? And grab the title off of that, cuz it's gonna be the same for all of them and same with body. It's gonna be photosRows 0.body. But if you did a separate query for that, you would grab it out of the separate query.

[00:12:40]
So if you're reading through my completed code, it's a little bit more clever in terms of how it does it, just cuz that's how I write code. I try to be a little bit more explicit here as we wrote it together. But just to walk you through it really quick.

[00:12:56]
I have it here as well. API for completed recipes. Here, I do all the destructuring in one line. But notice it's grabbing photosRows and ingredientsRows the same way. It's just doing a little bit more terse syntax. And then here, instead of doing like a for loop to grab all the URLs out, it does does exactly the same thing.

[00:13:28]
Otherwise exactly the same. So let's get rid of that and get rid of that. And get rid of that. So now, let's go look at our project. This still works okay, let's look at cookies. We get butter, eggs, brown sugar, milk, sugar. And notice we're getting all of our photos down here and all the ingredients get put in here as well.

[00:14:01]
So it works. Hope it's very demonstrative of like this is really what is like writing SQL code together with back end code. This is generally what it feels like.