Data source with nested child.

iamgenesisivan

Hi may I ask if this table structure
is posible with datatable?

My data is ready. If this would be posible, can you give me some tips on how to do this?

rf1234

I'd be very interested in this as well.
In terms of the data it is certainly possible with Data Tables: Simple parent - child relationship.
But I have no idea how to display it this way with Data Tables' client components.

rf1234

Regarding the data:
You can simply use an Mjoin to retrieve them: https://editor.datatables.net/manual/php/mjoin#Mjoin-class

Or if it is "read-only" and it is more complex than a simple parent child relationship you can use your own proprietary SQL in a getFormatter to retrieve what you need.

Here is an example for this:

This returns the same data format as an MJoin. Why not use an MJoin then? It can't do multiple joins and no inner joins etc. and I don't need to update the data anyway.

Field::inst( 'ctr_govdept.id AS gov' )->set( false )   //return same format as an MJoin             
    ->getFormatter( function($val, $data, $opts) {
        global $dbh;    
        $dbh->query('SELECT DISTINCT a.name AS govName, a.regional_12 AS govRegional12
                       FROM gov a
                 INNER JOIN ctr_installation_has_gov b          ON a.id = b.gov_id
                 INNER JOIN ctr_installation c                  ON b.ctr_installation_id = c.id
                 INNER JOIN ctr_govdept_has_ctr_installation d  ON c.id = d.ctr_installation_id
                      WHERE d.ctr_govdept_id = :ctrGovdeptId
                   ORDER BY 1 ASC');
        $dbh->bind(':ctrGovdeptId', $val);
        return $dbh->resultsetAssoc();
    }),

{   data: "gov", render: "[,<br>].govName"  },
{   data: "gov", render: "[,<br>].govRegional12"  }

colin

This thread should help, it's asking the same thing.

That said, @gyrocode 's link might help.

Cheers,

Colin

rf1234

@colin, this looks really difficult and complicated. So there is actually no real support for this on the front end side?

My thinking would be rather simple like this:
- it is pretty easy to retrieve all the child row data with an MJoin and the like
- Doing it that way you can easily render the child values in separate lines, but within one cell, not in multiple cells.
- The only thing that is missing are new grid lines after each child or - as in @iamgenesisivan 's example - different colors.

Can you think of a simple solution for this? In this example I would want a line after each child record looking like the Bootstrap 3 grid lines in the Data Table. And I would also want the newly separated cells to be "striped" and "bordered". I think this is achieved by applying classes "table table-striped table-bordered". Like the red lines I poorly drew here:

Sorry I don't like HTML and front end programming ... Hence I don't have the creativity to come up with a solution myself ... I always rely on the default functionality of Bootstrap 3 and Data Tables ...

This is my HTML:

<!--tblUser: Users-->
<table id="tblUser" class="table table-striped table-bordered"
       cellspacing="0" width="100%">
    <thead>
        <tr>
            <th>Name</th>
            <th><?php echo $en?('Language'):('Sprache');?></th>
            <th><?php echo $en?('Type'):('Typ');?></th>
            <th><?php echo $en?('Email'):('E-Mail');?></th>
            <th><?php echo $en?('Tooltips'):('Tooltips');?></th>
            <th><?php echo $en?('Layout'):('Layout');?></th>
            <th class="ttRoleFin"><?php echo $en?('User roles finance'):('Nutzer-Rollen Finanzierung');?></th>
        </tr>
    </thead>
</table>    

colin

So there is actually no real support for this on the front end side?

Yep, it's not currently supported. As you say it's complicated - we would've added it if it was easy

Colin

rf1234

Easier than I thought ...

This is the code for the example above with the red lines:

//  user financing roles
{   data: null,
    render: function ( data, type, row ) {
        var ix=0;
        var str = '';
        while (row.govdept[ix]) {
            if (ix !== 0) {
                str += '<br>';
            }
            str +=
                (row.govdept[ix].deptName + ': '
                 + renderRole(row.govdept[ix].userRole));
            ix++;       
        }
        return str;
    }
},

And here is my new "solution": Bootstrap 3 Table within a Data Table cell:

And the modified code. Not many changes actually:

//  user financing roles
{   data: null,
    render: function ( data, type, row ) {
        var ix=0;
        var str = '';
        while (row.govdept[ix]) {
            if (ix === 0) {
                str += '<table class="table table-striped table-bordered">'
            }
            str += '<tr><td>' +
                (row.govdept[ix].deptName + ': '
                 + renderRole(row.govdept[ix].userRole)) + '</td></tr>';
            ix++;       
        }
        if ( str > '' ) {
            str += '</table>';
        }
        return str;
    }
},

colin

It's the ordering and filtering that are the tricky bits - but yep, glad you got it going.

Colin