So this time around, I want to create another database function that will allow me to search for availability,
not for a given room, but for all rooms.
And as I suggested last time, one of the things we want to do with this is to return not just whether
or not there is availability, but to return the actual rooms for which there is availability, if any,
for a given date range.
So the first thing I want to do before I go any further is if you look at the function name for the
one we did last time, search availability by dates, that's actually search availability for dates
by room ID.
So let's change the name of it and change it here by room ID.
And then of course, we'll have to go back to a repository and change the name to by room ID and then
back in our handlers.
They'll be a mistake here somewhere, probably if we called that now.
We haven't called it yet, so we're good.
So just to fix that up, to make the function actually describe what it's going to do.
Now let's go look at our database structure and think about this.
So this is Dvor and I'm looking at the bookings database.
And let's go to the public schema right here and look for public and tables and let's look at room restrictions.
So I'll bring that up by double clicking on it.
And it takes it a moment to list the columns for some reason.
But there they are.
So one problem I see immediately is that there are going to be two kinds of of blocks or entries that
are permitted to go into this table.
One is for reservations and that's no problem.
We already have that one working.
So it puts in the start date and date room ID, reservation ID.
But the other kind will be where there's no actual reservation.
But we're going to allow the property owner to close rooms on certain dates because they're the owners
going on vacation or because the room is being closed for maintenance or whatever the reason might be.
But the room is not available for certain dates, even though there's no reservation.
And the problem that I see here is that reservation ID, which is this column right here.
If you look at the actual restrictions for that reservation, ID has not known, which means it has
to have a value in there.
And because there's a foreign key on that particular row, you have to have an entry in there that corresponds
to a reservation.
And that's not possible because in some situations there is no reservation associated with it.
So there's two things I'm going to do.
The first thing is I'll go to resurrect restrictions and I'll put another row in here.
So I open this up and I'll go to data and I'll put in a second entry.
So let's add an entry down here, find the plus button and we'll call this owner block, which is as
good a name as any.
Nobody sees it except for us anyway.
And the date will be 2011, 1920, 2011 nineteen.
OK, so let's save that.
OK, so now we have idea of two for honor block, and that's the one we're talking about, when someone
enters a restriction for a given room for giving dates with an idea of two, we don't actually need
the reservation.
So I need to change this column under room restrictions, which is right here.
I could just uncheck this for for restriction idea, which I believe is this row or for reservation.
I could just uncheck this and click save and would be good, but that's actually not helpful.
The next time I destroy my database and bring it back up by running.
So to reset, I'd have to go manually change that again.
And chances are I'm not going to remember that.
And then I'll be banging my head against the wall trying to figure out why something doesn't work.
So instead, let's go back to our idea and open our terminal window and make sure we're right in the
in the right direction.
And I am let's generate a new migration sota generate fiz add not no to reservation ID for restrictions.
That's close enough.
So it creates the the migration's for me and I will go down to my migration's right here and find that
new migration and find the up.
And what I want to do in this case is change a column so I can just say change column which is right
in the FBI's documentation or the sort of documentation.
But it's very simple syntax change column.
The table I want to change it on is room restrictions and the column I want to change is reservation
ID and it's still going to be an entry.
So I'll put integer.
And this time I want to put in No.
True, I think that will do it, so let's open up a terminal window and find out.
So to my right and there it is.
So now we should have a not now.
So we'll go back to Dover and hopefully it'll refresh this.
Sometimes I have to take two or three tries.
Yes, there it is.
Now, it is not null.
That means now it is not not null, which means I'm allowed to enter a null in there.
So when I enter a reservation or enter a restriction for a room that happens to be an honor block and
not a reservation, I can just not specify reservation ID and by default it will be no.
And that's fine.
So that solves that problem.
So now I have both restriction types that I need.
Now I have the ability to enter a restriction, interim restrictions without a reservation ID.
So now let's get back to our actual query.
So I'm going to hide this window and open up postcode just because it has an easier to read signal window.
So I'll open my bookings database and I'll open an empty query window.
Now, what we're going to do this time is very similar to what we did last time.
We're where we were looking for availability, for a given room, for a given date range.
But this time I want to get more than just a true or a false or the number of rows that exist in the
room restrictions table.
This time I want to get the room ID because I'm going to need that and the room name, but only for
available dates.
So I know I need the remedy in the room name and those exist in the room table so I can type select
and I'll put this on another line and I'm going to use an alias, Arnaut ID, our room name.
From the table rooms with the alias are that links it up here just to make it more readable and I need
to wear clothes.
And here's the part where I can actually get to where I can determine whether or not the rooms are available.
And it's very similar to what we did last time.
But this time we're going to use a sub query, which I've not seen before.
But it's not that difficult where our ID, the ID from the rooms table is not in and the syntax is just
not in.
And then you have a sub query and the sub query is just another query.
So what I'm going to do is almost identical to what I did in the last query we wrote.
I'm going to say.
Select room I.D. from room.
Restrictions.
And I'll call it per hour, just so it's readable and I can put an hour here or reminds something of
my alias where my start date and I'll choose 20, 21, zero to 19, because I know that's going to be
available is less than our end date, exactly the same as last time around end date.
And my end date, 20, 21, zero to 21 is greater than our start date.
And then I have to close my parentheses.
So if you think about what's happening here is I'm saying get me all of the IDs and room names from
the rooms tables where the ID that's in the room table is not in whatever this query returns.
And this is looking for room remedy in the room restrictions table using the same syntax we did last
time to determine whether or not something is available.
So when I run this, I get both rooms are available.
But if I look at my room restrictions table to find out what exactly exists as a booking, I have a
reservation from February the 1st of 2021 to February the 4th of 2021.
So if I go back to my school query and change this to say, 2021, 02, 02 to 21, 2001, 02, 03,
I should only get one available room.
And I do.
So this query works.
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
the function to make this search work, because this looks like it's going to do exactly what I want.
And close this and close this and find our Postgres and create a new function funk, and this again
will have the receiver of Postgrads DB Repo and this one I'll call search availability availability
for all rooms.
And it's going to take a start and an end, which are of time, dot time.
And this time I don't need the remedy because I'm searching all remedies.
And what is going to return.
Well, what is it going to return?
I'm going to get the room name and the room ID.
I could return to ense and potentially an error, but it seems to me that it's probably more useful
to return a slice of models, court room and an error, if any.
All right.
So let's first of all, get our query in here.
Query is assign the value of and I'll just put it in back ticks and paste in the query that I have,
which I'll fix up in a moment.
Let's go get our context so I don't have to retype this thing pasted in here.
And let's clean up our query a little bit just so it's more readable.
And I'll just move that over there.
Now we have a query and I'll put this on its online.
And they're now everything will fit nicely.
OK, so there's our query now what do we want to do?
We want to do exactly this sort of thing we did last time, except we're not going to be querying a
row context.
We'll be querying the context because we're potentially getting more than one row, which means I'm
going to get a result that I don't care about, an error that I do.
And that's going to be equal to IMDB dot query context.
Pass the context, pass it the query and pass it any parameters we're going to use and we're going to
use two, so we'll pass it start and end and now will replace these with our placeholder start.
And and OK, then we check for error, if error is not equal to nil.
We're going to return.
We'll return a variable I haven't declared yet, which I'll call rooms and our error.
And up here, let's just declare that variable var rooms, which will be a slice of models, dot room.
OK, so once we've done this, once we have our hour, that shouldn't be ignored.
That should be Rose.
What is Queretaro context return.
It returns rose.
So I actually want the rose here.
Not don't want to ignore the result.
I need something to look at.
So for Rose Dot next, what am I going to do.
I am going to say, just like we did up here anywhere.
We looked for a rose here.
We had some actually we didn't do in this one, but we did it in this one.
Connect to p.g.
We can look exactly how we did it in get all rose.
We say four rows next and then we scan for our rows so we can scan for that.
Let's go back to here and say.
Rose dot scan and I want to scan it into a particular destination.
So what am I going to scan this into?
Well, I'm only getting two things.
I'm getting my ID and my real name so I can say I got it.
First of all, declare a variable and my variable will be var room, which is a model model and start
room, not reservation room.
And I was looking into it, so I'll scan it into you.
Just put it over here rheumy eyed.
Because I'm selecting a ID and I'm going to scan into room, not name Romney.
Now I have this variable room and I need to put it into my slice, which is of named rooms, so I can
just say rooms equals append rooms, rooms, and then I'm going to append the value room.
So I now have this slice, so I need to do some returns, return rooms and no, but I want to check
for errors just like we did over here.
So after we did our our our scan, we actually put that into a variable error.
So let's go back into this and say error is assigned the value of this.
And if error is not equal to nil, what I want to do, I want to return.
I'll return the variable rooms.
We're going to ignore it.
And my error.
And again, just as we did over here, once we did our actual check for our actual scan in the fallout
for the rose.
We also check to make sure there was an air one last time.
So here we can do that right after the for loop.
And we don't want to log here.
We want to return our air.
I don't do the same thing, return rooms and earth.
So now I have this function.
Let's just format it to make sure it all looks good.
Let's give it a comment.
Search availability for all rooms returns a slice of available rooms, if any, for given date range
there.
Now, I need to put this in my repository, so let's copy this whole thing.
Go back to our repository and add this.
Let's make sure everything compiles so I'll clear the screen run.
Everything piles perfect, so now we're in a situation where we can actually begin to hook up our search
availability screen to the screen where we can actually make a reservation.
So we'll do that in the next couple of lectures.