Dynamic data in a child row using one-to-many

dbloor

Hi all,

So I just got my child row to work and it's fantastic! I even added the slider effect. I'm loving it.

So my challenge now is... I was trying out the one-to-many feature as explained here:
https://editor.datatables.net/examples/advanced/joinArray.html
I got that working, but I'd like to have each "access" or in my case "contact" to appear in the child row, but I can't figure out how.

I just need to know how to place
{ data: "contacts", render: "[, ].cname" }
into the javascript for
'<td>'+d.table.column+'</td>'+

I have the following:

server-side

<?php

// DataTables PHP library
include("../Editor-PHP-1.4.2/php/DataTables.php");


use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Join,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate;

Editor::inst( $db, 'brand' )
    ->field(
        Field::inst( 'brand.name' ),
        Field::inst( 'brand.address' ),
        Field::inst( 'brand.city' ),
        Field::inst( 'brand.state' ),
        Field::inst( 'brand.country' )
    )

    ->join(
        Join::inst( 'contacts', 'array' )
            ->join(
                array( 'id', 'brand_id' ),
                array( 'id', 'contact_id' ),
                'brand_to_contacts'
            )
            ->fields(
                Field::inst( 'id' )->validator( 'Validate::required' )
                ->options( 'contacts', 'id', 'cname'),
                Field::inst( 'cname' )
            )
    )
    ->process($_POST)
    ->json();

javascript for the child function

    function format ( d ) {
        // `d` is the original data object for the row
        return '<div class="slider">'+ '<table cellpadding="5" cellspacing="0" border="0" style="padding-left:50px;">'+
            '<tr>'+
            '<td>Contact Name:</td>'+
            '<td>'+d.???.????+'</td>'+
            '</tr>'+
            '</table>'+
        '</div>';
    }

dbloor

Okay so I'm kinda close... using
'<td>'+d.contacts+'</td>'+
I can get the child to show:
Contact Name: [object Object],[object Object]

This means it's pulling 2 objects that are joined to that parent. Great. Now to render the objects to the contacts.cname

dbloor

Okay I got it... Using a loop in javascript, I was able to get the table to list each child that is linked to each parent. So I have a "Comment" section for each brand. These comment sections usually contain some detailed notes about the brand that we need to be aware of. Next is the actual contacts. Contact type is a designation of Primary or Secondary.

I'm very new to javascript so I apologize if this is a bit messy!
Here is my code:

 function format(d) {
            var values = [];
            var comment_section = ('<div class="slider">' + '<table cellpadding="5" cellspacing="0" border="0" frame="box">' +
            '<tr>' +
            '<th>Comments</th>' +
            '<td>' + d.brand.comments + '</td>' +            '</tr>' +
            '</table>' +
            '</div>')

            for (i = 0; i < +d.contacts.length; i++) {
                values.push('<div class="slider">' + '<table cellpadding="5" cellspacing="0" border="0" frame="box" >' +

                '<tr class="two">' +
                '<th style="width: 100px">Contact Type</th>' +
                '<th>Name</th>' +
                '<th>Email</th>' +
                '<th>Title</th>' +
                '<th>Phone</th>' +
                '<th>Mobile</th>' +
                '<th>Fax</th>' +

                '</tr>' +
                '<tr>' +
                '<td>' + d.contacts[i].ctype + '</td>' +
                '<td>' + d.contacts[i].cname + '</td>' +
                '<td>' + d.contacts[i].cemail + '</td>' +
                '<td>' + d.contacts[i].ctitle + '</td>' +
                '<td>' + d.contacts[i].cphone + '</td>' +
                '<td>' + d.contacts[i].cmobile + '</td>' +
                '<td>' + d.contacts[i].cfax + '</td>' +
                '</tr>' +
                '</table>' +
                '</div>')


            }
            return comment_section + values.join('');
        }

        var editor; // use a global for the submit and return data rendering in the examples
        $(document).ready(function () {...

It is then called with:

            $('#example tbody').on('click', 'td.details-control', function () {
                var tr = $(this).closest('tr');
                var row = table.row(tr);

                if (row.child.isShown()) {
                    // This row is already open - close it
                    $('div.slider', row.child()).slideUp(function () {
                        row.child.hide();
                        tr.removeClass('shown');
                    });
                }
                else {
                    // Open this row
                    row.child(format(row.data()), 'no-padding').show();
                    tr.addClass('shown');

                    $('div.slider', row.child()).slideDown();
                }
            });

This uses the child slider rows found here: https://www.datatables.net/blog/2014-10-02

Now I have to adjust the editor form to call each child to allow me to edit them. this has been giving me even more trouble.

Any advice would be greatly appreciated! The Contacts live on another table and can be one or more. Lets say maximum of 2 to make it simple. Any for loop voodoo someone can help me with insider the editor?

Thanks!

allan

Hi,

Sorry I missed this thread - nice internal monolog :-)

You are absolutely correct - the way to display the many data points is to loop over the array containing that data and output it as such.

Now I have to adjust the editor form to call each child to allow me to edit them. this has been giving me even more trouble.

As in you want to edit each of the many items individually? Interesting question.

You have two options:

1. Create another DataTable for your inner table and use Editor as normal for the inner table - the inline controls example might be of interest if you don't want another row of TableTools buttons for each inner table.
2. If you don't want an inner DataTable, create an Editor instance for the joined table and have it operating in standalone mode (examples)

I suspect option 1 will be must easier!

The key is going to be creating the inner DataTable - you want to do that once the HTML for the table has been inserted into the document - immediately after the row.chlid().show() function has been called for example.

Regards,
Allan

dbloor

Thank you for your response Allan.
And I hope the internal monologue is helpful for someone in the future!

I've decided to go even simpler for now. I've added the following to the last item in the editor:
fieldInfo: "</div></div><div class='custommsg'>To add a contact to the brand go to the Brand Contacts page </div>"
The class custommsg has some CSS that makes the text red, bold and centred.

That page then lists the Brand name in the first column and then contact details for one contact. The user can create a new contact here, and select the brand name that they wish to associate the contact to. I feel this is acceptable since the contact information doesn't change very often.

Maybe alter I will look to incorporate your suggestions :)

Thanks again!

dbloor

I'd hate to dig this back up again... But I have come across a problem with my solution.

The MySQL table for contacts has the following columns: id, brandid, type, name, phone and a few more... type has a value of "Primary, Secondary or Emergency" (for now). Originally those were not normalized. Each row had a written "Primary" value ( or one of the others)

This was a problem because when creating a new contact, I could not get the "select" field type to show only unique values. It would list 150 Primary, 75 Secondary and 3 Emergency. So instead of fighting it, I made a "contact_types" table that contained "id, type" and had 3 rows, 1-Primary, 2-Secondary, 3-Emergency.

I am able to join the tables in my contacts page no problem, but now my wonderful looped parent child relationship is broken. type is shown as 1,2,3. I can't figure out how to join within my loop.

Would it be easier to return my "contact type" back to the way it was? If so, is there a way to make the "select" field type show only unique values? Sorry if this is covered somewhere. I can't seem to find it.

Thanks!

allan

Two options:

1. Add leftJoin to join to the contact_types table and get the label information for each option.
2. Revert to the old style and use unique().

Since you have the extra db table setup already, I would suggest option 1 is the way to go. Get the extra information from the database and display it.

Allan

dbloor

Thanks again for the reply. I'm still struggling combining my join array with a leftJoin... Here's what I have:

Editor::inst($db, 'brand')
    ->field(
        Field::inst('brand.brandname'),
        Field::inst('brand.address'),
        Field::inst('brand.city'),
        Field::inst('brand.state'),
        Field::inst('brand.country'),
        Field::inst('brand.comments')
    )

    ->join(
        Join::inst('contacts', 'array')
            ->join(
                array('id', 'brand_id'),
                array('id', 'contact_id'),
                'brand_to_contacts'
            )
            ->fields(
                Field::inst('id')->validator('Validate::required')
                    ->options('contacts', 'id', 'cname'),
                Field::inst('cname')

            )
            ->fields(
                Field::inst('id')->validator('Validate::required')
                    ->options('contacts', 'id', 'cemail'),
                Field::inst('cemail')
            )
            ->fields(
                Field::inst('id')->validator('Validate::required')
                    ->options('contacts', 'id', 'ctype'),
                Field::inst('ctype')
            )
            ->fields(
                Field::inst('id')->validator('Validate::required')
                    ->options('contacts', 'id', 'cphone'),
                Field::inst('cphone')
            )
            ->fields(
                Field::inst('id')->validator('Validate::required')
                    ->options('contacts', 'id', 'cmobile'),
                Field::inst('cmobile')
            )
            ->fields(
                Field::inst('id')->validator('Validate::required')
                    ->options('contacts', 'id', 'cfax'),
                Field::inst('cfax')
            )
            ->fields(
                Field::inst('id')->validator('Validate::required')
                    ->options('contacts', 'id', 'ctitle'),
                Field::inst('ctitle')
            )
    )


    ->process($_POST)
    ->json();

Any help is greatly appreciated :)