Laravel Pagination group by year and month only [d

Question

This question already has an answer here:

• Laravel pagination not working with group by clause 4 answers

I want to make paginate from laravel, group by year and month only
when i used get , it work well

$data = DB::table('gallery')
        ->select(DB::raw('DATE_FORMAT(created_at, "%Y-%m") as tanggal,count(created_at) as jumlah'),'gallery.*')
        ->where('type','Foto')
        ->groupBy('tanggal')
        ->orderBy('tanggal','desc')
        ->get();

but when i use paginate , there is an error :
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'tanggal' in 'group statement' (SQL: select count(*) as aggregate from gallery where type = Foto group by tanggal)

$data = DB::table('gallery')
        ->select(DB::raw('DATE_FORMAT(created_at, "%Y-%m") as tanggal,count(created_at) as jumlah'),'gallery.*')
        ->where('type','Foto')
        ->groupBy('tanggal')
        ->orderBy('tanggal','desc')
        ->paginate(2);

Or any idea for group by just year and month laravel 5.4?

Answer 1

From https://laravel.com/docs/5.4/pagination

Currently, pagination operations that use a groupBy statement cannot be executed efficiently by Laravel. If you need to use a groupBy with a paginated result set, it is recommended that you query the database and create a paginator manually.

Here's an alternative solution provided on Jen's Segers's blog