One Datatable with two leftJoins to the same "lookup" table

rclayton

Greetings all;

I cannot figure out how to alias two references to the same lookup table and get the leftJoins to work out.

Has anyone successfully done this?

table1.field1 fk lkuptable1.id
table1.field3 fk lkuptable1.id

in plain ol' sql I would write:

select lk1.name as Field1code,
          t.field2,
          lk2.name as Field3code
from   table1 t, 
          lkuptable1 lk1,
          lkuptable1 lk2
where lk1.id = t.field1 and
          lk2.id = t.field3

I guess a more streamlined question would be "is there a way to alias a table using the leftJoin method?"

Consider the Server Side Code:

// Build our Editor instance and process the data coming from _POST

$data = Editor::inst( $db, 'FSDB_SONDE_CALIBRATION_LOG', 'calibration_id' )
->field(
// clip out other stuff
Field::inst( 'FSDB_SONDE_CALIBRATION_LOG.inical_personnel' ),
Field::inst( 'FSDB_PERSONNEL_VW1.name'),
// clip out other stuff
Field::inst( 'FSDB_SONDE_CALIBRATION_LOG.postcal_personnel' ),
Field::inst( 'FSDB_PERSONNEL_VW2.name'),
// clip out other stuff
)

->leftJoin('FSDB_PERSONNEL_VW2','personnel_no','=','FSDB_SONDE_CALIBRATION_LOG.inical_personnel' )
// I get that the first parameter is wanting a table name ... so where would I reference an Alias?
->leftJoin('FSDB_PERSONNEL_VW1','personnel_no','=','FSDB_SONDE_CALIBRATION_LOG.postcal_personnel' )
// 
->process( $_POST )
->data();

if ( ! isset($_POST['action']) ) {
// Get a list of personnel for the `inical personnel select` list
$data['FSDB_PERSONNEL_VW1'] = $db
        ->selectDistinct( 'FSDB_PERSONNEL_VW', 'personnel_no as value, name as label' )
        ->fetchAll();
// Get a list of personnel for the `postcal personnel select` list
$data['FSDB_PERSONNEL_VW2'] = $db
        ->selectDistinct( 'FSDB_PERSONNEL_VW', 'personnel_no as value, name as label' )
        ->fetchAll();
}

And the output:

{
    "error": "SQLSTATE[42S02]: Base table or view not found: 1146 Table 'atxdatabaseb.FSDB_PERSONNEL_VW2' doesn't exist",
    "data": [],
    "FSDB_PERSONNEL_VW1": [
        {
            "value": "20",
            "label": "Query Acct"
        },
        {
            "value": "66",
            "label": "Frank Black"
        },
        {
            "value": "22",
            "label": "Phil Collins"
        },
        {
            "value": "1",
            "label": "Brian Eno"
        }
    ],
    "FSDB_PERSONNEL_VW2": [
        {
            "value": "20",
            "label": "Query Acct"
        },
        {
            "value": "66",
            "label": "Frank Black"
        },
        {
            "value": "22",
            "label": "Phil Collins"
        },
        {
            "value": "1",
            "label": "Brian Eno"
        }
    ],

}

Thanks in advance.

Rob Clayton

rclayton

I guess I could make two views in Mysql of the same table ... so I have a workaround for now, but I am curious if it is possible using left-join. Thanks.

allan

is there a way to alias a table using the leftJoin method?

Yes :-). The table name can use an SQL as to alias it and Editor will recognise and use that. For example:

->leftJoin('FSDB_PERSONNEL_VW1 as vw1','personnel_no','=','FSDB_SONDE_CALIBRATION_LG.postcal_personnel' )

Then you can use vw1 (or whatever else you want to alias it as).

There is an example of that in action here: https://editor.datatables.net/examples/advanced/joinSelf.html (click the "Server side" tab to see the PHP used).

Regards,
Allan

rclayton

I really "hate it" when the answer is in the Manual. :)

Thanks for the direction and great work with this product!

Rob