Ingredients API Solution

[00:00:00]
>> Let's pop over to our code, Here in ingredients, I'm gonna close this for a little bit. First thing here, we will connect to postgres, we're gonna say, const pg = require ('pg'). const pool = new pg.pool. User: 'postgres' Host: 'localhost' database, whatever you call it your database it could be postgres, it should be 'recipeguru', if you've been following the wrong.

[00:00:46]
Password: it's whatever you set the password as. I put mine as lol because I'm a child. And port: 5432 cuz that is the default port for postgres. Okay, this will connect us to the database now, and we'll have a pool that we can use. So whenever someone calls backs or type, we wanna return to them, the ingredients of that specific type, right?

[00:01:21]
So here we wanna say const {rows} = await Pool.query Something like, "SELECT * FROM ingredients WHERE type = $1". Okay, and then here we're just gonna provide type into, the parameterized query here. Await here is going to do the query, it'll pause the execution here, and then it'll wait for that rows to come back.

[00:02:08]
So once status is back, then here we're gonna say, instead of res.status{501}, we'll do a 200. That's actually optional, you could actually just delete that cuz it does that by default. And then the status here, you can delete that cuz we don't actually need it. And then you can do rows here like that.

[00:02:34]
Should work now, if we go back over to our Recipe Guru website. You can see so far this is working the way we expect it to. We have fruit, we have veggies, we have meat, and we have other. That all works. Your carousel won't work yet because the client side code is Aryan cuz that's how I wrote it.

[00:03:00]
So let's make the other one work as well. So, there's a bunch of ways that you could do this. I'm just gonna show you how I did it. Delete that. We're gonna say, let whereClause; say, const params = [page * 5]. So, if page comes back as zero, that means you're on the zero with page.

[00:03:35]
You want everything from 0 to the 5th or the 4th, it'll be the 5th one but index 4, right. If it comes back with page one, this will be 1 times 5, so it'll be 5, answer will be 5, 10, 15, 20. Okay, if there's a search (term), then I'm going to add my whereClause = 'WHERE CONCAT and I put (title and type).

[00:04:05]
If you just did title, that's fine. I'm searching across title and type. And then I'm gonna say, ILIKE$2' Okay, and then we're gonna say, params.push(term) Again, it doesn't matter if $2 comes before $1 in the query, it's still gonna go on the order that we tell it to, right?

[00:04:40]
And then now I'm gonna say let const rows, equals, rather const {rows} = await pool.query "SELECT * FROM ingredients" And then, optionally, I might need this whereClause. So if they put a search term in there, then we wanna filter by that like, write this ILIKE here. So we can do this whereClause like this.

[00:05:25]
If whereClause is just an empty string, right? We can even put that here. Then this will just be nothing in whitespace, right? Which is fine. So it'll either filter or not And then we'll do OFFSET $1, which is coming from this, right? And then we'll do LIMIT 5.

[00:06:00]
Okay, and then here we can get rid of the status where you can put 200 there up to you. Give it as status not implemented, and we can say rows like that. If you're not familiar with this rows like that where I just have one it's both the key and the value, right?

[00:06:20]
Where I have, the row key is going to be assigned rows that I get from right here. You can just do shorthand and make it rows like that. Okay, so if we go back over here and refresh, Still getting a little bit of an error here. Did I crash my server?

[00:06:48]
I did, probably. There's no $1 parameter. Yeah, Dustin.
>> Comment in the chat that no parameters are passed to pull that query.
>> That's correct. Thank you. So you have to pass in the params right there, or else it's gonna crash, cuz it's trying to look for variables that you didn't provide to it.

[00:07:15]
So now if I refresh, everything is coming in okay. And it's paging okay, which is great. So there's one more thing I kind of wanna show you. It's a kind of peeking ahead a little bit. Obviously, there are no more pages here, right? Because it only returned one, so how do we know if there are more pages or not?

[00:07:49]
Well, we have to do something to kinda pick ahead in our query. So, there's a whole section on window functions, but I'm gonna show you really quickly how to do this. 'SELECT* and then we're gonna say, COUNT (*) :: INT AS total_count So what this is gonna do is this is actually going to go and count how many rows total exist for this query.

[00:08:22]
It's gonna return them as a number called total count. Let me just show you what that looks like. So if I refresh the page, and they go to my Network tab, And it crashed, why did that crash? Yep, okay. COUNT (*) OVER () :: INT Okay, not crashing, network.

[00:09:04]
I'm going to show you the results first, and then I'll explain to you why it happened that way. So we have this search, this is what's coming back from the API, let's make this a little bit bigger. So this is our search, one that we were just implementing, you can see all the rows coming back here.

[00:09:22]
Id:1, red pepper, so this matches up to this one right here, right? Title, red pepper, we see that. Image, red pepper we see that right there, type vegetable we see that right there. And then we have this count 31. That's how many total rows exist for this entire query that we have, right?

[00:09:47]
And it's actually on every single one, total count, total count, total count, right? And it's the same on every one of them. So, this is what's called a window function that's selecting across rows. So, there's a whole section on it. But the whole reason that we did that, is so now if we paginate all of our information here, eventually it'll rise to the last one, and it can know there's only 31 in this set, and we're way past that, I'm done, right?

[00:10:22]
That's called the window function. And just to prove to you that I'm not lying, we will explain that in depth here in window functions. We'll actually explain part of it as well here in aggregation, which we're about to do. But suffice to say, this part right here is counting how many results we have for that entire set.

[00:10:53]
It'll change right so going back here if I type in berry Now we're too far away. Did I crush now? I did it. So this is fine, we're learning all the things now. My error here is that offset must not be negative. [INAUDIBLE]
>> In your solution for params.push in the whereClause, you actually have the parentheses around the term Rather than just pushing in term, you actually put in the [INAUDIBLE].

[00:11:43]
>> Yeah, good point. Okay, so what you have to do here, which I have also not done correctly here, is that you have to put in term like that, right? Which is what I was alluding to earlier with my note that you have to wrap it yourself in the percentages.

[00:12:03]
Okay, try that again.
>> Curious, what if you just put percent around the $1 in the query, will that still work?
>> No, which is why I specifically called it out there. So if you wanted to put it here, right? [CROSSTALK]
>> That $2, that's what I mean.

[00:12:28]
That wouldn't work?
>> Pretty sure it doesn't work, cool, So I wanted to show you search for berry. You have to take these out again. All right, berry, we get three berries there and come on. Berry, Just to demonstrate, you look to your rows. Notice this count over, so the total count it's only counting from that sub query, right?

[00:13:07]
So including that whereClause there's only three rows that will be returned here. So that's what the window function does for you. Okay, so that was sufficiently fun and messy, which is, all good code is worth being messy about. But I think we also kind of learned a couple.

[00:13:32]
One of the things I really do like about postgres is, it gives you very good error messages. When something goes wrong, it's typically this like, hey, you did something here, and I don't get it, you should look at it. Which usually is pretty close to leading you to a solution.

[00:13:48]
Frequently even says like you did this, and you probably wanted to do this.