How can I use a Ext JsonReader to return a Master Detail record set?

Question

Let's say I have an invoice and an invoice item. I'd like to show a list of invoices in a grid on the top and below I want to show the corresponding invoice items to the selected invoice. I have the SQL and JSON part down fine. I query the invoices, query the invoices items for all invoices returned (only 2 queries). Then I match up the items with their invoices. And finally I convert this into JSON. It would look something like this.

{
  "success": true,
  "results": 2,
  "rows": [
    {
      "data": {"id": 1, "invoiceDate": "2010-01-01", "total": "101.00" },
      "invoiceItems": [
        {"id": 11, "invoiceID": 1, "item": "baseball", "subtotal": "50.00" },
        {"id": 12, "invoiceID": 1, "item": "bat", "subtotal": "51.00" }
      ]
    },
    {
      "data": {"id": 2, "invoiceDate": "2010-02-02", "total": "102.00" },
      "invoiceItems": [
        {"id": 21, "invoiceID": 2, "item": "hat", "subtotal": "52.00" },
        {"id": 22, "invoiceID": 2, "item": "baseball", "subtotal": "50.00" }
      ]
    }
  ]
}

So when I select invoice 1 in the top grid, I want to see items 11 and 12 displayed in the botton grid. And then show 21 and 22 when invoice 2 is selected. I'd like to NOT have to return to the server every time I toggle between invoices.

And then finally, I'd love to be able to track which ones have changes so that I can send data back to be persisted.

How is this all possible using Ext JS? I've yet to see a working master detail example using Ext JS.

Answer 1

This is certainly possible with ExtJS and I suggest ExtJS provides tools to help.

However, you might be encountering trouble if you are trying to use a single store to contain your JSON records. I recall reading (I searched for a reference, but was unable to find it) you should think of a store as a single database table rather than trying to store parent/child information in one store.

So, I humbly suggest you store your invoices in one store and your invoice items in a second store, link the child invoice items to the parent invoice via some reference (invoice ID), and use these two stores to support two different grids (or whatever widget) - one for invoices and a second for invoice items. When a user clicks on an invoice, your listener (event handler) would update the invoice items grid/widget appropriately.

This would be my approach.

Answer 2

This is certainly possible however you aren't really mapping the sub-objects rather than just expecting them to be there...

Consider this test case (stick it into FireBug and you should see the results..)

var store = new Ext.data.JsonStore({
    data: {
success: true, result: [
{
test: {prop1: 1, prop2: 2}
}]
},
    root: 'result',
    fields: ['test']
});

console.log(store.getRange()[0].data.test.prop1); // prints "1"

In your instance you would do something like this in your row select event...

//assume "this" = your panel containing your Grid (at position 0) and another Grid (at position 1)
var selectedRowRecord = this.get(0).getSelectionModel().getSelected();

var invoiceItemsStore = this.get(1).getStore();
invoiceItemsStore.removeAll();
invoiceItemsStore.loadData(selectedRowRecord.data.invoiceItems);

Hope this helps. Stuart

Answer 3

in that case, you need two readers as code below:

var reader2 = new Ext.data.JsonReader({
    root: 'invoiceItems',
    fields: [{name: 'id', type:'int'},
             {name: 'invoiceID', type:'int'},
             {name: 'item', type:'string'},
             {name: 'subtotal': type:'float'}]
});
var reader = new Ext.data.JsonReader({
    idProperty: 'id',
    totalProperty: 'results',
    successProperty: "success",
    root: 'rows',
    fields: [
        {name: 'id', type:'int'},
        {name: 'invoiceDate', type:'date'},
        {name: 'total', type:'float'},
        {name: 'invoiceItems', convert: function(v, n){ return reader2.readRecords(n).records;} }//v: value, n: data;
    ]
});

var conn = new Ext.data.Connection({
    timeout : 120000,
    url: 'address-path-to-get-json-data',
    method : 'POST'
});
var dproxy = new Ext.data.HttpProxy(conn);

var gstore = new Ext.data.Store({
    proxy: dproxy,
    reader: reader,
    sortInfo:{field: 'id', direction: "DESC"}
});

and here is code you need to render the grid

var numrender = function(value, cell, rec, rowIndex, colIndex, store){
    if(value*1>0){
        return Ext.util.Format.number( value, '0,000.00');
    }else return '-';
}
var invoicedetail = function(value, cell, rec, rowIndex, colIndex, store) {
    var html = '<div class="itemdetail">{0} - {1} - {2} - {3}</div>';
    var re = '';
    Ext.each(value,function(item,index){
        re += String.format(html,item.get('id'),item.get('invoiceID'),item.get('item'),item.get('subtotal'));
    });
    return re;
}
var cm = [
    new Ext.grid.RowNumberer({header:"No.", width: 30}),
    {header: "ID", align: 'left',sortable:true, width: 40, dataIndex: 'id'},
    {header: "Invoice Date", align: 'left',sortable:true, width: 40, dataIndex: 'invoiceDate'},
    {header: "Total", align: 'right', width: 30, dataIndex: 'total', renderer: numrender},
    {header: "Invoice Items", align: 'left',sortable:false, id:'col_detail', width: 100, dataIndex: 'invoiceItems', renderer: invoicedetail}
];

var grid = new Ext.grid.GridPanel({
    id:'invoices',
    store: gstore,
    columns: cm,
    enableHdMenu: false,
    loadMask: {msg:'Loading Invoices ...'},
    enableColumnResize:false,
    stripeRows: true,
    viewConfig: { autoFill: true },
    columnLines : true,
    autoExpandColumn: 'col_detail',
    renderTo:'grid-wrapper'
});
gstore.load();

or you might be interested in looking at this treegrid:

http://www.max-bazhenov.com/dev/ux.maximgb.tg/index.php