00:00 Even though we have an ORM, sometimes you need to reach in and do some raw SQL. So I have a real project and the search page is pretty complicated. There are a lot of filters and things, and this is a query from that real project that I've got.

00:18 So we're selecting starships and brand models and a bunch of other things. This is the search page that allows the user to say, I want to find a starship and I want to sort everything by the star ports where they are located that are closest to me,

00:37 so to my geo location. So we have the latitude and longitude, and the select statement is fine, but then we have this nested select, so you can do nested queries, and we're going to be doing that in this exercise as a nested order by. Then we're selecting the average rating because we want to sort by

00:55 the host ratings as well as the ship ratings too. So we're grabbing all the ship ratings, so we're going to order those as well. We're also averaging that so that we can display stars and stuff like that. Then we're selecting on the ship, but then we're doing an inner join.

01:13 We're going to be doing joins in this exercise. We have this nested select. So this is the part where we're joining by the star port that is in the city where they are closest to. It's pretty crazy how this works,

01:31 but it is awesome that it works at all. So feel free to dive into that if you want to a little bit. I'll make sure there's a link to the actual project as well. But yeah, so we're going to select from the star port, order by their distance, and limit to one. So now we have the closest star port

01:49 to the city that they have selected in their selection. There are a couple other features of this particular search page. But search pages, typically, if you're going to be writing the search implementation yourself,

02:06 you're going to be writing a fair bit of raw SQL. Yeah, AI definitely helped me write this. So we're actually, in this exercise, I'm going to show you how I use AI to assist me in my programming. So, and how you make sure to avoid it leading you down the wrong path.

02:26 So that should be fun. Luckily for us in Prisma, there's not a really great way to represent this. Like, I don't know if there's any ORM that could nicely represent a query like this. It's just, yeah, far too complicated. But, I mean, maybe it's possible.

02:45 Let me know. But Prisma does have this escape patch for querying, just like doing a regular SQL query. And so that is exactly how I accomplish that. These raw queries is by using this query raw API from Prisma.

03:04 And what's cool is that you may have heard of the dangers of doing a raw query. It's like, oh no, now people can do their comment line and then drop table, Tommy Tables or whatever. Not good stuff. But the query raw API will auto escape everything.

03:22 So it's a prepared statement. And you don't have to worry about that when you're using this API, which is nice. And then the other problem with this is that we are not type safe. As soon as you do this query raw, what comes back is unknown.

03:38 And so we can't actually use it, at least in TypeScript, until we parse it and check the type that it is. Luckily for us, we have Zod. And so we can do that with Zod. So here we can parse the search results. And then when it comes back, if there's an error,

03:57 then we can just let people know there was a parsing error. Otherwise we've got our data and it's all type safe and it's awesome. And then of course you can disable this in production if that's a problem. We're going to talk about that in this exercise. So quite a bit of stuff to get through in this exercise. It's going to be a lot of fun. I think that you're going to enjoy this.

04:14 It's always kind of fun to go this low level when you work with these higher level tools all the time. So have a good time with this. There's a lot of power here and with great power comes great responsibility. So yeah, thank you, Peter Parker. Have a good one.