How do you get row reordering and a select in editor requiring a joined table to work together?

koniahin

I've gone round and round with this trying different things unsuccessfully.

The goal is to use a secondary table to create a select list in the editor (New) while at the same time row ordering is working. I can get either to work separately but not harmoniously together.

The main table in menu_items(2). In the server side php file we have:

/* PUBLISH */
Field::inst( 'menu_items2.publish' )
  ->options( Options::inst()
  ->table( 'publish_options' )
  ->value( 'publish' )
  ->label( 'publish' )
  )
  ->validator( Validate::dbValues() ),
Field::inst( 'publish_options.publish' ),
/* PUBLISH */

Field::inst( 'menu_items2.rowOrder' )->validator( 'Validate::numeric' )

with some regular fields. Then a left join with standard row ordering processing:

/* JOIN /
->leftJoin( 'publish_options', 'publish_options.publish_id', '=', 'menu_items2.id' )
/ JOIN */

In the datatable page file I have:

fields: [ {
    label: 'Order:',
    name: 'menu_items2.rowOrder',
    fieldInfo: 'This field can only be edited via click and drag.'
  }, {
    label: 'Title:',
    name: 'menu_items2.title'
  }, {
    label: 'Publish:',
    name: 'menu_items2.publish',
    type: 'select'
  }, {
  some other fields ...
  }

Columns:

columns: [
  { data: 'menu_items2.rowOrder', className: 'reorder' },
  { data: 'menu_items2.id' },
  { data: 'menu_items2.title' },
  { data: 'menu_items2.publish' },
  { data: 'menu_items2.access' },
  { data: 'menu_items2.alias' },
  { data: 'menu_items2.menu' },
  { data: null, render: function ( data, type, row ) {
    return '<a href="update-webpage.php?article_id='+data.id+'"><span class="fa fa-edit">update</span></a>';
    }
  }

And:

rowReorder: {
  dataSrc: 'menu_items2.rowOrder',
  editor: editor
},

and:

editor
.on( 'postCreate postRemove', function () {
table.ajax.reload( null, false );
} )
.on( 'initCreate', function () {
editor.field( 'menu_items2.rowOrder' ).enable();
} )
.on( 'initEdit', function () {
editor.field( 'menu_items2.rowOrder' ).disable();
} );

Row ordering works but inconsistently. The other thing I see with row reordering is that rather than create a natural sequence like 0 1 2 3 4 5 6 7 8 9 ... it swaps out numbers, ids. So if you've either deleted an item or added an item with an odd order number you can end up with gaps in the sequence.

See a live example with a demo table at: http://www.dottedi.biz/demo/code/public/dt/index.php

allan

The goal is to use a secondary table to create a select list in the editor (New) while at the same time row ordering is working.

I don't quite understand what you mean I'm afraid. Do you want a DataTable inside the Editor modal, which itself is editable? At the moment I'm afraid that isn't possible as only one Editor can be active at a time.

So if you've either deleted an item or added an item with an odd order number you can end up with gaps in the sequence.

Yes, you need to update the sequence in the database based on the item(s) that were removed. In the example if you click the "Server script" tab below the table you'll be able to see the preCreate and preRemove event that I used for this.

Allan