---------------------------------------------- In this video, you’ll learn how to fetch all employers’ job posts from the database and show them inside the Applicant Dashboard (Find Jobs page) in a Next.js Job Portal App.
We will build a real production-style DB query using Drizzle ORM, apply filters/conditions like:
show only active jobs
hide deleted jobs
exclude expired jobs
return the latest jobs first
We’ll also use INNER JOIN to combine data from multiple tables like jobs, employers, and users (company logo + company name), then we’ll loop the jobs and render them in a simple UI layout.
This is exactly how real job portals fetch job listings. ------------------------------------------------------------------------------------
⌛TIMELINE⏳ 0:00 Fetching all employer job posts (Applicant Dashboard) 1:30 Support my channel ❤️ 1:50 Jobs page folder structure (Next.js App Router) 3:20 UI preview (Jobs Card Layout) 4:00 Creating getAllJobs() DB Query 4:20 What our function should return (Job Card Data) 7:20 Understanding INNER JOIN in Drizzle ORM 10:00 Adding conditions (deletedAt + expiresAt filters) 11:10 What our function returns (type-safe data) 11:30 Fetch data + loop through jobs in UI 13:20 Next video update / what’s coming next
AI Generated·May contain errors
Hello what's up everyone
Welcome back everyone to the nextgest and MySQL presented job portal application
Today marks our 68th day
And today we're going to take
a look at how our applicant'
s dashboard works and how we can access it whenever needed
Click on the Jobs tab to view all
job postings from
various employers
Here's where we're showing them right here
Where guys this video
Is extremely important because
Employers were posting
jobs and whatever
they posted we simply
showed them that
To see it an employer could only look after their own
And only their own
But what will happen here now
The applicant has the power to
view all employers who log into
our job portal application and
the job posts they have made
We'll show you everything here soon
Don't worry we're adding all that here
filters search everything
But the first step is that I
need all the job postings here
And you can clearly see
A vacancy for a full-stack developer
was posted by Codify Private Limited
and a separate vacancy was also
announced for a next-gen developer
It was posted by this company I' ll tell you about
this part later
How to access the detail page and all that stuff
But today in this video understanding this is extremely important
Different different
How we can display
the applicant 's
field values together
here is a very important video
and what we have here overall
Our backend query is for today's date we'll address
it right away—let's start
the video without any delay
But before that you haven't checked out the previous video yet
Please this will be extremely important
And if you haven't subscribed to the channel yet
please do so because your subscription
means a lot to me and will greatly motivate me to keep sharing everything completely free
I can keep uploading such videos
Please make sure to like them as well
That would mean a lot to me
Alright let's take a look
I'm bringing Siddhar here
And this is our main
We have this job's page
And by the way
I know you haven't set up the job page yet
Don't worry
Just check with Gohar
So you already know of course tha
That is we have this app component
Not the app component app folder over here
Inside the app
We've created the applicant's dashboard
Where the dashboard is followed by a forward slash
Whatever it is we've got it covered
Philal we've already checked the page
And we've looked at
layout tsx We've reviewed it
But up until now we hadn't seen what would happen
when I click on the jobs page
and what the folder structure would look like
And it's simple
As we saw in the employer's case
it's the same for this applicant
—if you check it'll be correct
Inside the dashboard I've already
What did I create by working on a job guys
Here you can see
I made a folder
Ignore Philal please
I've written something here but for
Philal just ignore this part since
we haven't yet
dealt with dynamic data— we'll check that tomorrow
But understand this:
inside Jobs I've created a
file and it's yours So
basically whenever anyone
When the jobs page appears
it will display this page
This is your folder structure
To please Aap I'll note this down carefully Will
take it tomorrow
It seems to us that some updates are needed
We'll make the necessary modifications
And I sincerely hope tha
The new updated method I explained to you earlier for our sidebar
is exactly what you should use
Employers must have changed their stance in this case as well
That's why I'm still inside this page tsx file Philal
You can see for yourself right here
What we need to do here is this: what you see on this
page is what you should focus on —
this part is what you're seeing
We've already seen that part so what do we
need to see today Today
we need to see this
The company's post that it
has shared how can
I show it all at once right here So this sidebar what is it
The content on the right
side that's shown to you is
what I need — naturally it
will come from the database
I need to trigger a server action so I have to make an API call
That's why
If you look closely it's normal
There's a function here that if you look at it
from Gauhar's perspective
is called "Get all jobs "
I've kept it very simple here
I'm calling this
Just look directly at Gauhar
I'm the one calling and that's that
This is a function that contains such a thing
I'm typing my query
so that I can retrieve
whatever data is saved in my database
You'll have to figure out the method I used to write it
You'll need to understand it a bit
If you look closely here it says
Posted by which is the company's name
And there are many things I’ll show you in detail
And there if I were to take you
to our table
So this is your employer's table
Employer's name represents your organization
Here is their description
Their banner image is
Their name their organization team size—
Year of establishment—
So much information right here—
And if I were to go through the jobs there—
To jobs you'll find something suitable
By the way employers will offer you another one
Yes indeed how do you see it
Codify Private Limited
recently I created this
to show you how multiple employer jobs work
So you're showing us something here
So tell me what exactly are you trying to point out here
To different different
Do employers have any jobs available with me
How many positions have they posted in total
So many positions have been posted
You can see them right here
And by the way you can see two bottles here
There are so many bottles visible here
And the reason behind this is
That is because
There are some that are already
expired so we'll also need
to check in the query for
jobs that haven't been deleted
And jobs that
haven't expired
yet—what am I saying The ones
that haven't been deleted—and
jobs that haven't expired yet
I want to show you those jobs right here
So if I show you right here
We have one field called
Expires at obviously
If you look closely here
Look carefully
So yeah the 22nd date
This is the 17th date
This is the 21st date
And this is something like that
I need to know about the future
The position is still open
I can apply for it
So that's the reason
Because those three have already expired
And yes only non-expired posts should be shown
to the applicant Only the posts that haven't expired
should appear for the applicant
Got it
Keep this in mind:
anything that hasn 't
been deleted yet—just remember that
And here's this deleted advertisement
Expires at deleted ad
Just keep this in mind for a moment
And nothing else of that sort exists
Just understand this:
Data will likely be needed from the Jobs table as well as
from the Employers table and
possibly from the Users table too
After all anything can happen here
If I need to retrieve exact
data from multiple tables
And now for that
What do we use SQL for
What you can do
with multiple queries
But there's a better solution
And that is to use joins
Today we’ll be working on joins
So what kind of work does a join perform
I'll let you know what we'll see from Gohar You'll have a full idea of what we need next
Hey guys grab this normal query —
read-only query you've got it
We now understand what this function is doing
Currently it's acting as a server operation
It's a simple query
From where I need to
read the data you hold
it so tightly that we must come here first
We'll use the drizzle URL here
Let's write the drizzle syntax right here
Db select
Which fields do I need
To show jobs we need an ID
the title your description
the maximum salary and
the salary currency
And one more thing to keep
in mind: whatever
I'm telling you by name of these fields whatever comes after that
Whatever part you're seeing right now where are we getting its value from From
the ID field on the jobs table
The field in the jobs table with the maximum salary
You understand right in that way
So this is the name of your table
Salary period locatio
Job type work type created at
And that's it
Yeah it's a field like that
That's why we need to use this here
I mean if there was just one
table then this would basically
become a child's query wouldn't it Selecting from this single table
The job's done nothing left to do That's right but the
company's name is here which
comes from my employers' table
I mean
it's being displayed—within
it the field's name is also shown
as name—second your
company logo —and that's your users avatar URL
Where on earth is this coming from anyway
If you look at it it's solely your responsibility therefore
from now on we're
performing an inner join because I need
the common elements present in both tables
We need a inner join because we have a total
of 3 tables That's what will
be required along with jobs
We'll compare this Here's
how: Inner join this is your
Employers To jobs I'm now
connecting with the employer
That's right brother— the
employer ID in the jobs
table must match the ID in
the employers table right
This is your inner join Now I'll explain
the employer ID in the Jobs table
in the Jobs table
This employer ID here Sorry over here
We have this employer ID One is one and the other is four
Simply put both are in our
possession These should be equal
Then four Okay
And then the second one obviously users
Are also here So again
With users So employers
Whose ID it is and the user's
That's it It should be the
same Employer's ID followed
by the user's ID So we have
the user's ID right here
One two three four No tension at all
And that's the employer's ID That's why
Employers dot id That's what we're discussing One
And then four Those should also be identical Theke
The inner join has been successfully executed
These tables will be delivered to me However some
You're supposed to keep the condition
right That's what's being said
Some condition is needed Obviously we have a condition and that is
I've already explained the main
point to you Notice
that when we use
an AND operator both conditions
must be true—both need to be true So
It's being indicated here in the null that the job
shouldn't be deleted right And
then the other point is tha
But please just understand
this: the job should not
have expired That's precisely the point I'm trying to make
The job should not have expired the time
it has should be greater than today's
These two lines The mallab should be in this wear
condition The job should not be deleted
And it should not have expired
Everything will be mine
Then what should we do with him Order-wise
Want to show From
Latest to whatever
They're being arranged
in that way That's why the
job created will be displayed in descending order
Order by jobs created in
descending order We'll return
all the data you need
That's it Just do this once
That function I called—it returned jobs
data to me and that's
exactly what this is
What I'll get in the select basically
If everything goes right
Isn't that right That's all there is to it
That's right Whatever I've written
on ExpiredSide today is exactly
what today's simple goal is That's what we've been aiming for
Set the time to 00 right
Controller save it And
these remaining items will
obviously cause variation
We're importing
these tables from our
schema and the database is giving me
No need for a break Obviously our db connection was
what it was Here I called it
So this function was invoked
Whenever I call you know
Give me this data Basically
the one named Jobs my constant
The variable is here That's the data I'll get there
And that's the data in question
ID Title Description—those are all the details right
I'm not stressed about anything anymore Whether it's
employers users or jobs—none
of it bothers me I'm just simply
Jobs Yeah the Jobs I'm referring
to — Jobs CreatedAd — Jobs
CompanyName Company — I'll write
the logo I'll get the data soon
It happened once So I'm logging
to the console And obviously it's
an array of objects Multiple objects
will be inside it So that's it
The reason I looped
through Jobs is thanks to
MapMethod—this is your Job ID you know Simple
And Job Title was set as main
Posted by Job
Whatever company name there was And this
It's something I'll tell you tomorrow There's no need for it
today The dynamic value we'referring to is what we're discussing
You're saying you're about to leave but if you really do
If you were to walk away from here
You see them in that way
Got it So basically everything is mine
Let me tell you something Let's do one thing This
empty condition here—why don 't
we just adjust it a bit
Just once more That is
even if it's deleted it'll
still work Even if it's
expired Because I've already
Sorry for the confusion Take a
look at this—how many of these do
you have Here are my current jobs
Total: We have around 5 Basically
1 2 3 4 5
— How much
is that 2 — 3 5 See
You'll realize just how crucial this condition is
because we can't show the applicant the job posting
that has been deleted
It had already
expired which is why
we had to include this condition
It's essential I hope you now
understand its value It was just a
regular straightforward inner join—nothing particularly special
And you've just received this in this manner Now in tomorrow's video I'll tell
you how you can achieve this
You can make this really awesome This card
here—how can we make its
layout more beautiful
I'll tell you about it in tomorrow's video These are just quick notes
But yes This video clip isn't today
And I’d like to give you a task:
how could you make this awesome
Since I already have the data I just
need to work on integrating it with the UI—
You know make it a bit better
And I hope you can see it here So in a way today you can see it like this
I mean you shouldn't make that Just forget it guys
This is it Today's not a video clip I hope
You'll really enjoy this video And you
Applicant we're watching Employer you've
already seen So it must have seemed good to you
A lot is coming our way but we'll tackle it step by step
Just take it slow one step
at a time Much better that way
It's feeling good Because happiness is coming through Even after doing so much
it still feels great Whatever it is The rest
Don't forget to check out the
videos Our next goal is 800 000
subscribers If you haven't subscribed yet please do so now
See you in the next video Until then guys Thank you so much
Take care