Join tables - link table - combined label render

Allcor

Hey, love to work with DataTables editor. But I didn't figure out how to do the following.

I have a linked table like the following:

collection
   \_ origin_id
         \_ synonym
          _ iso3166-id
               \_ name

so thanks to this example the table shows the official name of the country whatever synonym has been filled in.

<?php
Editor::inst( $db, 'collection', 'id' )
    ->field(
        Field::inst( 'collection.origin_id' )
            ->options( Options::inst()
                ->table( 'origin' )
                ->value( 'id' )
                ->label( array('synonym', 'iso3166-id') )
                ->render( function ( $row ) {
                    return $row['iso3166-id'].' - ('.$row['synonym'].')';
                } )
            ),
        Field::inst( 'iso3166.name' )
    )
    ->leftJoin( 'origin',        'collection.origin_id',       '=', 'origin.id')
    ->leftJoin( 'iso3166',    'origin.iso3166_id', '=', 'iso3166.id')

I will leave the .js but fields show the iso-name and the editField put on the 'select' has all the synonyms. So far so good.

Now my question:
How can I have the labels of the select show the name value from the linked table? iso3166.name where 'iso3166-id is now.

allan

Your Editor fields.name option for that field should be collection.origin_id - is it?

Allan

Allcor

yes, it is, this works perfectly.

It's the labels in the drop-down I can't add the linked value to.

Allcor

Will add some of the javascript. Removed lot of the stuff I believe is not relevant, hope I didn't remove too much.

$(document).ready(function () {

    editor = new $.fn.dataTable.Editor( {
        ajax: {
            url: "/../../../isolates.php",
            dataSrc: 'data'
        },
        table: "#collectionTable",
        fields: [
            {
                label: "Origin of sample:",
                name: "collection.origin_id",
                type: 'select',
                placeholderDisabled: false,
                placeholder: ''
            }
        ]
    });

    $('#collectionTable').on( 'click', 'tbody td', function (e) {
        editor.inline( this );
    } );

    var table = $('#collectionTable').DataTable( {
        "ajax": {
            url: "/../../../isolates.php",
            dataSrc: 'data'
        },
        columns: [
            { data:'iso3166.name', editField: 'collection.origin_id'}
        ],
    });
});

Allcor

Did a bad job explaining what the actual question is. Hope this clears things up

allan

Oh I see - sorry. You want the label to include information from two different tables. I'm afraid that at the moment the built in Options class doesn't have that ability. You'd need to use a custom function to query both tables.

Allan

Allcor

That could work! I now realize it defines a separate query. Should have the right SQL query, could you please help calling it?

found this https://datatables.net/forums/discussion/31952/raw-sql-query-for-editor but doesn't seem to work

SELECT DISTINCT collectie_origin_pathogen.id AS value, CONCAT(collectie_countrycode.name, ' - (', collectie_origin_pathogen.given_name, ')') AS label
FROM collectie_origin_pathogen
INNER JOIN collectie_countrycode on collectie_origin_pathogen.country_id=collectie_countrycode.id
WHERE is_choice=true

Allcor

I'm a bit new to PHP, how do I use the $db from within DataTables\Editor\Field\options?

allan

->options( function () {
  global $db;
  return $db->sql( 'SELECT ....' )->fetchAll();
} );

Allan

Allcor

you're a hero Allan!