php left join editor issue

gforster

http://debug.datatables.net/alejok

Everything is functioning fine on the DataTables display side.

I am not getting any options shown with the selector in the Editor field

The Join I'm attempting to do is the MySQL equivalent of (rather similar to the example):

    SELECT 
        SERVERS.hostname, LOCATION.name AS location
    FROM
        idm_assets.SERVERS
            LEFT JOIN
        idm_assets.LOCATION ON SERVERS.location_id = LOCATION.id;

The LOCATION table only has two columns (id, name) and, as of now, only two rows. I want the editor to update the SERVERS.location_id field of a given row with the correct value from the LOCATION.id (a 1 or a 2) but show the options from the corresponding LOCATION.name field.

my table.php:

  // DataTables PHP library
  include( "php/DataTables.php" );

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

  // Build our Editor instance and process the data coming from _POST
  Editor::inst( $db, 'SERVERS' )
      ->fields(
          Field::inst( 'SERVERS.id' ),
          Field::inst( 'SERVERS.hostname' ),
          Field::inst( 'SERVERS.dev_level' ),
          Field::inst( 'SERVERS.os' ),
          Field::inst( 'SERVERS.location_id' )
              ->options( Options::inst()
                  ->table( 'LOCATION' )
                  ->value( 'id' )
                  ->label( 'name' )
              )
              ->validator( 'Validate::dbValues' ),
          Field::inst( 'LOCATION.name' ),
          Field::inst( 'SERVERS.description' )
      )
      ->leftJoin( 'LOCATION', 'SERVERS.location_id', '=', 'LOCATION.id' )
      ->process( $_POST )
      ->json();

And the javascript:

 var editor; 

     $(document).ready(function() {
         editor = new $.fn.dataTable.Editor( {
             ajax: "test_table.php",
             table: "#example",
             fields: [ {
                     label: "Hostname",
                     name: "SERVERS.hostname"
                 }, {
                     label: "dev_level",
                     name: "SERVERS.dev_level"
                 }, {
                     label: "OS:",
                     name: "SERVERS.os"
                 }, {
                     label: "Location:",
                     name: "LOCATION.name",
                     type: "select",
                     placeholder: "Select A Location"
                 }, {
                     label: "description",
                     name: "SERVERS.description"
                 }
             ]
         } );
         $('#example').DataTable( {
             dom: "Bfrtip",
                 ajax: {
                     url: "test_table.php",
                     type: 'POST'
                 },

             columns: [
                 { data: "SERVERS.hostname" },
                 { data: "SERVERS.dev_level" },
                 { data: "SERVERS.os"},
                 { data: "LOCATION.name"},
                 { data: "SERVERS.description" }
             ],
             select: true,
             buttons: [
                 { extend: "create", editor: editor },
                 { extend: "edit",   editor: editor },
                 { extend: "remove", editor: editor }
             ]
         } );
     } );

gforster

FIXED

I was referencing the wrong table.column in the javascript.

Instead of:

  label: "Location:",
  name: "LOCATION.name",
  type: "select",
  placeholder: "Select A Location"

It needed to be:

  label: "Location:",
  name: "SERVERS.location_id",
  type: "select",
  placeholder: "Select A Location"

Thanks!

allan

Thanks for posting back. You are correct - you need to set the Editor field to the field you want to change the value of, rather than to that of the label.

Regards,
Allan