Two Pointers to same database table

Theo Baart

EDIT: Upon further research I have concluded that I might be able to solve it by using something similar to the following sql: LEFT JOIN harbor AS harborIn ON cargo.harborinID = harbor.harborID and LEFT JOIN harbor AS harborOut ON cargo.harboroutID = harbor.harborID. The question is now, how do I implement the were clause? In the following code: ->leftJoin( 'harbor', 'harboroutID', '=', 'harbor.harborID') where does the as statement go?

Thanks again.

---

For an application I am developing I have a mysql table (cargo) as follows:

harborinID and harboroutID are the harbors where the cargo is loaded/offloaded and they point to the table harbor:

My datatable is as follows:

As you might notice the harbor names are the same despite the fact that according to the mysql tables these should be different. However, if I click on it to edit it it does show the correct harbor:

My question is...is there any way to correctly link and display these two items or do I need to create a duplicate table?
My codes is as follows:

php output.

php code:

<?php


// DataTables PHP library
include( "../../DataTables-1.10.0/extensions/Editor-1.3.1/php/DataTables.php" );

// Alias Editor classes so they are easy to use
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Join,
    DataTables\Editor\Validate;

// Build our Editor instance and process the data coming from _POST
$data = Editor::inst( $db, 'cargo', 'cargoID' )
    ->fields(
                Field::inst( 'cargo.state'),
                Field::inst( 'cargo.shipID')->validator( 'Validate::notEmpty' ),
                Field::inst( 'cargo.harborinID')->validator( 'Validate::notEmpty' ),
                Field::inst( 'cargo.harboroutID')->validator( 'Validate::notEmpty' ),
                Field::inst( 'cargo.artikel' )->validator( 'Validate::notEmpty' ),
                Field::inst( 'cargo.description' )->validator( 'Validate::notEmpty' ),
                Field::inst( 'cargo.weight' )->validator( 'Validate::notEmpty' ),
                Field::inst( 'cargo.volume' )->validator( 'Validate::notEmpty' ),
                Field::inst( 'ship.ship'),
                Field::inst( 'harbor.harborName')
    )
        ->leftJoin( 'ship', 'cargo.shipID', '=', 'ship.shipID')
        //->leftJoin( 'harbor', 'harborinID', '=', 'harbor.harborID')
        ->leftJoin( 'harbor', 'harboroutID', '=', 'harbor.harborID')
    ->process( $_POST )
    ->data();

if ( ! isset($_POST['action'])){
    // get list of countries for select list
    $data['ship'] = $db
        ->selectDistinct( 'ship', 'shipID as value, ship as label')
        ->fetchAll();
    $data['in'] = $db
        ->selectDistinct( 'harbor', 'harborID as value, harborName as label')
        ->fetchAll();
    $data['out'] = $db
        ->selectDistinct( 'harbor', 'harborID as value, harborName as label')
        ->fetchAll();
}

echo json_encode($data);

You will notice I have commented out a left join, as that results in sql errors.

And the javascript is as follows

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

$(document).ready(function() {
    cargovar = new $.fn.dataTable.Editor( {
        ajax: "/ReWork/tables/cargoHandler.php",
        table: "#cargo",
        fields: [ {
                label: "State:",
                name: "cargo.state",
                type: "select",
                ipOpts: [
                    {label: "Active", value: "Active"},
                    {label: "Completed", value: "Completed"},
                    {label: "To Do", value: "To Do"}
                ]
            },{
                label: "Ship:",
                name: "cargo.shipID",
                type: "select"
            },{
                label: "Harbor (in):",
                name: "cargo.harborinID",
                type: "select"
            },{
                label: "Harbor (out):",
                name: "cargo.harboroutID",
                type: "select"
            },  {
                label: "Item:",
                name: "cargo.artikel"
            },{
                label: "Description:",
                name: "cargo.description"
            },{
                label: "Weight:",
                name: "cargo.weight"
            },{
                label: "Volume:",
                name: "cargo.volume"
            }
        ]
    } );
        
    $('#cargo').on( 'click', 'tbody td', function(e) {
        var index = $(this).index();
        // Each element is clarified as I keep getting to many "unable to determine field source" errors
        if (index === 1) {
            cargovar.bubble( this, ['cargo.state']);
        }
        if (index === 2) {
            cargovar.bubble( this, ['cargo.shipID']);
        }
        if (index === 3) {
            cargovar.bubble( this, ['cargo.harborinID']);
        }
        if (index === 4) {
            cargovar.bubble( this, ['cargo.harboroutID']);
        }
        if (index === 5) {
            cargovar.bubble( this, ['cargo.artikel']);
        }
        if (index === 6) {
            cargovar.bubble( this, ['cargo.description']);
        }
        if (index === 7) {
            cargovar.bubble( this, ['cargo.weight']);
        }
        if (index === 8) {
            cargovar.bubble( this, ['cargo.volume']);
        }
    })
    var cargo = $('#cargo').DataTable( {
        scrollX: true,
        scrollCollapse: true,
        /*paging: true,*/
        
        dom: "Tfrtip",
        ajax: "/ReWork/tables/cargoHandler.php",
        columns: [
            { data: null, defaultContent: '', orderable: false },
            { data: "cargo.state" },
            { data: "ship.ship" },
            { data: "harbor.harborName" },
            { data: "harbor.harborName" },
            { data: "cargo.artikel" },
            { data: "cargo.description" },
            { data: "cargo.weight" },
            { data: "cargo.volume" }
        ],
        order: [ 1, 'asc' ],
        tableTools: {
            sRowSelect: "os",
            sRowSelector: 'td:first-child',
            aButtons: [
                { sExtends: "editor_create", editor: cargovar },
                {
                    sExtends: "editor_edit",
                    sButtonClass: "editor_edit",
                    editor: cargovar,
                    formButtons: [
                    {
                        label: ">",
                        fn: function (e) {
                        this.submit( function () {
                            var tt = $.fn.dataTable.TableTools.fnGetInstance('cargo');
                            var row = tt.fnGetSelected()[0];
                            var rows = cargo.rows( {filter:'applied'} ).nodes();
                            var index = rows.indexOf( row );
         
                            tt.fnDeselect( row );
                            if ( rows[ index+1 ] ) {
                            tt.fnSelect( rows[index+1] );
                            $('a.editor_edit').click();
                            }
                        }, null, null, false );
                        }
                    },
                    {
                        label: "Save",
                        fn: function (e) {
                        this.submit();
                        }
                    },
                    {
                        label: "<",
                        fn: function (e) {
                        this.submit( function () {
                            var tt = $.fn.dataTable.TableTools.fnGetInstance('cargo');
                            var row = tt.fnGetSelected()[0];
                            var rows = cargo.rows( {filter:'applied'} ).nodes();
                            var index = rows.indexOf( row );
         
                            tt.fnDeselect( row );
                            if ( rows[index-1] ) {
                            tt.fnSelect( rows[index-1] );
                            $('a.editor_edit').click();
                            }
                        }, null, null, false );
                        }
                    }
                    ]
                },
                { sExtends: "editor_remove", editor: cargovar }
            ]
        },
        lengthMenu: [[5,10,15,25,50,-1],[5,10,15,25,50,"All"]],
        initComplete: function ( settings, json ) {
            // Populate select list with database info
            cargovar.field( 'cargo.shipID' ).update( json.ship);
            cargovar.field( 'cargo.harborinID' ).update( json.in);
            cargovar.field( 'cargo.harboroutID' ).update( json.out);
        }
                
    } );
} );

and this is a live example.
I appreciate any help and suggestions,

thanks,

Theo

allan

Hi Theo,

You can use as in the first parameter of the leftJoin() method. For example:

$editor->leftJoin( 'users as manager', 'users.manager', '=', 'manager.id' )

There is an example of that being used on the Editor site.

Regards,
Allan

Theo Baart

Thanks for the quick reply.
I have tried the following code

    
       ->leftJoin( 'harbor as in', 'cargo.harborinID', '=', 'in.harborID')

       ->leftJoin( 'harbor as out', 'cargo.harboroutID', '=', 'out.harborID')

However, I keep getting the following error

{"error":"SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'in ON `cargo`.`harborinID` = `in`.`harborID` LEFT JOIN `harbor` as out ON `ca' at line 1"

In mysql I can fix the error by placing backticks around the table name (i.e. `in` instead of in), but I'm not sure how to ensure this actually occurs in datatables.
Once again, thanks for your help,

Theo

allan

Yup - that looks like an error in the libraries which I'll look into. For the moment, just call your aliases something other than an SQL reserved word perhaps.

Allan

Theo Baart

I should've known that. Sorry about that. But it does work now. Thanks a lot!
Theo