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.

pfullenpfullen Posts: 11Questions: 2Answers: 0

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

This question has accepted answers - jump to:

Answers

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    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

  • pfullenpfullen Posts: 11Questions: 2Answers: 0

    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

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    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

  • pfullenpfullen Posts: 11Questions: 2Answers: 0
    edited 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');
            }
        } );
        
            
    } );
    
  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin
    Answer ✓

    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

  • pfullenpfullen Posts: 11Questions: 2Answers: 0
    edited 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

  • pfullenpfullen Posts: 11Questions: 2Answers: 0

    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!!!!!!

  • pfullenpfullen Posts: 11Questions: 2Answers: 0
    edited 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');
            }
        } );
    } );
    
  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin
    Answer ✓

    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

  • pfullenpfullen Posts: 11Questions: 2Answers: 0

    Awesome
    You Rock!!!!

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

  • pfullenpfullen Posts: 11Questions: 2Answers: 0

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

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

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    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

This discussion has been closed.