1 00:00:01,020 --> 00:00:07,890 So this time around, I want to create another database function that will allow me to search for availability, 2 00:00:08,040 --> 00:00:10,770 not for a given room, but for all rooms. 3 00:00:11,020 --> 00:00:17,130 And as I suggested last time, one of the things we want to do with this is to return not just whether 4 00:00:17,130 --> 00:00:23,850 or not there is availability, but to return the actual rooms for which there is availability, if any, 5 00:00:23,850 --> 00:00:24,970 for a given date range. 6 00:00:25,470 --> 00:00:29,730 So the first thing I want to do before I go any further is if you look at the function name for the 7 00:00:29,730 --> 00:00:35,250 one we did last time, search availability by dates, that's actually search availability for dates 8 00:00:35,460 --> 00:00:37,140 by room ID. 9 00:00:37,420 --> 00:00:42,090 So let's change the name of it and change it here by room ID. 10 00:00:42,810 --> 00:00:50,430 And then of course, we'll have to go back to a repository and change the name to by room ID and then 11 00:00:50,430 --> 00:00:51,510 back in our handlers. 12 00:00:51,510 --> 00:00:55,410 They'll be a mistake here somewhere, probably if we called that now. 13 00:00:55,410 --> 00:00:56,700 We haven't called it yet, so we're good. 14 00:00:57,300 --> 00:01:01,030 So just to fix that up, to make the function actually describe what it's going to do. 15 00:01:01,500 --> 00:01:04,110 Now let's go look at our database structure and think about this. 16 00:01:05,580 --> 00:01:10,080 So this is Dvor and I'm looking at the bookings database. 17 00:01:10,080 --> 00:01:19,890 And let's go to the public schema right here and look for public and tables and let's look at room restrictions. 18 00:01:20,730 --> 00:01:22,740 So I'll bring that up by double clicking on it. 19 00:01:23,220 --> 00:01:25,950 And it takes it a moment to list the columns for some reason. 20 00:01:25,950 --> 00:01:26,450 But there they are. 21 00:01:27,120 --> 00:01:34,860 So one problem I see immediately is that there are going to be two kinds of of blocks or entries that 22 00:01:34,860 --> 00:01:36,420 are permitted to go into this table. 23 00:01:36,900 --> 00:01:39,180 One is for reservations and that's no problem. 24 00:01:39,180 --> 00:01:40,500 We already have that one working. 25 00:01:40,500 --> 00:01:44,520 So it puts in the start date and date room ID, reservation ID. 26 00:01:45,120 --> 00:01:48,330 But the other kind will be where there's no actual reservation. 27 00:01:48,330 --> 00:01:54,810 But we're going to allow the property owner to close rooms on certain dates because they're the owners 28 00:01:54,810 --> 00:02:01,140 going on vacation or because the room is being closed for maintenance or whatever the reason might be. 29 00:02:01,140 --> 00:02:05,520 But the room is not available for certain dates, even though there's no reservation. 30 00:02:05,940 --> 00:02:10,830 And the problem that I see here is that reservation ID, which is this column right here. 31 00:02:11,220 --> 00:02:18,630 If you look at the actual restrictions for that reservation, ID has not known, which means it has 32 00:02:18,630 --> 00:02:19,710 to have a value in there. 33 00:02:19,830 --> 00:02:27,690 And because there's a foreign key on that particular row, you have to have an entry in there that corresponds 34 00:02:27,690 --> 00:02:29,130 to a reservation. 35 00:02:29,130 --> 00:02:34,820 And that's not possible because in some situations there is no reservation associated with it. 36 00:02:35,070 --> 00:02:36,170 So there's two things I'm going to do. 37 00:02:36,210 --> 00:02:39,270 The first thing is I'll go to resurrect restrictions and I'll put another row in here. 38 00:02:39,270 --> 00:02:45,900 So I open this up and I'll go to data and I'll put in a second entry. 39 00:02:46,020 --> 00:02:56,640 So let's add an entry down here, find the plus button and we'll call this owner block, which is as 40 00:02:56,640 --> 00:02:57,350 good a name as any. 41 00:02:57,360 --> 00:02:58,920 Nobody sees it except for us anyway. 42 00:02:59,700 --> 00:03:05,040 And the date will be 2011, 1920, 2011 nineteen. 43 00:03:05,410 --> 00:03:06,750 OK, so let's save that. 44 00:03:09,130 --> 00:03:14,350 OK, so now we have idea of two for honor block, and that's the one we're talking about, when someone 45 00:03:14,650 --> 00:03:20,950 enters a restriction for a given room for giving dates with an idea of two, we don't actually need 46 00:03:21,130 --> 00:03:21,790 the reservation. 47 00:03:23,080 --> 00:03:27,850 So I need to change this column under room restrictions, which is right here. 48 00:03:28,120 --> 00:03:32,710 I could just uncheck this for for restriction idea, which I believe is this row or for reservation. 49 00:03:32,730 --> 00:03:37,240 I could just uncheck this and click save and would be good, but that's actually not helpful. 50 00:03:37,240 --> 00:03:41,260 The next time I destroy my database and bring it back up by running. 51 00:03:41,260 --> 00:03:44,290 So to reset, I'd have to go manually change that again. 52 00:03:44,290 --> 00:03:46,360 And chances are I'm not going to remember that. 53 00:03:46,360 --> 00:03:49,910 And then I'll be banging my head against the wall trying to figure out why something doesn't work. 54 00:03:50,410 --> 00:03:57,630 So instead, let's go back to our idea and open our terminal window and make sure we're right in the 55 00:03:57,760 --> 00:03:58,480 in the right direction. 56 00:03:58,600 --> 00:04:12,280 And I am let's generate a new migration sota generate fiz add not no to reservation ID for restrictions. 57 00:04:12,850 --> 00:04:13,570 That's close enough. 58 00:04:14,620 --> 00:04:21,820 So it creates the the migration's for me and I will go down to my migration's right here and find that 59 00:04:21,820 --> 00:04:23,920 new migration and find the up. 60 00:04:24,310 --> 00:04:29,410 And what I want to do in this case is change a column so I can just say change column which is right 61 00:04:29,410 --> 00:04:32,170 in the FBI's documentation or the sort of documentation. 62 00:04:32,170 --> 00:04:35,020 But it's very simple syntax change column. 63 00:04:35,680 --> 00:04:45,670 The table I want to change it on is room restrictions and the column I want to change is reservation 64 00:04:45,670 --> 00:04:50,560 ID and it's still going to be an entry. 65 00:04:51,340 --> 00:04:52,540 So I'll put integer. 66 00:04:54,130 --> 00:04:57,700 And this time I want to put in No. 67 00:04:59,190 --> 00:05:07,180 True, I think that will do it, so let's open up a terminal window and find out. 68 00:05:07,620 --> 00:05:10,540 So to my right and there it is. 69 00:05:10,560 --> 00:05:12,450 So now we should have a not now. 70 00:05:12,450 --> 00:05:16,670 So we'll go back to Dover and hopefully it'll refresh this. 71 00:05:16,680 --> 00:05:18,350 Sometimes I have to take two or three tries. 72 00:05:18,360 --> 00:05:18,980 Yes, there it is. 73 00:05:18,990 --> 00:05:20,300 Now, it is not null. 74 00:05:20,310 --> 00:05:24,890 That means now it is not not null, which means I'm allowed to enter a null in there. 75 00:05:25,410 --> 00:05:33,480 So when I enter a reservation or enter a restriction for a room that happens to be an honor block and 76 00:05:33,480 --> 00:05:39,600 not a reservation, I can just not specify reservation ID and by default it will be no. 77 00:05:39,600 --> 00:05:40,220 And that's fine. 78 00:05:40,920 --> 00:05:42,280 So that solves that problem. 79 00:05:42,300 --> 00:05:45,090 So now I have both restriction types that I need. 80 00:05:45,090 --> 00:05:50,030 Now I have the ability to enter a restriction, interim restrictions without a reservation ID. 81 00:05:50,400 --> 00:05:52,530 So now let's get back to our actual query. 82 00:05:52,560 --> 00:06:01,800 So I'm going to hide this window and open up postcode just because it has an easier to read signal window. 83 00:06:02,310 --> 00:06:07,180 So I'll open my bookings database and I'll open an empty query window. 84 00:06:07,650 --> 00:06:11,460 Now, what we're going to do this time is very similar to what we did last time. 85 00:06:11,460 --> 00:06:16,080 We're where we were looking for availability, for a given room, for a given date range. 86 00:06:16,650 --> 00:06:22,380 But this time I want to get more than just a true or a false or the number of rows that exist in the 87 00:06:22,380 --> 00:06:23,430 room restrictions table. 88 00:06:23,700 --> 00:06:29,820 This time I want to get the room ID because I'm going to need that and the room name, but only for 89 00:06:29,970 --> 00:06:30,870 available dates. 90 00:06:30,990 --> 00:06:35,760 So I know I need the remedy in the room name and those exist in the room table so I can type select 91 00:06:36,030 --> 00:06:41,580 and I'll put this on another line and I'm going to use an alias, Arnaut ID, our room name. 92 00:06:44,110 --> 00:06:51,970 From the table rooms with the alias are that links it up here just to make it more readable and I need 93 00:06:51,970 --> 00:06:52,660 to wear clothes. 94 00:06:53,680 --> 00:06:59,080 And here's the part where I can actually get to where I can determine whether or not the rooms are available. 95 00:06:59,090 --> 00:07:01,120 And it's very similar to what we did last time. 96 00:07:01,120 --> 00:07:05,200 But this time we're going to use a sub query, which I've not seen before. 97 00:07:05,200 --> 00:07:13,600 But it's not that difficult where our ID, the ID from the rooms table is not in and the syntax is just 98 00:07:13,600 --> 00:07:14,290 not in. 99 00:07:14,500 --> 00:07:18,020 And then you have a sub query and the sub query is just another query. 100 00:07:18,040 --> 00:07:22,620 So what I'm going to do is almost identical to what I did in the last query we wrote. 101 00:07:23,080 --> 00:07:24,040 I'm going to say. 102 00:07:26,200 --> 00:07:30,190 Select room I.D. from room. 103 00:07:32,420 --> 00:07:33,410 Restrictions. 104 00:07:38,180 --> 00:07:44,140 And I'll call it per hour, just so it's readable and I can put an hour here or reminds something of 105 00:07:44,150 --> 00:07:51,500 my alias where my start date and I'll choose 20, 21, zero to 19, because I know that's going to be 106 00:07:51,500 --> 00:07:58,910 available is less than our end date, exactly the same as last time around end date. 107 00:07:59,960 --> 00:08:08,020 And my end date, 20, 21, zero to 21 is greater than our start date. 108 00:08:09,140 --> 00:08:10,660 And then I have to close my parentheses. 109 00:08:11,090 --> 00:08:17,240 So if you think about what's happening here is I'm saying get me all of the IDs and room names from 110 00:08:17,240 --> 00:08:23,960 the rooms tables where the ID that's in the room table is not in whatever this query returns. 111 00:08:23,960 --> 00:08:30,110 And this is looking for room remedy in the room restrictions table using the same syntax we did last 112 00:08:30,110 --> 00:08:32,130 time to determine whether or not something is available. 113 00:08:32,480 --> 00:08:36,380 So when I run this, I get both rooms are available. 114 00:08:37,010 --> 00:08:42,890 But if I look at my room restrictions table to find out what exactly exists as a booking, I have a 115 00:08:42,890 --> 00:08:48,160 reservation from February the 1st of 2021 to February the 4th of 2021. 116 00:08:48,170 --> 00:09:01,310 So if I go back to my school query and change this to say, 2021, 02, 02 to 21, 2001, 02, 03, 117 00:09:03,170 --> 00:09:04,920 I should only get one available room. 118 00:09:05,540 --> 00:09:06,170 And I do. 119 00:09:06,200 --> 00:09:07,500 So this query works. 120 00:09:07,520 --> 00:09:17,480 So what I'm going to do is copy this, query the entire query and go put it in my in my ID and write 121 00:09:17,480 --> 00:09:22,700 the function to make this search work, because this looks like it's going to do exactly what I want. 122 00:09:25,120 --> 00:09:33,400 And close this and close this and find our Postgres and create a new function funk, and this again 123 00:09:33,400 --> 00:09:42,730 will have the receiver of Postgrads DB Repo and this one I'll call search availability availability 124 00:09:42,940 --> 00:09:45,280 for all rooms. 125 00:09:46,900 --> 00:09:51,310 And it's going to take a start and an end, which are of time, dot time. 126 00:09:53,170 --> 00:09:55,960 And this time I don't need the remedy because I'm searching all remedies. 127 00:09:56,200 --> 00:09:57,470 And what is going to return. 128 00:09:57,490 --> 00:09:58,920 Well, what is it going to return? 129 00:09:59,170 --> 00:10:01,870 I'm going to get the room name and the room ID. 130 00:10:02,710 --> 00:10:08,830 I could return to ense and potentially an error, but it seems to me that it's probably more useful 131 00:10:08,830 --> 00:10:17,860 to return a slice of models, court room and an error, if any. 132 00:10:18,760 --> 00:10:19,150 All right. 133 00:10:19,780 --> 00:10:21,460 So let's first of all, get our query in here. 134 00:10:21,460 --> 00:10:27,520 Query is assign the value of and I'll just put it in back ticks and paste in the query that I have, 135 00:10:27,520 --> 00:10:28,610 which I'll fix up in a moment. 136 00:10:29,320 --> 00:10:33,880 Let's go get our context so I don't have to retype this thing pasted in here. 137 00:10:35,590 --> 00:10:39,820 And let's clean up our query a little bit just so it's more readable. 138 00:10:42,400 --> 00:10:44,120 And I'll just move that over there. 139 00:10:44,170 --> 00:10:48,220 Now we have a query and I'll put this on its online. 140 00:10:51,160 --> 00:10:53,360 And they're now everything will fit nicely. 141 00:10:53,430 --> 00:10:56,520 OK, so there's our query now what do we want to do? 142 00:10:56,530 --> 00:11:00,910 We want to do exactly this sort of thing we did last time, except we're not going to be querying a 143 00:11:00,910 --> 00:11:01,990 row context. 144 00:11:02,260 --> 00:11:07,030 We'll be querying the context because we're potentially getting more than one row, which means I'm 145 00:11:07,030 --> 00:11:10,480 going to get a result that I don't care about, an error that I do. 146 00:11:10,750 --> 00:11:16,150 And that's going to be equal to IMDB dot query context. 147 00:11:18,720 --> 00:11:24,570 Pass the context, pass it the query and pass it any parameters we're going to use and we're going to 148 00:11:24,570 --> 00:11:31,740 use two, so we'll pass it start and end and now will replace these with our placeholder start. 149 00:11:33,750 --> 00:11:39,280 And and OK, then we check for error, if error is not equal to nil. 150 00:11:40,110 --> 00:11:41,220 We're going to return. 151 00:11:42,300 --> 00:11:47,490 We'll return a variable I haven't declared yet, which I'll call rooms and our error. 152 00:11:49,580 --> 00:12:00,290 And up here, let's just declare that variable var rooms, which will be a slice of models, dot room. 153 00:12:01,140 --> 00:12:08,300 OK, so once we've done this, once we have our hour, that shouldn't be ignored. 154 00:12:08,300 --> 00:12:08,930 That should be Rose. 155 00:12:08,960 --> 00:12:10,790 What is Queretaro context return. 156 00:12:10,790 --> 00:12:13,240 It returns rose. 157 00:12:13,250 --> 00:12:14,600 So I actually want the rose here. 158 00:12:14,600 --> 00:12:16,030 Not don't want to ignore the result. 159 00:12:16,460 --> 00:12:17,740 I need something to look at. 160 00:12:18,530 --> 00:12:24,020 So for Rose Dot next, what am I going to do. 161 00:12:24,140 --> 00:12:27,500 I am going to say, just like we did up here anywhere. 162 00:12:27,500 --> 00:12:29,420 We looked for a rose here. 163 00:12:29,420 --> 00:12:33,830 We had some actually we didn't do in this one, but we did it in this one. 164 00:12:34,220 --> 00:12:35,530 Connect to p.g. 165 00:12:35,690 --> 00:12:38,600 We can look exactly how we did it in get all rose. 166 00:12:38,990 --> 00:12:44,180 We say four rows next and then we scan for our rows so we can scan for that. 167 00:12:44,360 --> 00:12:47,120 Let's go back to here and say. 168 00:12:49,160 --> 00:12:53,530 Rose dot scan and I want to scan it into a particular destination. 169 00:12:53,550 --> 00:12:54,890 So what am I going to scan this into? 170 00:12:54,920 --> 00:12:57,470 Well, I'm only getting two things. 171 00:12:57,470 --> 00:13:02,930 I'm getting my ID and my real name so I can say I got it. 172 00:13:02,930 --> 00:13:11,270 First of all, declare a variable and my variable will be var room, which is a model model and start 173 00:13:11,270 --> 00:13:16,070 room, not reservation room. 174 00:13:17,210 --> 00:13:21,170 And I was looking into it, so I'll scan it into you. 175 00:13:21,200 --> 00:13:24,230 Just put it over here rheumy eyed. 176 00:13:26,040 --> 00:13:32,980 Because I'm selecting a ID and I'm going to scan into room, not name Romney. 177 00:13:34,470 --> 00:13:41,700 Now I have this variable room and I need to put it into my slice, which is of named rooms, so I can 178 00:13:41,700 --> 00:13:50,160 just say rooms equals append rooms, rooms, and then I'm going to append the value room. 179 00:13:51,720 --> 00:14:02,070 So I now have this slice, so I need to do some returns, return rooms and no, but I want to check 180 00:14:02,070 --> 00:14:04,410 for errors just like we did over here. 181 00:14:04,410 --> 00:14:08,940 So after we did our our our scan, we actually put that into a variable error. 182 00:14:08,940 --> 00:14:14,550 So let's go back into this and say error is assigned the value of this. 183 00:14:14,850 --> 00:14:20,220 And if error is not equal to nil, what I want to do, I want to return. 184 00:14:20,460 --> 00:14:22,350 I'll return the variable rooms. 185 00:14:22,530 --> 00:14:23,400 We're going to ignore it. 186 00:14:23,760 --> 00:14:24,540 And my error. 187 00:14:26,820 --> 00:14:33,930 And again, just as we did over here, once we did our actual check for our actual scan in the fallout 188 00:14:33,930 --> 00:14:34,640 for the rose. 189 00:14:34,890 --> 00:14:37,580 We also check to make sure there was an air one last time. 190 00:14:37,920 --> 00:14:40,910 So here we can do that right after the for loop. 191 00:14:41,820 --> 00:14:43,580 And we don't want to log here. 192 00:14:43,590 --> 00:14:44,830 We want to return our air. 193 00:14:44,850 --> 00:14:47,870 I don't do the same thing, return rooms and earth. 194 00:14:49,050 --> 00:14:51,050 So now I have this function. 195 00:14:51,060 --> 00:14:53,210 Let's just format it to make sure it all looks good. 196 00:14:53,220 --> 00:14:54,330 Let's give it a comment. 197 00:14:58,390 --> 00:15:10,990 Search availability for all rooms returns a slice of available rooms, if any, for given date range 198 00:15:11,410 --> 00:15:11,770 there. 199 00:15:12,730 --> 00:15:16,470 Now, I need to put this in my repository, so let's copy this whole thing. 200 00:15:17,260 --> 00:15:20,470 Go back to our repository and add this. 201 00:15:21,100 --> 00:15:25,000 Let's make sure everything compiles so I'll clear the screen run. 202 00:15:28,380 --> 00:15:36,360 Everything piles perfect, so now we're in a situation where we can actually begin to hook up our search 203 00:15:36,360 --> 00:15:40,570 availability screen to the screen where we can actually make a reservation. 204 00:15:40,590 --> 00:15:42,960 So we'll do that in the next couple of lectures.