1

I was reading this article to work out how to sort records in my database based on how many likes they have:

Laravel OrderBy relationship count

I came up with this which works:

$Book = Book::with('likes')->get()->sortByDesc(function($book_sort)
{
    return $book_sort->likes->count();
});

Which is based upon this Book model:

namespace App;

use Illuminate\Database\Eloquent\Model;

class Book extends Model
{
    public $timestamps = true; 


    protected $fillable = [
    'title', 'author', 'category', 'featured', 'rating', 'description'
    ];

    public function category()
    {
        return $this->hasOne('App\Cat', 'id', 'category_id');
    }
    public function likes()
    {
        return $this->belongsToMany('App\User', 'favourite_books')->withTimestamps();
    }
    public function total_likes()
    {
        return $this->likes()->count();
    }
}

However now I am stuck on how I would paginate these results. Does anyone know?

Community
  • 1
  • 1
Magearlik
  • 523
  • 1
  • 6
  • 18

2 Answers2

1

Create manual pagination, try this:

$Book = Book::with('likes')->get()->sortByDesc(function($book_sort)
{
     return $book_sort->likes->count();
}); 

$paginator = new Illuminate\Pagination\Paginator($Book, 10);

return view('pages.homepage', compact('paginator'))
Jose Rojas
  • 3,490
  • 3
  • 26
  • 40
  • 1
    Sorry, but I don't think `paginate()->sortByDesc(function(){};)` will work properly here. – Alexey Mezenin Apr 22 '16 at 13:34
  • sortByDesc can be applied over collection https://laravel.com/docs/5.2/collections#method-sortby, the paginate method return a collection according to API of laravel https://laravel.com/api/5.2/Illuminate/Database/Eloquent/Builder.html#method_paginate – Jose Rojas Apr 22 '16 at 13:41
  • but maybe you can right @AlexeyMezenin, I haven't tested it before. – Jose Rojas Apr 22 '16 at 13:46
  • I get an error 'Method appends does not exist'. I have this at the end of my results page to print the page numbers: – Magearlik Apr 22 '16 at 14:44
  • @Magearlik What is Request::only('q') ?, I see you have $Books at the blade but in your question is $Book – Jose Rojas Apr 22 '16 at 15:19
  • Request::only('q') makes sure the search results get paginated. The search text field is called 'q' for query. – Magearlik Apr 23 '16 at 03:21
  • What about in your blade is `$Books` and the Controller `$Book`, this isn't your issue, right? – Jose Rojas Apr 23 '16 at 03:27
  • Even if I just do $Books->render() to show the page numbers it still doesn't work. I get Call to undefined method Illuminate\Database\Query\Builder::sortByDesc() – Magearlik Apr 23 '16 at 03:30
  • It is Books in the controller and the view. – Magearlik Apr 23 '16 at 03:31
  • How are you passing the data to the view? – Jose Rojas Apr 23 '16 at 03:32
  • return view('pages.homepage', compact('Books')); – Magearlik Apr 23 '16 at 03:35
  • try with `view('pages.homepage')->with($Books)` – Jose Rojas Apr 23 '16 at 03:44
  • Same error: "Call to undefined method Illuminate\Database\Query\Builder::sortByDesc() – Magearlik Apr 23 '16 at 03:47
  • I think the error is because sortByDesc creates a different data type then what paginate wants. And you can't do this by using orderBy as far as I know – Magearlik Apr 23 '16 at 03:49
  • you want to order by number of likes that every book, right? – Jose Rojas Apr 23 '16 at 04:08
  • Yep that works! However how would I get it to give me page numbers rather than previous and next? – Magearlik Apr 23 '16 at 06:55
  • 1
    @Magearlik, this can really be a perfomance hit. What are you doing here is getting ALL `books` with ALL `likes` and only then counting, sorting them out and paginating. It will kill your hosting's memory and CPU. Paginate is designed to query only part of data, that's why it's useful. – Alexey Mezenin Apr 23 '16 at 07:02
  • Ok so I should create a new table for counting the likes for each book? – Magearlik Apr 23 '16 at 07:09
  • @AlexeyMezenin is right, in case the records are so much the performance could be poor, so you can post your tables and structure to look at other alternatives? – Jose Rojas Apr 23 '16 at 12:09
0

sortBy() and sortByDesc() are working with collections only. orderBy() is working only with column name. Also, getting results, sorting and paginating them is a perfomance hit. It will also can eat all the memory.

So, the only solution I can see here is to use orderByRaw() or even raw query.

Alexey Mezenin
  • 158,981
  • 26
  • 290
  • 279