php - how to make pagination with dates in laravel

Question

I Want to Make pagination in laravel with date format below is my table

(id, customer_id, bill_no, from_date, to_date)
(191, 2, 1, '2020-12-20', '2020-12-26')
(192, 3, 2, '2020-12-20', '2020-12-26')
(193, 4, 3, '2020-12-20', '2020-12-26')
(194, 5, 4, '2020-12-20', '2020-12-26')
(195, 6, 5, '2020-12-20', '2020-12-26')
(196, 7, 6, '2020-12-20', '2020-12-26')
(197, 8, 7, '2020-12-20', '2020-12-26')
(198, 2, 8, '2020-12-27', '2021-01-02')
(199, 3, 9, '2020-12-27', '2021-01-02')
(200, 4, 10, '2020-12-27', '2021-01-02')
(201, 5, 11, '2020-12-27', '2021-01-02')
(202, 6, 12, '2020-12-27', '2021-01-02')
(203, 7, 13, '2020-12-27', '2021-01-02')
(204, 8, 14, '2020-12-27', '2021-01-02')

this is my code i tried this :

$weekly_billings_for_cash = DB::table('weekly_billing')
   ->Join('customers', 'weekly_billing.customer_id', '=', 'customers.id') 
   ->select('weekly_billing.*','customers.customer_name','customers.customer_type') 
   ->orderBy('weekly_billing.bill_no', 'asc') 
   ->simplePaginate(16);

i want to paginate with date wise like from date 2020-12-20 to 2020-12-26 in first page date 2020-12-27 to 2020-01-02 in second page and so on how can i achieve any help would be appreciated. thank You

question from:https://stackoverflow.com/questions/65541098/how-to-make-pagination-with-dates-in-laravel

Answer 1

I suppose the difference between your dates are always 6 days and from_date and to_date in your table are string. You should customize the page number. We must have a date as start. For example lets say we start our pagination from 2020-12-20 Suppose user asks for page number 2.

$page = $request->input('page', 1)

We will make the dates using Carbon:

$from = new Carbon(new Datetime('2020-12-20'))->addWeeks($page - 1);
$to = new Carbon(new Datetime('2020-12-19'))->addWeeks($page);

Notice I used '2020-12-19' because of the form of data in your table. Now we can get 'Year-Month-Day' from these Carbon objects.

$fromString = $from->format('Y-m-d'); // It gives something like 2020-12-27
$toString = $to->format('Y-m-d');

And the query:

$weekly_billings_for_cash = DB::table('weekly_billing')
->Join('customers', 'weekly_billing.customer_id', '=', 'customers.id') 
->select('weekly_billing.*','customers.customer_name','customers.customer_type') 
->orderBy('weekly_billing.bill_no', 'asc')
->where('customers.from_date', '=', $fromString)->where('customers.to_date', '=', 
$toString);