WEBVTT 00:00.710 --> 00:01.340 Okay guys. 00:01.340 --> 00:04.430 So next up we're going to cover the pagination. 00:04.430 --> 00:10.340 So pagination is essentially revolving around the SQL queries. 00:10.340 --> 00:17.120 And it means that we want the result of specific SQL query to be returned in chunks. 00:17.360 --> 00:19.790 And those chunks are called pages. 00:19.880 --> 00:23.810 You can customize the size of every single chunk. 00:23.810 --> 00:28.700 So it can be ten elements per chunk or 20 or 50. 00:28.730 --> 00:29.930 It's up to you. 00:29.960 --> 00:37.190 So a quick reminder by using the pagination we will optimize the data fetching. 00:37.220 --> 00:45.020 So it's always more efficient if you can fetch less items than if you have to fetch more. 00:45.230 --> 00:55.040 And if you multiply every single request for a list of blog posts by, let's say, 300 users, we can 00:55.040 --> 01:05.200 quickly see how getting just a chunk of the results instead of the full list of blog posts can be more 01:05.200 --> 01:10.090 optimal for the compute power and for potential cost. 01:10.120 --> 01:18.550 Now also, as we have previously said, recency matters and typically you will be fetching the items 01:18.550 --> 01:20.590 that are more recent. 01:21.010 --> 01:21.370 Okay. 01:21.400 --> 01:29.050 So after this short reminder, let's just jump and see what we need to do to implement pagination. 01:29.770 --> 01:34.480 We're going to start with the post controller as that's our entry point. 01:34.510 --> 01:38.440 And I'd like to start with the parameters first. 01:38.470 --> 01:42.700 So the page will be passed by the URL parameters. 01:42.700 --> 01:45.220 And it will also have a default. 01:45.220 --> 01:48.760 That's why I'm getting it from the Get super global. 01:51.040 --> 01:55.990 So if page is not specified we assume the current page is one. 01:56.980 --> 01:59.470 Now we can make the limit parameter. 01:59.470 --> 02:05.920 So how many items per page should be displayed also part of the query parameters. 02:05.950 --> 02:14.860 I'm just gonna leave it as static value, and I'm setting it to two just because we only have three 02:14.860 --> 02:19.360 blog posts, if I remember correctly, loaded through fixtures. 02:19.450 --> 02:23.290 So that's the only way we can see multiple pages. 02:25.480 --> 02:27.970 Okay, so those were the parameters. 02:29.020 --> 02:35.290 Maybe let's separate the parameters from database interactions. 02:36.070 --> 02:38.080 So we would need to fetch the post. 02:38.080 --> 02:47.470 But also we would have to know how many posts do we have in total when we apply all the criteria like 02:47.470 --> 02:48.970 the search query. 02:49.930 --> 02:51.760 So that's a separate thing. 02:51.790 --> 02:57.790 We should add some kind of a method that can give us this info. 02:57.850 --> 03:04.180 I might call it temporarily count and I need to pass the search query to it. 03:04.390 --> 03:06.540 So it's non-existing right now. 03:06.540 --> 03:08.730 So I comment that out. 03:10.770 --> 03:16.500 Next up we should pass some data to the view like the current page. 03:16.530 --> 03:18.210 That would be useful. 03:18.450 --> 03:22.710 So we can generate links to different pages etc.. 03:22.710 --> 03:27.240 And also we need to know how many pages do we have. 03:28.200 --> 03:35.250 And this can be easily calculated by dividing the number of total items. 03:35.250 --> 03:40.350 So the total blog posts by the limit. 03:42.420 --> 03:49.320 And rounding this up so we don't have the total value yet. 03:49.320 --> 03:52.680 So I also comment this one out for now. 03:54.210 --> 03:57.870 So let's jump to the database models shall we. 03:57.900 --> 04:02.100 As we would have to make some changes maybe. 04:02.100 --> 04:03.570 Let's start with this. 04:03.570 --> 04:10.190 Get recent method as it also needs to incorporate the pagination. 04:10.490 --> 04:15.200 So starting with parameters we've got the limit. 04:15.230 --> 04:17.510 We've got the search query. 04:17.570 --> 04:20.750 But we are missing the page. 04:20.930 --> 04:23.090 Let me add the page parameter. 04:24.920 --> 04:28.220 So it will also be nullable. 04:28.910 --> 04:36.650 And if we scroll this method we already have the handling of a limit. 04:36.650 --> 04:42.320 But this only limits the amount of results that we're going to receive. 04:42.770 --> 04:46.520 To also add pagination we need offset. 04:47.450 --> 04:52.100 So to understand the concept of the offset, let's take a look at this diagram. 04:52.100 --> 04:57.290 Again assuming we would like to get ten items per page. 04:57.530 --> 05:01.430 The offset for the first page would be zero. 05:01.940 --> 05:06.020 And then we just grab the ten consecutive items. 05:06.020 --> 05:12.710 So including the item at position zero, the last one would have the index nine. 05:12.770 --> 05:23.390 For the second page, we need to increase the offset by ten so it becomes ten and we take ten items. 05:23.390 --> 05:25.640 So the last item is 19. 05:26.900 --> 05:30.020 Consequently, for the third page works the same. 05:30.020 --> 05:34.610 We increase the offset by the amount of items per page. 05:34.610 --> 05:39.650 So if it's ten the offset for the third page would be 20. 05:43.070 --> 05:50.660 So we can leave this if statement checking for the limit, because sometimes the limit can serve different 05:50.660 --> 05:51.350 purpose. 05:51.350 --> 06:00.050 In this method we can just limit the recent posts to let's say five most recent posts. 06:00.980 --> 06:12.130 So we should only add the offset if both the page is not null And when the limit is not null, this 06:12.130 --> 06:22.000 means someone actually expects the pagination, and in such cases we should calculate the offset. 06:22.720 --> 06:24.160 So the offset. 06:24.190 --> 06:26.680 Well, we need to work with the page. 06:28.180 --> 06:35.830 If page is one we need to subtract one from it and multiply by limit. 06:35.920 --> 06:40.510 This is how we are going to receive what I've explained previously on the diagram. 06:40.510 --> 06:43.420 So for the page that is one. 06:43.450 --> 06:45.760 The offset starts at zero. 06:45.790 --> 06:53.230 But for the second page the offset is page minus one times limit. 06:53.260 --> 06:54.580 That's ten. 06:58.540 --> 06:59.110 K. 06:59.110 --> 07:02.710 And then we need to add something to the query. 07:03.580 --> 07:06.520 So let's use concatenate equals. 07:07.000 --> 07:10.930 And in SQL this is just called Offset. 07:10.960 --> 07:11.440 Offset. 07:11.470 --> 07:14.200 Keyword and the parameter. 07:15.940 --> 07:21.310 So we need to add the calculated offset to the params. 07:21.310 --> 07:24.460 And we've got the pagination. 07:25.480 --> 07:34.390 If we now jump to the post controller where we actually want to paginate data, we can pass the page 07:34.390 --> 07:38.350 as the second parameter or just use the named parameters. 07:39.940 --> 07:45.100 Next up, let's solve the missing count of the total items. 07:45.130 --> 07:50.650 This needs to take into the account the search query if there is any. 07:51.640 --> 07:54.070 So let's add this method because it's missing. 07:54.070 --> 08:03.340 And I'm gonna start by copy pasting the get reset method because the count method would be similar to 08:03.370 --> 08:05.890 it, but just simpler. 08:07.060 --> 08:15.930 So in the count method we don't need the limit nor page because this doesn't make sense in that context. 08:15.960 --> 08:20.490 We want to count everything in this table. 08:20.490 --> 08:23.700 We only need the potential search query. 08:25.740 --> 08:27.060 It's the same as above. 08:27.090 --> 08:35.550 We get the database, we start with the query, and we need to take into account the search query if 08:35.550 --> 08:36.570 there is any. 08:38.820 --> 08:41.430 But we don't need to do any sorting. 08:42.870 --> 08:43.920 There is no limit. 08:43.950 --> 08:47.250 No pagination can remove this fragment. 08:49.590 --> 08:56.070 And the query is slightly different because this query here is fetching all the columns. 08:56.070 --> 09:04.050 And to count the elements using a specific query, you would use count asterisk. 09:04.830 --> 09:10.410 This would instead just give us one single value. 09:10.410 --> 09:13.290 So also that's not fetch all. 09:13.400 --> 09:16.850 this is a query method. 09:18.260 --> 09:23.480 There is no class in here because we would like to get one single value. 09:24.950 --> 09:34.160 So from the query method we can also get one single column value using fetch column. 09:34.190 --> 09:42.080 By default this would return the value at column zero, and since this returns a single value which 09:42.080 --> 09:45.020 would be a number, this is exactly what we need. 09:45.050 --> 09:53.720 And even better, we can cast this to an integer to make sure it is an integer and just add return value 09:53.750 --> 09:55.490 here, which is int. 09:57.560 --> 10:01.610 Let's go back to the post controller now. 10:01.610 --> 10:10.100 So we are missing the links to navigate between the pages, but we're going to do that in the next video. 10:10.130 --> 10:14.090 Though it shouldn't stop us from testing this logic. 10:15.380 --> 10:19.130 So this first parameter this actually needs to be the limit. 10:19.130 --> 10:22.520 So let me change that to the limit variable. 10:23.480 --> 10:29.510 Now we are using the variable in case later on we want to change this to a URL query parameter. 10:29.660 --> 10:39.680 And after I save the changes and I go to posts, I should see only two blog posts instead of three. 10:40.070 --> 10:44.480 And now let me just manually type the query parameter. 10:44.480 --> 10:46.580 So we are on the first page. 10:46.580 --> 10:48.140 That's the default one. 10:48.380 --> 10:54.950 Now let me go to the second page and I think this makes sense. 10:54.950 --> 11:02.150 That was my first blog post, which just confirms that everything works fine. 11:02.150 --> 11:10.280 And all that's missing now are the buttons properly rendered below the blog posts that will take us 11:10.280 --> 11:16.910 to the previous and next pages, and we're gonna do that in the next video.