Formatting number in Eloquent query or Yarja Datatables

Question

I am writing a searchable table which includes the area and population. Here is the basic query:

public function getCountryData()
  {
    $co = DB::table('countries')->leftJoin('country_detail','country_detail.country_id','=','countries.id')->addSelect(['countries.id','countries.name','country_detail.capital','country_detail.area','country_detail.iso3','country_detail.population','country_detail.currencyName','country_detail.phone','country_detail.continent'])->get();
    return Datatables::of($co)
    ->addColumn('action', function($co){
                            $btn = '<div style="float:right">
                            <a href="'. route('country.edit',$co->id) .'  " class="btn btn-outline-secondary btn-xs" title="edit" style="margin-right:.5em">'.getEditIcon().'</a><a href="'. route('country.show', $co->id) .'" class="btn btn-outline-secondary btn-xs" title="images" style="margin-right:.5em">'.getBinoculars().'</a>';

                             return $btn;
                     }) ->rawColumns(['action'])
                 ->make(true);

  }

All this works fine in my view except that the population field, for example, returns something like 29121286 and of course I want to format it so it is 29,121,286.

Can this be done in the query or something in Datatables itself?

score 1 · Accepted Answer · answered Jan 01 '20 at 12:13

1

Instead of formatting the number on client side using javascript, I suggest using php's number_format function to format your number.

return Datatables::editColumn('population', function($item) {
    return number_format($item->population);
});

For more help on formatting numbers, see https://www.php.net/manual/en/function.number-format.php

answered Jan 01 '20 at 12:13

Hammad Rasheed

241
1
7

this is more convenient than frontend formatting – owf Feb 04 '22 at 07:04

score 0 · Answer 2 · answered Dec 30 '19 at 10:28

Thanks to Navok on Laracasts here is the answer.

Firstly you need a javascript function to convert the string to a number, properly formatted.

function formatNumber(num) {
  return num.toString().replace(/(\d)(?=(\d{3})+(?!\d))/g, '$1,')
}

I have added this in a file called misc.js (so I can have other things) which I call from the layouts.app so it is available system wide.

This can be found at https://blog.abelotech.com/posts/number-currency-formatting-javascript/.

Then within the datatables declaration on your page add

createdRow: function (row, data, dataIndex) {
        if (data.population !== undefined) {
            // 4 here is the cell number, it starts from 0 where this number should appear
            $(row).find('td:eq(4)').html(formatNumber(data.population)); 
        }
    },

I hope this is of value.

Formatting number in Eloquent query or Yarja Datatables

2 Answers2