How to set all past dates at the end of resulting data?

Hello, In laravel 5.7 app I have data from server ordered by boolean field(invoices.was_invoice_sent) and datetime field(invoices.billed_through_date)

        $invoicesCollection = Invoice
            ::orderBy('invoices.was_invoice_sent', 'asc')
            ->orderBy('invoices.billed_through_date', 'asc')

and on client side I set property not to sort on client side:

    oTable = $('#get-invoice-dt-listing-table').DataTable({
        processing: true,
        serverSide: true,
        "ordering": false,
        "lengthChange": true,

My question is if there is a way to set all past dates by invoices.billed_through_date(less today) at the end of resulting data?

Thanks!

SilenceBringer

5 years ago

Level 55

You can try something like:

$invoicesCollection = Invoice ::orderBy(\DB::raw('if(curdate() > date(billed_through_date), 1, 0)'))
    ->orderBy('was_invoice_sent')
    ->orderBy('billed_through_date')

The first statement will check if the billed_through_date date is less then current date, assign 1 if yes and 0 if no. After sorting (asc by default) all records with the date lesss then current date will be at the end of result.

If you need to check by datetime (not by date part only), need to little change the query

$invoicesCollection = Invoice ::orderBy(\DB::raw('billed_through_date > now(), 1, 0)'))
    ->orderBy('was_invoice_sent')
    ->orderBy('billed_through_date')

this time all records prior to current time will be at the end.

1 like

Please or to participate in this conversation.