Laravel 5.1 eloquent query orderby external data and paginated

Question

I'm trying to get data from an Eloquent query (that works), then order the data, then paginate the data.

I had the code ordering by date, then paginating, but now I want to order by facebook API obtained data (I get the data correctly).

The thing is I don't know what I should do first (paginate or ordering):

If I paginate first, I don't know how to order the data since the object is LengthAwarePaginator and doesn't have any orderBy method.
If I order first, I get a collection object and can't use ->paginate($perPage) to do the pagination.

This is the code:

$posts = Post::with('User')->
                where('created_at', '<', new \DateTime)->
                paginate($perPage);
$counter = 0;
foreach ($posts as $post) {
    $url = 'http://myweb.com/' . $post['id'];
    $fb_stuff = json_decode(file_get_contents('https://api.facebook.com/method/links.getStats?urls=' . $url . '&format=json'), true);
    $posts[$counter]['share_count'] = $fb_stuff[0]['share_count'];
    $counter++;            
}

jedrzej.kurylo jedrzej.kurylo · Accepted Answer · 2015-07-11T21:41:27

If you need to sort by some value that is not stored in the database then the only option is to fetch all records from the database, fetch the sorting data from external source (Facebook API), sort it by user defined function (see http://php.net/manual/en/function.usort.php) and then paginate.

Once you have your data sorted you can easily get the paginated version by creating a Paginator object:

$paginator = new \Illuminate\Pagination\Paginator($posts, $perPage, $currentPage);

Anyway, this solution will be quite heavy as you'll need to fetch data from Facebook API every time you want a sorted list of posts. I doubt you need real time data so I suggest to store share_count in your posts table and refresh it on regular basis, e.g. by running a scheduled laravel command.

Laravel 5.1 eloquent query orderby external data and paginated

1 Answers