So, did you succeed?
Let's work on this together.
And for this, back in blog.js,
of course, we wanna work on this get /posts route
which we already have.
But at the moment, we're not fetching any data there,
and that's what we wanna change now.
So in here, in this posts route,
the plan is to fetch a list of all the blog posts.
Therefore, we can again use db.query
and run a new SQL query against our database.
And here I wanna SELECT all the data FROM posts in the end,
all the blog posts.
But now we have to be careful.
If you do use the post-item EJS,
you will note that I also plan on outputting
some information about the author here.
And, of course, the author data is stored
in a separate table.
We only store the author id in the blog post table.
So, therefore, we have to join our posts with our authors.
Maybe you already did that in your solution.
If you did, that's amazing.
If you didn't, that was a bit more complex,
so that's absolutely fine, but we will do it here now.
I don't just wanna select all the blog posts like this.
Instead, I do wanna join them with the author data.
So, therefore, I'll also add a INNER JOIN statement,
and then INNER JOIN my post with the authors table
ON posts.author_id
being equal to authors.id.
That's the joining logic here.
And with that, we'll fetch all that posts data.
Now, what you can do but don't have to do
is you can also fine-tune the columns that will be fetched.
I wanna fetch all the columns from posts,
and we learned in the previous section
that we can do this with posts.*,
but then from the author table,
I'm only interested in the author name.
So, therefore, I'll only fetch authors.name here
and actually also give it an alias
with the AS keyword I showed you in the last section
where I'll assign author_name as an alias to the author name
just to make it a bit easier to differentiate
from the post title.
It's not required,
but I'll do it here as extra practice and exercise.
Now, therefore, this is a bit
of a longer query statement here.
And whilst this is technically absolutely fine,
to improve readability,
I'll actually outsource it into a separate constant,
a constant which I'll name query
and which I'll create by using backticks.
Now, you might recall this from early in the course.
These are not single quotes, these are backticks,
which is a different character on the keyboard.
And with backticks,
we can also create strings in JavaScript
but strings into which we, a, can inject dynamic values,
a feature we don't need here,
and b, a feature we do need here,
we can easily split them across multiple lines.
And, therefore, for readability's sake,
I'll grab my query here,
move that into this backtick-driven string,
and then just split it into multiple lines
to improve readability.
This is not a must-do, but it is something you can do
to make that easier to read.
And then I'll pass this query constant
and the value stored in it to the query method.
So it will have the same effect as before,
but it's a bit more readable.
And with that, we should fetch all the posts
and all the author data for the posts.
Now, of course, this takes a while,
and therefore let's add async in front of the function
and await in front of this line
to await this asynchronous operation
and only continue with the next line
once this longer-taking task is done.
Now, query, as you learned, does return
an array with two items
where the first item are the results of that query
and the second item holds some metadata.
And therefore, here, again, I'm interested in the results,
and I will again use array destructuring
to get my list of posts, and I'll name it posts here.
You can name it however you want.
And then to the template, we can pass extra data
and pass a posts key into the template,
and the value should be the data stored in this posts array.
So, again, this posts here refers to this posts array
which we create with destructuring.
This posts here simply is a key
that will be available in the template.
With that, we should be fetching that data,
and we should be handing it off to the template.
So let's now use it there, and hence we go to posts-list,
and in here we now got some work to do.
We wanna loop through all the posts
and make sure we display some posts data.
I actually also wanna handle the case
that we maybe don't have any post yet,
and I wanna show a fallback message in that case.
So I'll start with that and simply add a EJS tag here
where I check if not posts,
so if it's undefined, if it's falsy,
or if posts.length is equal to zero,
so we do have an array of posts, but it's empty,
in these situations, I wanna show a little error message.
Also don't forget the curly braces.
So in here, if we don't have any posts,
I'll simply add a paragraph where I say No posts found,
maybe start creating some?
Like this.
And then in the next line, I'll add an anchor tag
where I'll say Create a new post,
and that should lead to /new-post
because that's our new post route,
the route that loads this form.
And I'll add a class of btn
to give this anchor tag appropriate styling.
Now, if you did not do that in your solution,
that's absolutely fine
because that's just a little bonus here.
Now, however, let's work on the main task.
I'll add a else block here,
so I wanna handle the case that we do have posts,
and then also close those curly braces.
And here in this else block,
the goal now is to output a list of all the blog posts.
Now, I will output it as ordered list.
You could also argue that it's unordered,
but it does have some inherit order,
so I'll put it as ordered list.
And I'll give it an id of posts-list
for appropriate styling.
You find the id for that in the CSS files.
If you didn't add it, that's, of course, also fine
because it's not about the styling
but about fetching the data and outputting the data.
So in here, I then wanna output a bunch of list items.
And, of course, all those list items
should be generated dynamically
because we fetched that data from a database.
So, again, we need EJS tag here.
We can create a for loop
where we loop through all the posts with a for-of loop
just like that, and also close those curly braces.
And then inside of that for loop,
we can create list items.
And inside of every list item,
I will then include my post item include file
with include and then includes/post-item like that.
So that will include this post-item.ejs file.
Now, in this file, we also now need to fill
all those placeholders, all those empty spaces,
with concrete data about that post.
And for that, first of all,
I wanna forward the post which I have here in the loop
to this included file.
For that, we can pass a second parameter value
to this include function which we're calling here
and then define a key-value pair
which will be exposed to that included file.
And here I wanna expose a post key,
and the data stored in it
or the data made available through it
should be the post from this loop.
So here, this post here is the same as this post,
so that's how these are connected.
And this post here is just a key that will be available
inside of this post-item include template here.
With that, the post data will be exposed
to the post-item included file here,
and therefore here we can now fill those voids.
And, for example, I'll put the post title
in this h2 element.
For this, I'll replace the three dots with an EJS tag
where we output post.title.
Then here I wanna output the author name.
So for that, I'll again use EJS and output post.author_name,
author_name because I chose this as an alias here
when I fetched all that post data.
If you did not choose an alias, it's just .name.
Or if you chose a different alias, you have to use that.
But I chose author_name as an alias in the result set,
so it's .author_name here.
Then here I wanna have the summary text,
so I'll also output that with help of EJS.
post.summary is the right way of accessing it.
And now with that, we're outputting all that data.
Now, we got these buttons and links which won't work yet,
but we will make them work a little bit later.
For the moment, this is actually all I wanted to achieve,
and therefore now let's save everything, all the files,
go back and reload this main/posts page.
And if everything worked, you should now see your post here.
The buttons won't work, but you should see your post.
And if we create a second post:
MySQL + Node.js is amazing.
A truly awesome combination
of technologies.
You can easily send
SQL commands and queries
to your MySQL database when working with Node + MySQL.
It's easy
and allows you to build real websites!
If you add that, maybe with a different author,
and you click Add Post, you see that post here as well.
So this is all working, fetching the data is working,
and hence we now made another major step forward.
As a next step,
it would now be nice if we could click View Post
and we would then be taken to the Detail page
where we can view more post details,
so where those blanks are filled,
before we then move on to deleting and editing posts.
Now, if you feel like it,
definitely again take this as a challenge
and try as many of these things on your own
before continuing with the next lectures.
Try adding a route for handling a single post,
maybe with some dynamic route parameters,
fetch the data for that single post
with the features you learned about thus far,
display some single post data here,
and maybe even implement deleting and updating.
These are all exercises you can take and go through.
You can also pause later throughout the next lectures
if you wanna follow along
for some parts but not all of them.
But definitely at least think about trying
some of those things on your own
since it will be a great practice
of the concepts you learned.
With that, in the next lecture,
we are going to work on this post-detail page here.