Do anyone know how to edit the ids-objects.php to include columns from multiple tables.

pfullen · August 2014

I am working with the sever side row_details.html.

I am trying to create a table that shows the sales ordres (customerName, soDate, notes)
The row details will pull from another table called lineItems.

I could just create another ajax because I am able to search by d.id
However I thought it would be best not duplicate code.

I thought I could have the first table show as d.id, d.customerName, d.soDate, d.notes)
Then have the second table show as i.product ,i.qty, i.price i.so_ID (Where i.so_ID = d.id)

Thanks a bunch for any help you can provide.

Peter

allan · August 2014

Hi Peter,

Are you doing this with Editor (which I see you purchased earlier today - thanks!)? If so, I would suggest ignoring the ids-objects.php example and use the Editor PHP libraries to do this as they has support for join tables built in, while the ids-objects.php file (and its supporting class) does not.

In particular, take a look at the join examples for Editor.

Indeed, even if you aren't using Editor in this case, you can use the Editor PHP class to still load the data using the leftJoin() method that is used in that example just to load the data. Simply don't provide any editing interface to stop editing!

You might also find the Editor join documentation useful.

If you have any questions about it, please do let me know.

Regards,
Allan

pfullen · August 2014

Thanks

I am able to load and use the editor join array. I think I sent and support email just after I purchased the editor.

I would like to be able to add row details to the joinarray.html

For example I want to have a sales order - The parent row shows basic information such as order# customer, notes, user. Then when the row is expanded it shows line item details from another table

For example

Sales Order # Customer Notes User
60 Grady Blah blah blah John
+ Product QTY Price
Shirt 10 25.95
Pants 5 12.95
Glove 4 8.95
Hat 10 15.95
61 Kennestone Blah blah Blah Mark
62 Piedmont Blah blah blah Steve
Etc Etc

Thanks

Peter

allan · August 2014

Hi Peter,

Sounds good. Do you have a join table set up and working just now? That would be the first step I would suggest, and ignore the child rows in the first instance as they can easily be added once the basic table is up and running.

Once it is, the child row code from the row details example and be used (specifically lines 38 - 53 for the action, and also 24 to 29 to show the details control - in the Javascript block shown below the table).

Regards,
Allan

pfullen · August 2014

Thanks for all your help

I have the join table set and working.
However when I add the lines 38 - 53 and 24-29 The data no longer appears.
When I inspect in the console.log the error message is :
Uncaught TypeError: Cannot read property 'style' of undefined - jquery.datatables.js 4061

I noticed in the rowdetails examples that on line 23 the columns is in quotes "columns"
However in the joinArray.html there are no quotes. I was not sure if this was the problem I am having.
Here is the code I have

Thanks again for all your help

$(document).ready(function() {
    editor = new $.fn.dataTable.Editor( {
        ajax: "../php/joinArray.php",
        table: "#example",
        fields: [ {
                label: "First name:",
                name:  "users.first_name"
            }, {
                label: "Last name:",
                name:  "users.last_name"
            }, {
                label: "Site:",
                name:  "users.site",
                type:  "select"
            }, {
                "label": "Access:",
                "name": "access[].id",
                "type": "checkbox"
            }
        ]
    } );

    $('#example').dataTable( {
        dom: "Tfrtip",
        ajax: {
            url: "../php/joinArray.php",
            type: 'POST'
        },
        columns: [
        
            {
                "class":          'details-control',
                "orderable":      false,
                "data":           null,
                "defaultContent": ''
            },
            { data: "users.first_name" },
            { data: "users.last_name" },
            { data: "sites.name" },
            { data: "access", render: "[, ].name" }
        ],
        tableTools: {
            sRowSelect: "os",
            aButtons: [
                { sExtends: "editor_create", editor: editor },
                { sExtends: "editor_edit",   editor: editor },
                { sExtends: "editor_remove", editor: editor }
            ]
        },
        initComplete: function ( settings, json ) {
            editor.field( 'users.site' ).update( json.sites );
            editor.field( 'access[].id' ).update( json.access );
        }
    } );
    
    // Add event listener for opening and closing details
    $('#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
            row.child.hide();
            tr.removeClass('shown');
        }
        else {
            // Open this row
            row.child( format(row.data()) ).show();
            tr.addClass('shown');
        }
    } );
    
        
} );

allan · August 2014

I noticed in the rowdetails examples that on line 23 the columns is in quotes "columns" However in the joinArray.html there are no quotes.

Shouldn't make any difference in this case. The quotes are optional in Javascript (required in JSON).

In the code you have 5 columns defined in the columns array. Do you also have 5 columns in the HTML? The error you are getting makes me suspect that there are only 4.

The HTML and the Javascript must agree on the number of columns, otherwise errors such as this can occur.

Regards,
Allan

pfullen · August 2014

Sorry I a little confused.
Is the

      {
            "class":          'details-control',
            "orderable":      false,
            "data":           null,
            "defaultContent": ''
        },

counting as a column

I then have 4 columns

        { data: "users.first_name" },
        { data: "users.last_name" },
        { data: "sites.name" },
        { data: "access", render: "[, ].name" }

My HTML code is as follows

<table id="example" class="display" cellspacing="0" width="100%">
                <thead>
                    <tr>
                        <th>First name</th>
                        <th>Last name</th>
                        <th>Location</th>
                        <th>Access</th>
                    </tr>
                </thead>

                <tfoot>
                    <tr>
                        <th>First name</th>
                        <th>Last name</th>
                        <th>Location</th>
                        <th>Access</th>
                    </tr>
                </tfoot>
            </table>

Thanks

Peter

pfullen · August 2014

I was able to add <th> </th> and get the additional column problem fixed.
However it is not showing the expand button + and -

Thanks I really appreciate all this help!!!!!!

pfullen · August 2014

I was able to add the buttons so I have that all fixed now.
Thanks for your help

I am trying to add the table tools now for Copy CVS Print PDF

I added :

dom:  'T<"clear">lfrtip',

and

tableTools: {
  "sSwfPath": "copy_csv_xls_pdf.swf",

I copied the filed to the same directory

Here is my code. I can not figure out what I am doing wrong
Thanks

var editor; // use a global for the submit and return data rendering in the examples

$(document).ready(function() {
    editor = new $.fn.dataTable.Editor( {
        ajax: "../php/joinLinkTable.php",
        table: "#example",
        fields: [ {
                label: "First name:",
                name:  "users.first_name"
            }, {
                label: "Last name:",
                name:  "users.last_name"
            }, {
                label: "Site:",
                name:  "users.site",
                type:  "select"
            }, {
                label: "Department:",
                name:  "user_dept.dept_id",
                type:  "select"
            }
        ]
    } );
    
    
    var table = $('#example').DataTable( {
            dom:  'T<"clear">lfrtip',
        "ajax": {
            url: "../php/joinLinkTable.php",
            type: 'POST'
        },
        
        "columns": [
            {
                "class":          'details-control',
                "orderable":      false,
                "data":           null,
                "defaultContent": ''
            },
            
            { data: "users.first_name" },
            { data: "users.last_name" },
            { data: "sites.name" },
            { data: "dept.name" }
        ],
        "order": [[0, 'asc']],
        tableTools: {
            "sSwfPath": "copy_csv_xls_pdf.swf",
            sRowSelect: "os",
            aButtons: [
                { sExtends: "editor_create", editor: editor },
                { sExtends: "editor_edit",   editor: editor },
                { sExtends: "editor_remove", editor: editor }
            ]
        },
        initComplete: function ( settings, json ) {
            editor.field( 'users.site' ).update( json.sites );
            editor.field( 'user_dept.dept_id' ).update( json.dept );
        }
    } );
    
    // Add event listener for opening and closing details
    $('#example tbody').on('click', 'td.details-control', function () {
       alert('you clicked expand');
        var tr = $(this).closest('tr');
        var row = table.row( tr );

        if ( row.child.isShown() ) {
            // This row is already open - close it
            row.child.hide();
            tr.removeClass('shown');
        }
        else {
            // Open this row
            row.child( format(row.data()) ).show();
            tr.addClass('shown');
        }
    } );
} );

allan · August 2014

Hi Peter,

Sorry I a little confused. Is the { "class": 'details-control', "orderable": false, "data": null, "defaultContent": '' }, counting as a column

Yes - each entry in the columns array is a column.

I was able to add the buttons so I have that all fixed now. Thanks for your help

Excellent to hear :-)

I am trying to add the table tools now for Copy CVS Print PDF

In your DataTables initialisation you are using the aButtons array of buttons to define the TableTools buttons for Editor. If you would like the export buttons to appear also they need to be added to the array as well.

For example you might end up with:

            aButtons: [
                { sExtends: "editor_create", editor: editor },
                { sExtends: "editor_edit",   editor: editor },
                { sExtends: "editor_remove", editor: editor },
                'copy',
                'xls',
                'csv',
                'pdf',
                'print'
            ]

Regards,
Allan

pfullen · August 2014

Awesome
You Rock!!!!

This plugin is super helpful. Thanks for all the work you have done on this!!!!!!!!!!!!

pfullen · August 2014

Ok I have the COPY, XLS, CVS, PDF and print working.

Where do I edit to get those buttons to float right?

allan · August 2014

Good to hear that worked!

You can use a little CSS to move the buttons around. In this Editor example I use a little margin to separate the buttons:

a.save-collection {
        margin-left: 1em;
}

You could also add float:right options and any other CSS required to the buttons to get the layout required. I would suggest using the browser's "Inspect element" option to take a look at the HTML for the buttons so you can see the structure and target the buttons you want with the required CSS.

Regards,
Allan

Do anyone know how to edit the ids-objects.php to include columns from multiple tables.

Do anyone know how to edit the ids-objects.php to include columns from multiple tables.

Answers

Howdy, Stranger!

Categories

DataTables

Do anyone know how to edit the ids-objects.php to include columns from multiple tables.

Do anyone know how to edit the ids-objects.php to include columns from multiple tables.

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables