Filtering Lessons by Publication Date

In This Lesson

We'll add the ability to search lessons by their publish date and time. We'll utilize before and after properties to allow for filtering that can look forward, backward, or within a specific date range on our lesson's publish at column.

Created by: @tomgobich
Published: 1 year ago

Join the Discussion 0 comments

Be the first to comment!

Transcript

- So for our search lessons publish at filter, what we're gonna do is add in two different properties.
We're gonna have before as one and after as another. If both after and before are provided, what we're going to do is search for the lessons
with a publish at between the two. So after would be searching from the past to the present and before would be searching from the present to the past,
kind of giving us that between range there. If however, just one of the two is provided, so if we just provide an after, we'll just search from the past to the present.
If just before is provided, then we'll search from the present to the past. Whatever that before date is to the past
or whatever that after date is to the present. First though, we need to jump into our lesson validator and add it into our payload. This is the one and only spot
that we are going to be filtering based off of a date in this application. So rather than creating a date filter as we have with our string and numbers,
we're just gonna go ahead and do this in line. So we'll have our publish at, that will be a type vine object. And within this object, we're gonna have our before property, which would be of type vine date.
And we're gonna want that to be optional. And then we're also going to have after, which too will be a vine date set to optional. In addition to that, we don't want to force a publish at filtering,
so we'll make the entire object optional as well. We want to be able to pass in a UTC or ISO date to this
for both our before and after as it's unified format. So within the date function, what we wanna do is pass in a formats option
with UTC set to true. And that's going to allow us to pass in an ISO date string as one of its formats.
So we'll set format UTC true to do that for both our before and after there. So now that's all that we need for our publish at rule within our search lessons, we need to actually apply it
within our apply filters method. So if our filters object contains a publish at, first, we'll go ahead and eliminate the instance
where they might have provided both a before and after. So we'll check for our after. And if we have that, and we have our filters publish at before, then we're gonna wanna go ahead and check for lessons
that have a publish at between our after and before date range. So we can build off of our query where, and we can use the between method to specify that we're searching
against the publish at column. And we wanna check where that value is between our filters publish at after and our filters publish at before.
And I'm adding in a where not null here, but it's actually not needed. The fact that we already have a between check here is going to eliminate the null values automatically on our behalf anyway.
So this where not null is not needed. Now, if you want to, we can clean this up a little bit by extracting this into its own function. So we can do static apply publish at filter,
take in our query of type query, and then we can accept in our filters, but we don't necessarily need the entire filter set here.
We can just pick off of our filters type, the publish at to specify that this method just needs the publish at property from there.
Then we go and paste in what we had copied previously. And if we wanna clean things up a little bit further, we can go ahead and pluck out publish at from there, which may or may not have a value.
So to simplify that further, let's first check if we don't have publish at. And if we don't, then we don't need to do anything with it. So we can just return directly from this method.
Now that we're extracting publish at out of our filters, we can get rid of our filters dot on all four of its usages here, just cleaning things up a little bit further there.
Cool. So we have our between check there for if we have both an after and before passed into our publish at filter. We go ahead and add in an else if,
if just a publish at after was specified to then do query. And we can do where not null again, publish at,
and where publish at is greater than the publish at after value passed in to go from that publish at date provided
to the present for its filtering. Then we can round this out with one more else if, publish at and check and see if before is there. You can also expand the not check up here
to check and see whether or not we have either before or after and then return if not. But this works fine here as well. Query dot where not null,
where publish at is less than our publish at before property going from the before date provided to the past.
If you want to include the exact publish date provided for either before or after here, you can also do greater than or equal to
or less than or equal to for either of those as well. All right, last thing left to do is to apply this to our filter.
So apply publish at filter within our apply filters method, we'll pass the query in and the filters in there. Awesome. So let's go ahead and jump back in top scotch
and test this out. So we can do publish at, provide that as an object. And I can see right down here in the results that we have a publish at 2025, 728.
So if we search for after and set the date to 2025, 07, 27, we can go ahead and omit the time that will still match a UTC format.
So we should get back at least this one result here. So if we send this off, we do indeed get back one result and it is that exact result. Rather than scrolling through all of our lists,
let's just open up TablePlus here. This is a GUI of our database. I've got the lessons open. You can see that we only have this one lesson here with a publish at date provided.
So what I'm gonna do for our testing purposes is just come in here, add another date here. So 07, maybe 2605. I'm gonna keep the time here the same for this one,
negative 04. And then I'm gonna set this one to 2025, 07, 28. And maybe we do 12, 000 at 04.
Okay, so this is the time in a 24 hour format. And then the negative 04 there is the time zone offset. And that's defined within the database itself.
So I have those set. I'm gonna go ahead and commit them with this little up arrow icon there. Just doing this to give us some additional things to test with here within Hopscotch to make sure that everything's working.
We could also create some additional new lessons if need be as well there to resolve that. Okay, so now we should probably get back to,
since we've added an additional one published on the 28th. So if we send this off, there's two. We have back my third lesson now published at 1600 hours.
And you can see that this is converting our date time to the universal time zone rather than the negative 04 that I have to find inside of the database itself.
So if we switch this to 07, 28 with a time of 1200.000 plus 000
to specify the time zone that we're after as well. Send this off. We get back just one result
because only one is beyond noon at universal time. If we switch this to 0900, send this off.
We still only get back one because now it's equal to 0900. If we switch this to 08, 59, 59, send this off.
We now get back two, including now, in addition to our 1600 hours, the one at 0900 hours. If in addition to our after,
we add in a before to specify 2025, 07, 08 with a time zone of 1200 hours, 000 plus 000 there
for the time zone. And we send this off. We should get back, we get back zero. Oh, I did the eighth rather than the 28th. There we go.
So let's switch that to 28th. There we go. Now we get back our one, which is set to 0900 hours. And if we expand this a little bit to 1700 hours,
send this off, we get back both again. As an aside, if we want to prevent the instance that we just ran into where our before and after values that we're searching with are kind of flip-flopped.
So let's say that we have 2000 and then we have 2025 in a timeline going that way.
Our after is 2005 and our before is 2015.
Let's say instead we send a before as 2002. In that case, if we want an error to be thrown
from our validation because the before predates the after and or vice versa, if our after is say 2017 and is later than our before,
we can do that relatively easily with vine.js by specifying that we want our before value to be after the value within the field, publish at dot after,
and then we can do the exact opposite for the after. So we can say before the field publish at dot before.
And that will ensure that in order for our validation to succeed that the before and after are provided in the correct ordered timeline. All right, back to where we were.
And so our before and after is working, just providing an after is working. What about just the before? So let's get rid of our after, send this off. We get back all three because all three
are before this date time. If we switch that back to 12, we should just get back two because we have that one at 1600 hours. If we switch this to 800 hours, we should just get back to one,
which was I think what we set that on the 26th. So awesome, all of that is working perfectly fine. If we go ahead and omit the publish at altogether,
let's make sure that we're still getting back all four, which we sure are, which includes the one that would be null, which we are ordering that to be last
via our order property. So if we go ahead and search, there's the fourth one. There it is at last within our results.

Adding an API to an AdonisJS Web App

43 Lessons

5h 31m

Introduction

API Authentication

Getting Started with APIs

Organization Resources

Courses

Searching

Rate Limiting

Rate Limiting an Organization's HTTP Requests

13m 59s

Stop losing your best ideas!

Use our integrated note feature to capture and organize your key insights. Take a note at any timestamp and jump instantly back to that moment in the video with a single click.