Răzvan Răzvan - 3 months ago 11
PHP Question

Eloquent ORM pagination Reddit-like - before/after hash-id

I have a table of posts with a rank column. Is there a way to retrieve the previous 20 posts starting from the ID of one post while ordering them by rank. It should behave similarly to reddit's pagination.

Ex. domain.com/posts/previous/{id_of_post_to_start_from}

UPDATE

Let me just rephrase this. The normal laravel/eloquent (simple) pagination will use pages and can be achieved by doing something like this:

$posts = Post::with(array('user'=>function($q){
$q->select('id', 'img','nick', 'points', 'slug');
}))
->with('categories')
->with('voted')
->orderBy('rank', 'DESC')
->orderBy('created_at','DESC')
->simplePaginate(20);
return view('layouts.home')->with('posts',$posts);


this will use an url with a
?page=pageNo
argument. Because the ranking changes quite fast, what I really want is to be able to get the following or previous posts after a certain post identified by an id or hashId. This is the way reddit does it:

https://www.reddit.com/?count=25&after=t3_4g1n0n


Update 2

@Jarek's answer seems to be working when ordering by the rank column as it has very few or no duplicates, but it fails when ordering by the number of votes. It only displays 3 pages instead of 5 and in the last one some rows are skipped. I've been trying to solve this for an entire day and I really don't know where to go next. Please help.

My urls are
/ordered-by-votes?before=hashId
and
/ordered-by-votes?after=hashId


Table structure ordered by
votes desc, created_at desc
:

| id | rank | votes | url | created_at |
+-----+-----------------------------+-----+------------+---------------------+
| 41 | 220.6454978 | 16 | 7X1dqLLNRG | 2016-04-24 08:02:03 |
| 32 | 192.6005579 | 15 | k39djkOGP0 | 2016-04-09 17:49:22 |
| 81 | 401.8382322 | 13 | 7X1dqPLNRG | 2016-07-27 18:04:14 |
| 36 | 192.6460924 | 12 | mZEzDLzxpb | 2016-04-09 19:36:12 |
| 35 | 192.5188647 | 9 | 5ykOxjNBp7 | 2016-04-09 19:34:29 |
| 50 | 325.8341567 | 8 | lm2dpyxOQY | 2016-06-18 06:39:19 |
| 34 | 192.4059869 | 7 | EDMOM0d16m | 2016-04-09 19:31:41 |
| 33 | 192.2031068 | 6 | J6RN0mdGmY | 2016-04-09 17:49:44 |
| 57 | 325.5080990 | 3 | 76mz7R1OGl | 2016-06-18 07:54:15 |
| 56 | 325.5039213 | 3 | KE0NQEkN1p | 2016-06-18 07:51:07 |
| 40 | 193.2540546 | 3 | 796Na28dJr | 2016-04-10 10:43:43 |
| 39 | 193.2262101 | 3 | 6yMNr0Ox-Y | 2016-04-10 10:22:50 |
| 38 | 193.2205435 | 3 | Y21OmeODEp | 2016-04-10 10:18:35 |
| 55 | 325.3238522 | 2 | e4Bd3xoOjg | 2016-06-18 07:48:08 |
| 54 | 325.3145633 | 2 | Z56zRyqOYM | 2016-06-18 07:41:10 |
| 53 | 325.2995633 | 2 | G3JdEMGd0y | 2016-06-18 07:29:55 |
| 52 | 325.2952967 | 2 | gm8dna7Noq | 2016-06-18 07:26:43 |
| 49 | 323.1741856 | 2 | -41zKQndp3 | 2016-06-17 04:55:53 |
| 48 | 323.1371411 | 2 | obmd2DLze2 | 2016-06-17 04:28:06 |
| 47 | 322.2090522 | 2 | 2KGdGR-N9j | 2016-06-16 16:52:02 |
| 45 | 318.4326967 | 2 | ngYN4lRNDB | 2016-06-14 17:39:46 |
| 42 | 244.7367189 | 2 | -arOeX3dby | 2016-05-07 08:27:47 |
| 27 | 191.1572300 | 2 | 7rbzy5zXGm | 2016-04-09 10:43:10 |
| 20 | 183.1333856 | 2 | PE4N13Oa-r | 2016-04-05 06:25:17 |
| 102 | 430.7966222 | 1 | -arOe3mNby | 2016-08-12 09:58:29 |
| 93 | 415.2774667 | 1 | G3JdEjGO0y | 2016-08-04 07:59:07 |
| 80 | 397.8948667 | 1 | 796NaX8zJr | 2016-07-26 06:42:10 |
| 79 | 397.0725111 | 1 | 6yMNrm0zx- | 2016-07-25 20:25:24 |
| 77 | 397.0368667 | 1 | j3-Ob8aN9k | 2016-07-25 19:58:40 |
| 75 | 388.2400889 | 1 | 5ykOxQjzBp | 2016-07-21 06:01:05 |
| 72 | 386.3236889 | 1 | k39djBkdGP | 2016-07-20 06:03:47 |
| 65 | 378.7166889 | 1 | B0kd5bkO7L | 2016-07-16 06:58:32 |
| 64 | 367.0564222 | 1 | l16zlkxOqe | 2016-07-10 05:13:20 |
| 63 | 365.7214000 | 1 | qBEdkk2d2M | 2016-07-09 12:32:04 |
| 62 | 354.0362889 | 1 | Xk3O-1jOEY | 2016-07-03 10:28:14 |
| 61 | 354.0347556 | 1 | kryNP1Kd9a | 2016-07-03 10:27:05 |
| 60 | 353.5897333 | 1 | PE4N123da- | 2016-07-03 04:53:19 |
| 58 | 329.7797111 | 1 | J-azJQ2Oeb | 2016-06-20 19:15:48 |
| 44 | 257.9238889 | 1 | jexdZ0JzR5 | 2016-05-14 09:03:56 |
| 59 | 350.6545778 | 0 | QK2N6yKzn6 | 2016-07-01 16:11:57 |
| 51 | 324.9408000 | 0 | 2P8O8P-Opm | 2016-06-18 06:46:37 |
| 46 | 321.4635111 | 0 | Da8zBj6z-Y | 2016-06-16 11:18:39 |
| 43 | 249.2581556 | 0 | e6XOLJENQ3 | 2016-05-09 20:44:38 |
| 92 | 413.8514933 | -26 | gm8dn07zoq | 2016-08-04 07:50:52 |


The
after
method in the model

public function scopeAfter($query, self $post)
{
return $query->orderBy('votes', 'desc')
->latest()
->where('status', '=', 'approved')
->where('id', '<>', $post->id)
->where('votes', '<=', $post->votes)
->where('created_at', '<=', $post->created_at);
}


The controller:

$posts=null;
if(isset($req['before'])){
//...
} elseif (isset($req['after'])){
$prevPost = Post::where('url','=',$req['after'])->first();
$posts= Post::after($prevPost)
->with('categories', 'voted')
->with(array('user'=>function($q){
$q->select('id', 'img','nick', 'points', 'slug');
}))
->limit(10)
->get();
} else {
// if no params, get the first 10
$posts= Post::with('categories', 'voted')
->with(array('user'=>function($q){
$q->select('id', 'img','nick', 'points', 'slug');
}))
->where('status','=','approved')
->orderBy('votes', 'desc')
->orderBy('created_at', 'desc')
->limit(10)
->get();
}

//return $posts;


The view links

<a href="{{url('/ordered-by-votes') . '?' . http_build_query(['after' => $lastPost->url])}}" class="btn after">< Previous</a>
<a href="{{url('/ordered-by-votes') . '?' . http_build_query(['before' => $firstPost->url])}}" class="btn before">Before ></a>


Update 3

There should be 5 pages with 44 results, but only 3 pages and 27 results are displayed.

Page 1 - correct - 10 results

from 7X1dqLLNRG to KE0NQEkN1p


Page 2 - correct - 10 results

from 796Na28dJr to 2KGdGR-N9j


Page 3 - incorrect - only 7 results are displayed in the wrong order

ngYN4lRNDB (OK)
-arOeX3dby (OK)
7rbzy5zXGm (OK)
PE4N13Oa-r (OK)
jexdZ0JzR5 (NOT OK)
Da8zBj6z-Y (NOT OK)
e6XOLJENQ3 (NOT OK)

Answer

Here's how you can work with the code:

// controller
$post = Post::findByWhateverYouWant($key_from_route);

// higher ranked:
$paginator = Post::before($post)
                 ->with('categories', 'votes')
                 ->simplePaginate(20);

// you need to reverse this result in order to sort 
// appropriately but AFTER the query was executed   
return $paginator->setCollection($paginator->reverse());




// lower ranked:
return Post::after($post)
           ->with('categories', 'votes')
           ->simplePaginate(20);

and here's the model with mentioned scopes:

public function scopeBefore($query, self $post, $sortBy = 'votes')
{
    return $query->orderBy($sortBy)
                 ->orderBy('created_at')
                 ->where('id', '<>', $post->id)
                 ->where(function ($q) use ($post, $sortBy) {
                    $q->where($sortBy, '=', $post->{$sortBy})
                      ->where('created_at', '>=', $post->created_at)
                      ->orWhere($sortBy, '>', $post->{$sortBy});
                 });
}

public function scopeAfter($query, self $post, $sortBy = 'votes')
{
    return $query->orderBy($sortBy, 'desc')
                 ->latest()
                 ->where('id', '<>', $post->id)
                 ->where(function ($q) use ($post, $sortBy) {
                    $q->where($sortBy, '=', $post->{$sortBy})
                      ->where('created_at', '<=', $post->created_at)
                      ->orWhere($sortBy, '<', $post->{$sortBy});
                 });
}

You may add 3rd param to these scopes in order to make 2nd column customizable if you like.