So the next thing we have to do is to write some code that allows us to query the database and see if
a room we want to stay in is available for a certain date range.
And right now, I only have one entry in my room restrictions table.
And that is an entry that starts on February the 1st, 2021 and ends February the 4th, 2021.
So you can think about it.
Those are the only days that I currently have booked in my reservation calendar.
So let's start a query.
Let's see how we can figure out what we want to do.
So what I want to do, and this is how you do comments and sequel is two dashes.
I have existing reservation from 2021 02 01 to 2020, one zero two zero four.
I think that's what it is.
Let's make sure.
Restrictions 020 one to 020 for Yes, that's correct.
So let's write a query and what I want this query to do is I want to ask the database how many rows
for restrictions in the room restrictions table do you have where my dates overlap in any way, shape
or form your dates and if I get zero that I know the room's available for those dates and if I get one
or more that I know there's a reservation that blocks that date range.
And if I have a really long date range, say I want to stay for three weeks, I might have five reservations
that overlap, but I don't care if the number is not zero.
I know my requested dates are not available.
So how are we going to write that?
Let's write a select statement because clearly we're selecting information from the database and I want
to select count.
I'd give me the number of rows.
That's all this building function does is count the number of occurrences of that field.
And I want to select it from and I'm formatting this.
So it's quite readable room restrictions.
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
hardcoded the dates.
Let's say I want to stay right now on dates that are exactly the same as the date for the one existing
reservation I have.
So I'm going to start by putting in single quotes twenty twenty one zero two zero one, which is my
start date.
If that's less than the end date and my end date, 2021 zero two zero four is greater than the start
date.
So that's my query.
So I expect this to return one row because my start date is less than the end date of the reservation.
Remember, the end date is this date and this date, 02 01 is less than the end date 02 04 and my start
date 02 04 is greater than the start date, which is zero two zero one.
So this should return one row.
And it does.
There's one right down there.
I'm going to move this up so we can see a little bit.
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
the same as the existing reservation.
So let me copy this whole thing and paste it down below.
So I'll put a semicolon in that and I'll paste this one here.
But a semicolon after that.
To my second case, I want to say my start date is before the start date of the restriction, but the
end is the same.
So search date, I'll say start date is same.
As existing reservation, no, before existing reservation.
And Date is saying, I'm just trying to see if this covers all the cases, so my start date is going
to be before the existing reservation, so I'll make it zero one thirty one because that's before the
start date of the existing reservation.
But my end date, my end date, 02 04 is exactly the same as the end date of the reservation.
And it is 21 02 04, 21 02 04 here 21 01 31 one is before 2001 02 01.
Great.
I want this to return one row and it does.
OK, let's try a third case.
So again, I'll copy this entire thing and I'll paste it below.
And now I want my end date to be after the end date of the restrictions and date is after existing reservation
and date and start date.
Is the same.
So let's change my start date back to 02 01 and let's make my end date of seven, which is after the
end date of the reservation.
Let's run that statement and we get one exactly as it should be.
Let's try another case.
We want to cover all the possible cases here.
Otherwise we're going to have double bookings and two people will be showing up on the same day for
the same room.
And that's not good.
So this time, let's put the dates.
Search dates, the ones we're searching on, are outside of all existing reservations, but cover the
reservation.
In other words, my start date will be before the existing start date.
So 01 01, that's before.
And my end date will just make it 03, 07.
So here we have a start date.
That's before an existing reservation and an end date.
That's after an existing reservation, which technically means that should not be available.
This should return one.
Execute the statement and it does.
OK, let's try the next category or the next case.
So so far, we have dates that are exactly the same and that works, we have a start date before the
existing reservation, but the end date is on the date of the existing reservation that works.
We have our end date of our search after the existing reservation end date.
But the start date is the same that works.
Now we have search dates entirely outside of all existing reservations, but cover an existing reservation.
There's one inside my start date and end date that works.
So what's next?
Let's say dates are outside of the existing reservation entirely.
So this one say certain dates are outside.
Of all existing reservations, so outside, so let's go to zero seven zero one two zero seven zero seven,
and I know there's no reservations.
This should return zero and it does.
So far, so good.
So what's the last case?
I have to check?
Let's copy this and paste it in here.
Paste search dates are inside of existing reservation, so here's a situation where someone searching
for dates where their start date is after the existing start date of an existing reservation, and the
end date is before the existing end date of an existing reservation.
So here's one where my requested dates fall entirely inside an existing restriction.
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
and my end date is 02 03.
And this should return one, and it does so it looks like this query will actually do the job for us.
So I'm going to copy this query and we'll go and create the database method that can do the search for
us.
We'll just start with a really simple version that ignores remedies.
So let's go back to our database or to our Idy and find our Postgres and we'll create a new function.
And I will call this Thunk with the receiver of Postgres DB Repo search availability
by dates.
And that will take two parameters start and end, which will be of type time, short time, and it will
return a anent and potentially an error into an error or.
So let's just paste that query in here right now so we don't lose it.
Query is assigned the value of and I'll just paste and we'll fix this in a minute.
But I need my contact stuff and I hate typing this out, so I'm going to copy and paste it.
OK, and let's format this a little better.
So because I'm using that tactics, I can format this however I want.
So I will now move this over.
So it's nice to read and change this to a placeholder dollar sign one and that will be my start date
and change this to a placeholder and that will be my end it.
And down here I will say ver Nimroz, which will be anent and I will say return Nimroz and nil because
there's no area we haven't generated one.
So here's my query now.
I need to execute this query.
So let's execute this query and we can do it right here.
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
care about.
And I'm going to call M DB query context.
There it is, X plus my query, plus my placeholders start, and so now I check for my error.
If error is not equal to nil, then what do I do?
I say, actually, I can make this a query, real context, because I'm only getting one number back.
Let me change that query row context.
And this will return a role.
And there's no air check yet.
So my query, real context, if you recall, we can actually go look at that because I think I have
it open here.
I do connect to query row his works the same way as query row context, so query row.
There it is.
I do my get my row and then I scan my row, so and that generates an error.
So that's all I have to do.
So I can go back to my other project here and say query real context works exactly the same way.
Error is assigned the value of Rodon scan and I'm only going to scan into the one variable I have that
I have to move in a minute called Nimroz and I'll move this up here.
So the declaration comes before the variable, otherwise I can't use it.
So if error is not equal to nil return, I don't care what the INT is because we're not going to pay
attention to it.
I just care about the error.
So I'll return that otherwise return and I can already have it there.
Return no and no.
So let's look at this.
I'm going to do my query.
I'm going to pass it my start date and end date, which are both of the format time, short time.
And then I do my query, give me the number of rows from Rome restrictions where my start date is less
than your end date or so and my end date is greater than your start date.
I do the query.
I scan the value into Nimroz.
I check for an error just to be sure, and then I return the number of rows.
If this number is zero, then there is availability.
If this number is is greater than zero, then there is no availability.
So I could do it this way.
I can return an an error, but it might be simpler just to say bool.
So return false.
Otherwise, return true.
No, if air is not equal to no return, false and air otherwise, if Nimroz.
Equals exactly zero, then return true in nil.
Otherwise, return false and nil there.
Now, I don't even have to care about the number, I just care about the boolean value.
So I get it true when you have availability and I get a false when you don't have availability.
And that, I think is really, really simple.
So let's copy this and give it a comment.
Returns true if availability exists and false if no availability exists.
OK, I misspelled availability there.
Now this is a good starting point, but obviously it has some limitations.
For example, I'm actually searching all rooms and just saying, yes, there is availability, but I'm
not giving you any information about what rooms are available.
Is that helpful?
It's helpful if you're searching for a single room.
So let's add another parameter here and we'll call it room ID of type it and we'll modify our work laws
here to say where room ID equals Dorson one.
And.
The rest stays the same.
So this limits it to a single room, which means I have to change this dollar, sign one to a two and
this two to a three and prepend or put in here Brumidi.
And change my comment to say, returns true if Israel it exists for room ID and false if no availability
there.
That's a much useful use, much more useful method of searching for availability for a given room.
Now, we also want the ability to and I will go back and make sure this application is running.
It is.
And go to our room ID, so local or local server localhost eighty-eight.
We also want the ability to do this to search availability, make reservation now for all rooms.
And when I do that, I want to get more than just a true or a false.
What I want to get instead is look at this function, something very similar to this.
But I want to return the rooms that are available with the room name as well at a minimum.
So I want that information to be returned.
So I'm not going to be returning a bool in the next function I write, which will be in the next lecture,
I'll be returning all of the room names and IDs that matter that have availability on the dates that
we specify.
OK, that's not too difficult, but we'll do it in the next lecture right now.
Right now, I need to take this and add it to my repository or it's not going to be available at all
there.
So I save that and we should be good to go.
So in the next lecture, we'll write our second query, our second database function to search for availability
for all rooms, and then we will hook them up to her handlers and make our search function.
Finally.