1 00:00:02,220 --> 00:00:04,700 So, did you succeed? 2 00:00:04,700 --> 00:00:06,939 Let's work on this together. 3 00:00:06,939 --> 00:00:08,850 And for this, back in blog.js, 4 00:00:08,850 --> 00:00:12,140 of course, we wanna work on this get /posts route 5 00:00:12,140 --> 00:00:14,080 which we already have. 6 00:00:14,080 --> 00:00:16,329 But at the moment, we're not fetching any data there, 7 00:00:16,329 --> 00:00:18,223 and that's what we wanna change now. 8 00:00:19,380 --> 00:00:22,310 So in here, in this posts route, 9 00:00:22,310 --> 00:00:26,240 the plan is to fetch a list of all the blog posts. 10 00:00:26,240 --> 00:00:28,710 Therefore, we can again use db.query 11 00:00:28,710 --> 00:00:33,080 and run a new SQL query against our database. 12 00:00:33,080 --> 00:00:37,360 And here I wanna SELECT all the data FROM posts in the end, 13 00:00:37,360 --> 00:00:38,683 all the blog posts. 14 00:00:40,170 --> 00:00:42,560 But now we have to be careful. 15 00:00:42,560 --> 00:00:45,330 If you do use the post-item EJS, 16 00:00:45,330 --> 00:00:48,010 you will note that I also plan on outputting 17 00:00:48,010 --> 00:00:51,090 some information about the author here. 18 00:00:51,090 --> 00:00:54,160 And, of course, the author data is stored 19 00:00:54,160 --> 00:00:56,090 in a separate table. 20 00:00:56,090 --> 00:00:59,980 We only store the author id in the blog post table. 21 00:00:59,980 --> 00:01:04,980 So, therefore, we have to join our posts with our authors. 22 00:01:05,160 --> 00:01:08,070 Maybe you already did that in your solution. 23 00:01:08,070 --> 00:01:09,530 If you did, that's amazing. 24 00:01:09,530 --> 00:01:12,010 If you didn't, that was a bit more complex, 25 00:01:12,010 --> 00:01:15,960 so that's absolutely fine, but we will do it here now. 26 00:01:15,960 --> 00:01:19,350 I don't just wanna select all the blog posts like this. 27 00:01:19,350 --> 00:01:22,913 Instead, I do wanna join them with the author data. 28 00:01:24,410 --> 00:01:28,230 So, therefore, I'll also add a INNER JOIN statement, 29 00:01:28,230 --> 00:01:33,230 and then INNER JOIN my post with the authors table 30 00:01:34,130 --> 00:01:36,870 ON posts.author_id 31 00:01:38,330 --> 00:01:41,640 being equal to authors.id. 32 00:01:41,640 --> 00:01:43,623 That's the joining logic here. 33 00:01:44,610 --> 00:01:47,983 And with that, we'll fetch all that posts data. 34 00:01:49,404 --> 00:01:52,260 Now, what you can do but don't have to do 35 00:01:52,260 --> 00:01:56,570 is you can also fine-tune the columns that will be fetched. 36 00:01:56,570 --> 00:01:59,430 I wanna fetch all the columns from posts, 37 00:01:59,430 --> 00:02:01,370 and we learned in the previous section 38 00:02:01,370 --> 00:02:03,080 that we can do this with posts.*, 39 00:02:04,980 --> 00:02:06,940 but then from the author table, 40 00:02:06,940 --> 00:02:09,293 I'm only interested in the author name. 41 00:02:10,280 --> 00:02:13,550 So, therefore, I'll only fetch authors.name here 42 00:02:13,550 --> 00:02:15,800 and actually also give it an alias 43 00:02:15,800 --> 00:02:19,340 with the AS keyword I showed you in the last section 44 00:02:19,340 --> 00:02:24,340 where I'll assign author_name as an alias to the author name 45 00:02:25,330 --> 00:02:27,650 just to make it a bit easier to differentiate 46 00:02:27,650 --> 00:02:29,350 from the post title. 47 00:02:29,350 --> 00:02:30,710 It's not required, 48 00:02:30,710 --> 00:02:33,713 but I'll do it here as extra practice and exercise. 49 00:02:36,710 --> 00:02:37,700 Now, therefore, this is a bit 50 00:02:37,700 --> 00:02:40,090 of a longer query statement here. 51 00:02:40,090 --> 00:02:43,020 And whilst this is technically absolutely fine, 52 00:02:43,020 --> 00:02:45,140 to improve readability, 53 00:02:45,140 --> 00:02:49,060 I'll actually outsource it into a separate constant, 54 00:02:49,060 --> 00:02:51,090 a constant which I'll name query 55 00:02:51,090 --> 00:02:54,280 and which I'll create by using backticks. 56 00:02:54,280 --> 00:02:57,470 Now, you might recall this from early in the course. 57 00:02:57,470 --> 00:03:00,970 These are not single quotes, these are backticks, 58 00:03:00,970 --> 00:03:03,910 which is a different character on the keyboard. 59 00:03:03,910 --> 00:03:05,120 And with backticks, 60 00:03:05,120 --> 00:03:07,680 we can also create strings in JavaScript 61 00:03:07,680 --> 00:03:12,680 but strings into which we, a, can inject dynamic values, 62 00:03:13,420 --> 00:03:15,440 a feature we don't need here, 63 00:03:15,440 --> 00:03:18,210 and b, a feature we do need here, 64 00:03:18,210 --> 00:03:21,340 we can easily split them across multiple lines. 65 00:03:21,340 --> 00:03:24,130 And, therefore, for readability's sake, 66 00:03:24,130 --> 00:03:26,300 I'll grab my query here, 67 00:03:26,300 --> 00:03:29,200 move that into this backtick-driven string, 68 00:03:29,200 --> 00:03:32,360 and then just split it into multiple lines 69 00:03:32,360 --> 00:03:34,223 to improve readability. 70 00:03:35,550 --> 00:03:39,010 This is not a must-do, but it is something you can do 71 00:03:39,010 --> 00:03:40,853 to make that easier to read. 72 00:03:42,200 --> 00:03:44,520 And then I'll pass this query constant 73 00:03:44,520 --> 00:03:48,240 and the value stored in it to the query method. 74 00:03:48,240 --> 00:03:50,450 So it will have the same effect as before, 75 00:03:50,450 --> 00:03:51,973 but it's a bit more readable. 76 00:03:52,950 --> 00:03:55,350 And with that, we should fetch all the posts 77 00:03:55,350 --> 00:03:58,057 and all the author data for the posts. 78 00:03:58,057 --> 00:04:00,350 Now, of course, this takes a while, 79 00:04:00,350 --> 00:04:04,020 and therefore let's add async in front of the function 80 00:04:04,020 --> 00:04:06,600 and await in front of this line 81 00:04:06,600 --> 00:04:09,320 to await this asynchronous operation 82 00:04:09,320 --> 00:04:11,540 and only continue with the next line 83 00:04:11,540 --> 00:04:14,183 once this longer-taking task is done. 84 00:04:15,820 --> 00:04:18,940 Now, query, as you learned, does return 85 00:04:18,940 --> 00:04:21,380 an array with two items 86 00:04:21,380 --> 00:04:25,290 where the first item are the results of that query 87 00:04:25,290 --> 00:04:28,723 and the second item holds some metadata. 88 00:04:29,920 --> 00:04:33,260 And therefore, here, again, I'm interested in the results, 89 00:04:33,260 --> 00:04:36,550 and I will again use array destructuring 90 00:04:36,550 --> 00:04:41,240 to get my list of posts, and I'll name it posts here. 91 00:04:41,240 --> 00:04:42,983 You can name it however you want. 92 00:04:44,260 --> 00:04:48,520 And then to the template, we can pass extra data 93 00:04:48,520 --> 00:04:52,130 and pass a posts key into the template, 94 00:04:52,130 --> 00:04:55,863 and the value should be the data stored in this posts array. 95 00:04:56,960 --> 00:05:01,590 So, again, this posts here refers to this posts array 96 00:05:01,590 --> 00:05:04,790 which we create with destructuring. 97 00:05:04,790 --> 00:05:07,030 This posts here simply is a key 98 00:05:07,030 --> 00:05:09,243 that will be available in the template. 99 00:05:11,810 --> 00:05:14,060 With that, we should be fetching that data, 100 00:05:14,060 --> 00:05:16,553 and we should be handing it off to the template. 101 00:05:17,780 --> 00:05:22,670 So let's now use it there, and hence we go to posts-list, 102 00:05:22,670 --> 00:05:25,960 and in here we now got some work to do. 103 00:05:25,960 --> 00:05:28,450 We wanna loop through all the posts 104 00:05:28,450 --> 00:05:31,003 and make sure we display some posts data. 105 00:05:32,110 --> 00:05:34,330 I actually also wanna handle the case 106 00:05:34,330 --> 00:05:36,710 that we maybe don't have any post yet, 107 00:05:36,710 --> 00:05:39,800 and I wanna show a fallback message in that case. 108 00:05:39,800 --> 00:05:43,760 So I'll start with that and simply add a EJS tag here 109 00:05:43,760 --> 00:05:46,440 where I check if not posts, 110 00:05:46,440 --> 00:05:50,070 so if it's undefined, if it's falsy, 111 00:05:50,070 --> 00:05:54,720 or if posts.length is equal to zero, 112 00:05:54,720 --> 00:05:57,940 so we do have an array of posts, but it's empty, 113 00:05:57,940 --> 00:06:02,663 in these situations, I wanna show a little error message. 114 00:06:03,580 --> 00:06:06,053 Also don't forget the curly braces. 115 00:06:07,050 --> 00:06:09,340 So in here, if we don't have any posts, 116 00:06:09,340 --> 00:06:14,120 I'll simply add a paragraph where I say No posts found, 117 00:06:14,120 --> 00:06:16,853 maybe start creating some? 118 00:06:19,110 --> 00:06:20,620 Like this. 119 00:06:20,620 --> 00:06:23,910 And then in the next line, I'll add an anchor tag 120 00:06:23,910 --> 00:06:26,793 where I'll say Create a new post, 121 00:06:27,890 --> 00:06:30,850 and that should lead to /new-post 122 00:06:30,850 --> 00:06:33,570 because that's our new post route, 123 00:06:33,570 --> 00:06:35,713 the route that loads this form. 124 00:06:36,590 --> 00:06:38,210 And I'll add a class of btn 125 00:06:38,210 --> 00:06:42,260 to give this anchor tag appropriate styling. 126 00:06:42,260 --> 00:06:44,350 Now, if you did not do that in your solution, 127 00:06:44,350 --> 00:06:45,670 that's absolutely fine 128 00:06:45,670 --> 00:06:48,280 because that's just a little bonus here. 129 00:06:48,280 --> 00:06:51,550 Now, however, let's work on the main task. 130 00:06:51,550 --> 00:06:53,490 I'll add a else block here, 131 00:06:53,490 --> 00:06:57,290 so I wanna handle the case that we do have posts, 132 00:06:57,290 --> 00:07:01,050 and then also close those curly braces. 133 00:07:01,050 --> 00:07:02,710 And here in this else block, 134 00:07:02,710 --> 00:07:05,953 the goal now is to output a list of all the blog posts. 135 00:07:07,520 --> 00:07:10,070 Now, I will output it as ordered list. 136 00:07:10,070 --> 00:07:12,435 You could also argue that it's unordered, 137 00:07:12,435 --> 00:07:14,420 but it does have some inherit order, 138 00:07:14,420 --> 00:07:17,530 so I'll put it as ordered list. 139 00:07:17,530 --> 00:07:20,330 And I'll give it an id of posts-list 140 00:07:20,330 --> 00:07:22,840 for appropriate styling. 141 00:07:22,840 --> 00:07:26,170 You find the id for that in the CSS files. 142 00:07:26,170 --> 00:07:28,630 If you didn't add it, that's, of course, also fine 143 00:07:28,630 --> 00:07:30,450 because it's not about the styling 144 00:07:30,450 --> 00:07:33,223 but about fetching the data and outputting the data. 145 00:07:35,060 --> 00:07:38,650 So in here, I then wanna output a bunch of list items. 146 00:07:38,650 --> 00:07:40,860 And, of course, all those list items 147 00:07:40,860 --> 00:07:43,150 should be generated dynamically 148 00:07:43,150 --> 00:07:45,603 because we fetched that data from a database. 149 00:07:46,690 --> 00:07:49,240 So, again, we need EJS tag here. 150 00:07:49,240 --> 00:07:50,850 We can create a for loop 151 00:07:50,850 --> 00:07:55,110 where we loop through all the posts with a for-of loop 152 00:07:56,030 --> 00:08:00,730 just like that, and also close those curly braces. 153 00:08:00,730 --> 00:08:02,680 And then inside of that for loop, 154 00:08:02,680 --> 00:08:04,920 we can create list items. 155 00:08:04,920 --> 00:08:07,230 And inside of every list item, 156 00:08:07,230 --> 00:08:11,770 I will then include my post item include file 157 00:08:12,660 --> 00:08:17,660 with include and then includes/post-item like that. 158 00:08:20,850 --> 00:08:24,313 So that will include this post-item.ejs file. 159 00:08:25,440 --> 00:08:29,150 Now, in this file, we also now need to fill 160 00:08:29,150 --> 00:08:32,690 all those placeholders, all those empty spaces, 161 00:08:32,690 --> 00:08:36,159 with concrete data about that post. 162 00:08:36,159 --> 00:08:37,570 And for that, first of all, 163 00:08:37,570 --> 00:08:40,909 I wanna forward the post which I have here in the loop 164 00:08:40,909 --> 00:08:42,893 to this included file. 165 00:08:43,890 --> 00:08:46,530 For that, we can pass a second parameter value 166 00:08:46,530 --> 00:08:49,480 to this include function which we're calling here 167 00:08:49,480 --> 00:08:52,030 and then define a key-value pair 168 00:08:52,030 --> 00:08:55,570 which will be exposed to that included file. 169 00:08:55,570 --> 00:08:59,220 And here I wanna expose a post key, 170 00:08:59,220 --> 00:09:01,070 and the data stored in it 171 00:09:01,070 --> 00:09:03,280 or the data made available through it 172 00:09:03,280 --> 00:09:05,163 should be the post from this loop. 173 00:09:06,730 --> 00:09:11,730 So here, this post here is the same as this post, 174 00:09:11,950 --> 00:09:14,800 so that's how these are connected. 175 00:09:14,800 --> 00:09:18,470 And this post here is just a key that will be available 176 00:09:18,470 --> 00:09:21,173 inside of this post-item include template here. 177 00:09:22,610 --> 00:09:25,670 With that, the post data will be exposed 178 00:09:25,670 --> 00:09:28,940 to the post-item included file here, 179 00:09:28,940 --> 00:09:31,880 and therefore here we can now fill those voids. 180 00:09:31,880 --> 00:09:34,200 And, for example, I'll put the post title 181 00:09:34,200 --> 00:09:36,500 in this h2 element. 182 00:09:36,500 --> 00:09:40,670 For this, I'll replace the three dots with an EJS tag 183 00:09:40,670 --> 00:09:43,623 where we output post.title. 184 00:09:45,610 --> 00:09:50,200 Then here I wanna output the author name. 185 00:09:50,200 --> 00:09:55,073 So for that, I'll again use EJS and output post.author_name, 186 00:09:57,701 --> 00:10:02,701 author_name because I chose this as an alias here 187 00:10:03,360 --> 00:10:06,470 when I fetched all that post data. 188 00:10:06,470 --> 00:10:10,960 If you did not choose an alias, it's just .name. 189 00:10:10,960 --> 00:10:14,110 Or if you chose a different alias, you have to use that. 190 00:10:14,110 --> 00:10:17,760 But I chose author_name as an alias in the result set, 191 00:10:17,760 --> 00:10:19,823 so it's .author_name here. 192 00:10:21,510 --> 00:10:24,240 Then here I wanna have the summary text, 193 00:10:24,240 --> 00:10:27,640 so I'll also output that with help of EJS. 194 00:10:27,640 --> 00:10:31,643 post.summary is the right way of accessing it. 195 00:10:32,480 --> 00:10:35,033 And now with that, we're outputting all that data. 196 00:10:35,890 --> 00:10:39,230 Now, we got these buttons and links which won't work yet, 197 00:10:39,230 --> 00:10:42,440 but we will make them work a little bit later. 198 00:10:42,440 --> 00:10:45,500 For the moment, this is actually all I wanted to achieve, 199 00:10:45,500 --> 00:10:49,660 and therefore now let's save everything, all the files, 200 00:10:49,660 --> 00:10:54,080 go back and reload this main/posts page. 201 00:10:54,080 --> 00:10:58,630 And if everything worked, you should now see your post here. 202 00:10:58,630 --> 00:11:01,293 The buttons won't work, but you should see your post. 203 00:11:02,290 --> 00:11:04,810 And if we create a second post: 204 00:11:04,810 --> 00:11:09,810 MySQL + Node.js is amazing. 205 00:11:11,460 --> 00:11:16,290 A truly awesome combination 206 00:11:16,290 --> 00:11:18,830 of technologies. 207 00:11:18,830 --> 00:11:21,630 You can easily send 208 00:11:21,630 --> 00:11:26,020 SQL commands and queries 209 00:11:26,020 --> 00:11:31,020 to your MySQL database when working with Node + MySQL. 210 00:11:35,790 --> 00:11:37,820 It's easy 211 00:11:37,820 --> 00:11:41,560 and allows you to build real websites! 212 00:11:41,560 --> 00:11:44,920 If you add that, maybe with a different author, 213 00:11:44,920 --> 00:11:48,523 and you click Add Post, you see that post here as well. 214 00:11:49,590 --> 00:11:52,740 So this is all working, fetching the data is working, 215 00:11:52,740 --> 00:11:57,220 and hence we now made another major step forward. 216 00:11:57,220 --> 00:11:58,410 As a next step, 217 00:11:58,410 --> 00:12:01,640 it would now be nice if we could click View Post 218 00:12:01,640 --> 00:12:04,960 and we would then be taken to the Detail page 219 00:12:04,960 --> 00:12:08,610 where we can view more post details, 220 00:12:08,610 --> 00:12:11,340 so where those blanks are filled, 221 00:12:11,340 --> 00:12:15,650 before we then move on to deleting and editing posts. 222 00:12:15,650 --> 00:12:17,810 Now, if you feel like it, 223 00:12:17,810 --> 00:12:20,400 definitely again take this as a challenge 224 00:12:20,400 --> 00:12:23,660 and try as many of these things on your own 225 00:12:23,660 --> 00:12:26,520 before continuing with the next lectures. 226 00:12:26,520 --> 00:12:30,050 Try adding a route for handling a single post, 227 00:12:30,050 --> 00:12:33,240 maybe with some dynamic route parameters, 228 00:12:33,240 --> 00:12:35,560 fetch the data for that single post 229 00:12:35,560 --> 00:12:38,790 with the features you learned about thus far, 230 00:12:38,790 --> 00:12:41,380 display some single post data here, 231 00:12:41,380 --> 00:12:45,270 and maybe even implement deleting and updating. 232 00:12:45,270 --> 00:12:48,580 These are all exercises you can take and go through. 233 00:12:48,580 --> 00:12:51,630 You can also pause later throughout the next lectures 234 00:12:51,630 --> 00:12:52,990 if you wanna follow along 235 00:12:52,990 --> 00:12:55,760 for some parts but not all of them. 236 00:12:55,760 --> 00:12:58,430 But definitely at least think about trying 237 00:12:58,430 --> 00:13:00,500 some of those things on your own 238 00:13:00,500 --> 00:13:02,460 since it will be a great practice 239 00:13:02,460 --> 00:13:04,153 of the concepts you learned. 240 00:13:05,010 --> 00:13:06,770 With that, in the next lecture, 241 00:13:06,770 --> 00:13:09,833 we are going to work on this post-detail page here.