1

00:00:01,240 --> 00:00:08,020

So the next thing we have to do is to write some code that allows us to query the database and see if



2

00:00:08,020 --> 00:00:12,160

a room we want to stay in is available for a certain date range.



3

00:00:12,280 --> 00:00:16,600

And right now, I only have one entry in my room restrictions table.



4

00:00:17,410 --> 00:00:24,930

And that is an entry that starts on February the 1st, 2021 and ends February the 4th, 2021.



5

00:00:25,600 --> 00:00:27,120

So you can think about it.



6

00:00:27,610 --> 00:00:31,770

Those are the only days that I currently have booked in my reservation calendar.



7

00:00:32,230 --> 00:00:34,640

So let's start a query.



8

00:00:34,660 --> 00:00:36,830

Let's see how we can figure out what we want to do.



9

00:00:36,850 --> 00:00:40,210

So what I want to do, and this is how you do comments and sequel is two dashes.



10

00:00:40,630 --> 00:00:52,300

I have existing reservation from 2021 02 01 to 2020, one zero two zero four.



11

00:00:52,300 --> 00:00:53,140

I think that's what it is.



12

00:00:53,720 --> 00:00:54,460

Let's make sure.



13

00:00:56,050 --> 00:01:00,320

Restrictions 020 one to 020 for Yes, that's correct.



14

00:01:00,780 --> 00:01:07,500

So let's write a query and what I want this query to do is I want to ask the database how many rows



15

00:01:07,680 --> 00:01:16,080

for restrictions in the room restrictions table do you have where my dates overlap in any way, shape



16

00:01:16,080 --> 00:01:22,680

or form your dates and if I get zero that I know the room's available for those dates and if I get one



17

00:01:22,710 --> 00:01:27,210

or more that I know there's a reservation that blocks that date range.



18

00:01:27,210 --> 00:01:32,070

And if I have a really long date range, say I want to stay for three weeks, I might have five reservations



19

00:01:32,070 --> 00:01:35,390

that overlap, but I don't care if the number is not zero.



20

00:01:35,580 --> 00:01:38,180

I know my requested dates are not available.



21

00:01:38,610 --> 00:01:40,050

So how are we going to write that?



22

00:01:40,590 --> 00:01:44,940

Let's write a select statement because clearly we're selecting information from the database and I want



23

00:01:44,940 --> 00:01:46,350

to select count.



24

00:01:46,350 --> 00:01:48,630

I'd give me the number of rows.



25

00:01:48,630 --> 00:01:53,040

That's all this building function does is count the number of occurrences of that field.



26

00:01:53,940 --> 00:01:56,880

And I want to select it from and I'm formatting this.



27

00:01:56,880 --> 00:01:59,400

So it's quite readable room restrictions.



28

00:02:01,380 --> 00:02:07,260

And then I need to build up a where clause where and I'm going to do it this way where and I'll just



29

00:02:07,260 --> 00:02:08,220

hardcoded the dates.



30

00:02:08,220 --> 00:02:15,540

Let's say I want to stay right now on dates that are exactly the same as the date for the one existing



31

00:02:15,540 --> 00:02:16,440

reservation I have.



32

00:02:16,440 --> 00:02:24,030

So I'm going to start by putting in single quotes twenty twenty one zero two zero one, which is my



33

00:02:24,030 --> 00:02:24,630

start date.



34

00:02:24,960 --> 00:02:36,990

If that's less than the end date and my end date, 2021 zero two zero four is greater than the start



35

00:02:36,990 --> 00:02:37,260

date.



36

00:02:38,160 --> 00:02:39,030

So that's my query.



37

00:02:39,660 --> 00:02:48,270

So I expect this to return one row because my start date is less than the end date of the reservation.



38

00:02:48,270 --> 00:02:57,360

Remember, the end date is this date and this date, 02 01 is less than the end date 02 04 and my start



39

00:02:57,360 --> 00:03:02,550

date 02 04 is greater than the start date, which is zero two zero one.



40

00:03:02,700 --> 00:03:04,500

So this should return one row.



41

00:03:05,490 --> 00:03:06,210

And it does.



42

00:03:06,240 --> 00:03:07,620

There's one right down there.



43

00:03:07,620 --> 00:03:09,300

I'm going to move this up so we can see a little bit.



44

00:03:10,500 --> 00:03:21,390

Now when we copy this one and I'll put a comment on this say I'm going to say that search date is exactly



45

00:03:21,570 --> 00:03:25,230

the same as the existing reservation.



46

00:03:26,340 --> 00:03:30,780

So let me copy this whole thing and paste it down below.



47

00:03:30,780 --> 00:03:34,330

So I'll put a semicolon in that and I'll paste this one here.



48

00:03:34,360 --> 00:03:35,460

But a semicolon after that.



49

00:03:35,460 --> 00:03:41,610

To my second case, I want to say my start date is before the start date of the restriction, but the



50

00:03:41,610 --> 00:03:42,300

end is the same.



51

00:03:42,630 --> 00:03:47,190

So search date, I'll say start date is same.



52

00:03:49,660 --> 00:03:55,180

As existing reservation, no, before existing reservation.



53

00:03:59,060 --> 00:04:05,060

And Date is saying, I'm just trying to see if this covers all the cases, so my start date is going



54

00:04:05,060 --> 00:04:11,180

to be before the existing reservation, so I'll make it zero one thirty one because that's before the



55

00:04:11,180 --> 00:04:12,860

start date of the existing reservation.



56

00:04:13,220 --> 00:04:18,620

But my end date, my end date, 02 04 is exactly the same as the end date of the reservation.



57

00:04:18,620 --> 00:04:28,400

And it is 21 02 04, 21 02 04 here 21 01 31 one is before 2001 02 01.



58

00:04:28,580 --> 00:04:28,910

Great.



59

00:04:29,270 --> 00:04:32,420

I want this to return one row and it does.



60

00:04:32,690 --> 00:04:34,940

OK, let's try a third case.



61

00:04:35,570 --> 00:04:40,580

So again, I'll copy this entire thing and I'll paste it below.



62

00:04:42,470 --> 00:04:52,160

And now I want my end date to be after the end date of the restrictions and date is after existing reservation



63

00:04:52,160 --> 00:04:55,040

and date and start date.



64

00:04:57,510 --> 00:04:58,120

Is the same.



65

00:04:58,770 --> 00:05:08,910

So let's change my start date back to 02 01 and let's make my end date of seven, which is after the



66

00:05:08,910 --> 00:05:09,920

end date of the reservation.



67

00:05:10,290 --> 00:05:14,340

Let's run that statement and we get one exactly as it should be.



68

00:05:14,700 --> 00:05:15,720

Let's try another case.



69

00:05:15,720 --> 00:05:18,180

We want to cover all the possible cases here.



70

00:05:18,450 --> 00:05:22,590

Otherwise we're going to have double bookings and two people will be showing up on the same day for



71

00:05:22,590 --> 00:05:23,250

the same room.



72

00:05:23,580 --> 00:05:24,920

And that's not good.



73

00:05:25,680 --> 00:05:27,780

So this time, let's put the dates.



74

00:05:30,310 --> 00:05:40,540

Search dates, the ones we're searching on, are outside of all existing reservations, but cover the



75

00:05:40,570 --> 00:05:41,340

reservation.



76

00:05:41,680 --> 00:05:47,310

In other words, my start date will be before the existing start date.



77

00:05:47,320 --> 00:05:50,350

So 01 01, that's before.



78

00:05:50,380 --> 00:05:52,660

And my end date will just make it 03, 07.



79

00:05:52,810 --> 00:05:54,130

So here we have a start date.



80

00:05:54,130 --> 00:05:57,360

That's before an existing reservation and an end date.



81

00:05:57,370 --> 00:06:02,580

That's after an existing reservation, which technically means that should not be available.



82

00:06:02,770 --> 00:06:03,820

This should return one.



83

00:06:04,660 --> 00:06:06,730

Execute the statement and it does.



84

00:06:06,910 --> 00:06:10,390

OK, let's try the next category or the next case.



85

00:06:12,170 --> 00:06:18,620

So so far, we have dates that are exactly the same and that works, we have a start date before the



86

00:06:18,620 --> 00:06:23,420

existing reservation, but the end date is on the date of the existing reservation that works.



87

00:06:23,870 --> 00:06:27,530

We have our end date of our search after the existing reservation end date.



88

00:06:27,530 --> 00:06:29,750

But the start date is the same that works.



89

00:06:30,260 --> 00:06:36,320

Now we have search dates entirely outside of all existing reservations, but cover an existing reservation.



90

00:06:36,320 --> 00:06:39,240

There's one inside my start date and end date that works.



91

00:06:40,040 --> 00:06:40,910

So what's next?



92

00:06:41,450 --> 00:06:46,230

Let's say dates are outside of the existing reservation entirely.



93

00:06:46,250 --> 00:06:49,190

So this one say certain dates are outside.



94

00:06:53,700 --> 00:07:04,610

Of all existing reservations, so outside, so let's go to zero seven zero one two zero seven zero seven,



95

00:07:04,920 --> 00:07:06,680

and I know there's no reservations.



96

00:07:06,750 --> 00:07:10,140

This should return zero and it does.



97

00:07:10,200 --> 00:07:11,190

So far, so good.



98

00:07:11,460 --> 00:07:12,780

So what's the last case?



99

00:07:12,780 --> 00:07:13,530

I have to check?



100

00:07:13,560 --> 00:07:16,950

Let's copy this and paste it in here.



101

00:07:18,500 --> 00:07:27,500

Paste search dates are inside of existing reservation, so here's a situation where someone searching



102

00:07:27,500 --> 00:07:34,490

for dates where their start date is after the existing start date of an existing reservation, and the



103

00:07:34,490 --> 00:07:38,840

end date is before the existing end date of an existing reservation.



104

00:07:38,870 --> 00:07:44,330

So here's one where my requested dates fall entirely inside an existing restriction.



105

00:07:44,750 --> 00:07:56,000

So my dates that I have are 020 one to two for so I can change this to my start date would be 02 02



106

00:07:57,740 --> 00:08:00,950

and my end date is 02 03.



107

00:08:02,520 --> 00:08:08,700

And this should return one, and it does so it looks like this query will actually do the job for us.



108

00:08:08,710 --> 00:08:14,460

So I'm going to copy this query and we'll go and create the database method that can do the search for



109

00:08:14,460 --> 00:08:14,670

us.



110

00:08:14,850 --> 00:08:18,160

We'll just start with a really simple version that ignores remedies.



111

00:08:18,830 --> 00:08:24,420

So let's go back to our database or to our Idy and find our Postgres and we'll create a new function.



112

00:08:25,380 --> 00:08:34,650

And I will call this Thunk with the receiver of Postgres DB Repo search availability



113

00:08:37,080 --> 00:08:38,190

by dates.



114

00:08:38,190 --> 00:08:45,690

And that will take two parameters start and end, which will be of type time, short time, and it will



115

00:08:45,690 --> 00:08:51,510

return a anent and potentially an error into an error or.



116

00:08:54,240 --> 00:08:57,500

So let's just paste that query in here right now so we don't lose it.



117

00:08:57,540 --> 00:09:01,950

Query is assigned the value of and I'll just paste and we'll fix this in a minute.



118

00:09:02,370 --> 00:09:07,230

But I need my contact stuff and I hate typing this out, so I'm going to copy and paste it.



119

00:09:08,080 --> 00:09:10,950

OK, and let's format this a little better.



120

00:09:10,950 --> 00:09:13,790

So because I'm using that tactics, I can format this however I want.



121

00:09:14,520 --> 00:09:16,800

So I will now move this over.



122

00:09:16,800 --> 00:09:24,750

So it's nice to read and change this to a placeholder dollar sign one and that will be my start date



123

00:09:25,080 --> 00:09:28,200

and change this to a placeholder and that will be my end it.



124

00:09:29,220 --> 00:09:40,890

And down here I will say ver Nimroz, which will be anent and I will say return Nimroz and nil because



125

00:09:40,890 --> 00:09:42,670

there's no area we haven't generated one.



126

00:09:43,020 --> 00:09:44,340

So here's my query now.



127

00:09:44,340 --> 00:09:45,780

I need to execute this query.



128

00:09:45,810 --> 00:09:48,570

So let's execute this query and we can do it right here.



129

00:09:49,620 --> 00:09:53,280

And I'm going to get a result that I don't care about and I'm going to get an error that I that I do



130

00:09:53,280 --> 00:09:53,820

care about.



131

00:09:54,390 --> 00:09:58,770

And I'm going to call M DB query context.



132

00:10:01,420 --> 00:10:10,960

There it is, X plus my query, plus my placeholders start, and so now I check for my error.



133

00:10:11,140 --> 00:10:14,200

If error is not equal to nil, then what do I do?



134

00:10:14,800 --> 00:10:19,750

I say, actually, I can make this a query, real context, because I'm only getting one number back.



135

00:10:19,780 --> 00:10:23,230

Let me change that query row context.



136

00:10:25,250 --> 00:10:26,780

And this will return a role.



137

00:10:29,690 --> 00:10:31,170

And there's no air check yet.



138

00:10:32,270 --> 00:10:36,680

So my query, real context, if you recall, we can actually go look at that because I think I have



139

00:10:36,680 --> 00:10:37,290

it open here.



140

00:10:39,050 --> 00:10:45,480

I do connect to query row his works the same way as query row context, so query row.



141

00:10:46,280 --> 00:10:46,850

There it is.



142

00:10:48,230 --> 00:10:52,780

I do my get my row and then I scan my row, so and that generates an error.



143

00:10:52,790 --> 00:10:53,930

So that's all I have to do.



144

00:10:54,110 --> 00:11:01,270

So I can go back to my other project here and say query real context works exactly the same way.



145

00:11:01,520 --> 00:11:07,760

Error is assigned the value of Rodon scan and I'm only going to scan into the one variable I have that



146

00:11:07,760 --> 00:11:13,130

I have to move in a minute called Nimroz and I'll move this up here.



147

00:11:13,130 --> 00:11:16,860

So the declaration comes before the variable, otherwise I can't use it.



148

00:11:18,950 --> 00:11:26,210

So if error is not equal to nil return, I don't care what the INT is because we're not going to pay



149

00:11:26,210 --> 00:11:26,870

attention to it.



150

00:11:27,050 --> 00:11:28,130

I just care about the error.



151

00:11:28,160 --> 00:11:32,720

So I'll return that otherwise return and I can already have it there.



152

00:11:32,720 --> 00:11:34,160

Return no and no.



153

00:11:35,480 --> 00:11:36,340

So let's look at this.



154

00:11:36,620 --> 00:11:37,700

I'm going to do my query.



155

00:11:37,730 --> 00:11:42,080

I'm going to pass it my start date and end date, which are both of the format time, short time.



156

00:11:42,530 --> 00:11:49,310

And then I do my query, give me the number of rows from Rome restrictions where my start date is less



157

00:11:49,310 --> 00:11:54,570

than your end date or so and my end date is greater than your start date.



158

00:11:55,250 --> 00:11:56,360

I do the query.



159

00:11:56,720 --> 00:11:58,700

I scan the value into Nimroz.



160

00:11:58,700 --> 00:12:03,050

I check for an error just to be sure, and then I return the number of rows.



161

00:12:03,050 --> 00:12:05,720

If this number is zero, then there is availability.



162

00:12:06,260 --> 00:12:10,160

If this number is is greater than zero, then there is no availability.



163

00:12:10,730 --> 00:12:12,620

So I could do it this way.



164

00:12:12,620 --> 00:12:16,010

I can return an an error, but it might be simpler just to say bool.



165

00:12:17,510 --> 00:12:19,190

So return false.



166

00:12:21,180 --> 00:12:23,400

Otherwise, return true.



167

00:12:27,510 --> 00:12:36,420

No, if air is not equal to no return, false and air otherwise, if Nimroz.



168

00:12:38,170 --> 00:12:42,910

Equals exactly zero, then return true in nil.



169

00:12:44,990 --> 00:12:49,780

Otherwise, return false and nil there.



170

00:12:49,940 --> 00:12:53,510

Now, I don't even have to care about the number, I just care about the boolean value.



171

00:12:54,050 --> 00:12:58,700

So I get it true when you have availability and I get a false when you don't have availability.



172

00:12:58,730 --> 00:13:00,800

And that, I think is really, really simple.



173

00:13:00,860 --> 00:13:03,200

So let's copy this and give it a comment.



174

00:13:04,520 --> 00:13:17,120

Returns true if availability exists and false if no availability exists.



175

00:13:18,020 --> 00:13:21,170

OK, I misspelled availability there.



176

00:13:21,800 --> 00:13:26,400

Now this is a good starting point, but obviously it has some limitations.



177

00:13:26,420 --> 00:13:34,100

For example, I'm actually searching all rooms and just saying, yes, there is availability, but I'm



178

00:13:34,100 --> 00:13:37,200

not giving you any information about what rooms are available.



179

00:13:37,640 --> 00:13:38,620

Is that helpful?



180

00:13:38,810 --> 00:13:41,330

It's helpful if you're searching for a single room.



181

00:13:41,420 --> 00:13:49,430

So let's add another parameter here and we'll call it room ID of type it and we'll modify our work laws



182

00:13:49,430 --> 00:13:55,340

here to say where room ID equals Dorson one.



183

00:13:57,910 --> 00:13:58,630

And.



184

00:14:00,500 --> 00:14:01,380

The rest stays the same.



185

00:14:01,730 --> 00:14:07,910

So this limits it to a single room, which means I have to change this dollar, sign one to a two and



186

00:14:07,910 --> 00:14:12,620

this two to a three and prepend or put in here Brumidi.



187

00:14:14,730 --> 00:14:23,220

And change my comment to say, returns true if Israel it exists for room ID and false if no availability



188

00:14:24,120 --> 00:14:24,510

there.



189

00:14:24,900 --> 00:14:31,170

That's a much useful use, much more useful method of searching for availability for a given room.



190

00:14:31,950 --> 00:14:36,440

Now, we also want the ability to and I will go back and make sure this application is running.



191

00:14:36,450 --> 00:14:37,080

It is.



192

00:14:38,270 --> 00:14:43,880

And go to our room ID, so local or local server localhost eighty-eight.



193

00:14:48,080 --> 00:14:54,100

We also want the ability to do this to search availability, make reservation now for all rooms.



194

00:14:54,650 --> 00:14:59,240

And when I do that, I want to get more than just a true or a false.



195

00:14:59,480 --> 00:15:04,670

What I want to get instead is look at this function, something very similar to this.



196

00:15:04,970 --> 00:15:11,740

But I want to return the rooms that are available with the room name as well at a minimum.



197

00:15:11,750 --> 00:15:13,820

So I want that information to be returned.



198

00:15:14,150 --> 00:15:18,620

So I'm not going to be returning a bool in the next function I write, which will be in the next lecture,



199

00:15:18,890 --> 00:15:24,980

I'll be returning all of the room names and IDs that matter that have availability on the dates that



200

00:15:24,980 --> 00:15:25,810

we specify.



201

00:15:25,970 --> 00:15:30,750

OK, that's not too difficult, but we'll do it in the next lecture right now.



202

00:15:30,800 --> 00:15:37,490

Right now, I need to take this and add it to my repository or it's not going to be available at all



203

00:15:38,090 --> 00:15:38,420

there.



204

00:15:39,350 --> 00:15:42,200

So I save that and we should be good to go.



205

00:15:42,320 --> 00:15:47,480

So in the next lecture, we'll write our second query, our second database function to search for availability



206

00:15:47,480 --> 00:15:53,700

for all rooms, and then we will hook them up to her handlers and make our search function.



207

00:15:53,810 --> 00:15:54,320

Finally.