Doctrine returning datetime object instead of date string, jqGrid jsonmap not working

Question

I'm trying to display date in my jqgrid. But doctrine is returning a datetime object instead of a normal string type value which jqgrid can display.

I can think of two possible solutions:

(1) Change doctrine's return type to somehow return a string so that jqgrid can display it without a problem. (Currently the json is returning an object)

(2) Use jsonmap to manipulate the json (The datetime is coming in as an object but it does display proper data inside)

My code:

(Doctrine related)

$qb = $this->em->getRepository('ProductOrderLookupBundle:Product')->createQueryBuilder('u');
$qb->select('u.id, u.productName, u.productDetails,u.shipDate, u');
$qb->getArrayResult();

(Jqgrid related)

jQuery(document).ready(function(){
            jQuery("#list").jqGrid({
                url:"{{ asset('/app_dev.php/_thrace-datagrid/data/user_management') }}",
                datatype: 'json',
                mtype: 'POST',
                colNames:['Product ID','Product Name', 'Product Details', 'Shipment Date', 'Actions'],
                colModel :[
                    {name:'id', index:'u.id', width:200},
                {name:'productName', index:'u.productName', width:200, editable: true},
                {name:'productDetails', index:'u.productDetails', width:200, align:'right', editable: true},
                {name:'shipDate', index: 'u.shipDate', editable: true,jsonmap: function (obj) { return getVaueByName(obj.cells, "date"); }},
                {name:'actions',index:'actions',sortable:false, search:false}
            ],
other jqgrid related code
var getVaueByName = function (cells, colName) {
        var i, count = cells.length, item;
        for (i = 0; i < count; i += 1) {
            item = cells[i];
            if (item.colname === colName) {
                return item.value;
            }
        }
        return '';
    };

My json

{
  "page":1,
  "total":20000,
  "records":1000000,
  "rows":
    [
      {"id":1,"cell":[1,"Door","Details",{"date":"2012-06-18 00:00:00","timezone_type":3,"timezone":"Europe\/Paris"}]},
      {"id":2,"cell":[2,"Door2","Details2",{"date":"2012-06-18 00:00:00","timezone_type":3,"timezone":"Europe\/Paris"}]},
      {"id":3,"cell":[3,"Door3","Details3",{"date":"2012-06-18 00:00:00","timezone_type":3,"timezone":"Europe\/Paris"}]},
      {"id":4,"cell":[4,"Door4","Details4",{"date":"2012-06-18 00:00:00","timezone_type":3,"timezone":"Europe\/Paris"}]},
      {"id":5,"cell":[5,"Door5","Details5",{"date":"2012-06-18 00:00:00","timezone_type":3,"timezone":"Europe\/Paris"}]},
      {"id":6,"cell":[6,"Door6","Details6",{"date":"2012-06-18 00:00:00","timezone_type":3,"timezone":"Europe\/Paris"}]},
      {"id":7,"cell":[7,"Door7","Details7",{"date":"2012-06-18 00:00:00","timezone_type":3,"timezone":"Europe\/Paris"}]},
      {"id":8,"cell":[8,"Door8","Details8",{"date":"2012-06-18 00:00:00","timezone_type":3,"timezone":"Europe\/Paris"}]},
      {"id":9,"cell":[9,"Door9","Details9",{"date":"2012-06-18 00:00:00","timezone_type":3,"timezone":"Europe\/Paris"}]},
      {"id":10,"cell":[10,"Door10","Details10",{"date":"2012-06-18 00:00:00","timezone_type":3,"timezone":"Europe\/Paris"}]}
    ]
}

In this all I want is the value under date column. so far I've tried this and this . Both are front end related. I have not been able to find doctrine related solutions. I cannot use jsonReader: { repeatitems: false } because it doesn't load my data for some reason.

This is what my current grid looks like

Any kind of solution will be appreciated.

Thanks

Answer 1

I think you should modify a query result a little before passing it to jqGrid, may be like this:

$qb = $this->em->getRepository('ProductOrderLookupBundle:Product')->createQueryBuilder('u');
$qb->select('u.id, u.productName, u.productDetails,u.shipDate, u');
$result = $qb->getArrayResult();

$returnValue = [];

foreach ($result as $value) {
    $returnValue[] = [
        'shipDate' => $value['shipDate']->format('Y-m-d' /* which format you wish */) // I assume $value['shipDate'] is returning a DateTIme object
        // other elements go here
    ];
}

// I'm assuming that you're returning a json response;
$response = new JsonResponse();
$response->setData($returnValue);

return $response;

Answer 2

What you can do is something like the following: http://jsfiddle.net/OlegKi/0fvajwhp/

It uses jsonReader: { repeatitems: false } first of all, but it uses additionally jsonmap property in every column of colModel:

colModel: [
    {name: 'id', index:'u.id', width: 110},
    {name: 'productName', jsonmap: "cell.1", index:'u.productName', width: 120, editable: true},
    {name: 'productDetails', jsonmap: "cell.2", index:'u.productDetails', width: 120, align:'right', editable: true},
    {name: 'shipDate', jsonmap: "cell.3.date", index: 'u.shipDate', align: "center", formatter: "date", editable: true, width: 120 },
    {name: 'actions', formatter: "actions", width: 70, align: "center", sortable:false, search:false}
]

The value jsonmap: "cell.1" inform to read first from cell property and then get 1 element of the cell. To read the date one need to use jsonmap: "cell.3.date". One can use alternatively function form of jsonmap:

jsonmap: function (obj) { return obj.cell[1]; } // to read productName

and

jsonmap: function (obj) { return obj.cell[3].date; } // to read date