Need help with drag & drop rowreordering with compound key and data from multiple tables

Need help with drag & drop rowreordering with compound key and data from multiple tables

mokozukimokozuki Posts: 13Questions: 3Answers: 0
edited April 2018 in Free community support

I am trying to get this working (have php experience (precedural), javascript not so much). I have data in a table which i want to be able to reorder. This table has information for multiple "lists", so i use a compound key (list_id+item_order) to select and display the items in this list. while showing this list, i also want to left join another table to get the "item names" (name_external) in the datatable (which should not be editable).
So far, i have gotten selecting and displaying the data working, but I can't create and delete rows. drag and drop reordering works until after dropping, the order will jump back to it's original order afterwards. It seems the ajax call works, but reordering does not, so it returns the original order.
I have set ->debug(true) in the server side script, but don't know how to output the log.

The user should only be able to reorder by drag and drop, create new and delete rows. when creating, the only needed data should be item_order and product_id. Most important is to get drag and drop reordering working.

Below is my js and server-side php, any help or nudge in the right direction would be greatly appreciated:

javascript

var editor;

$(document).ready(function() {

    editor = new $.fn.dataTable.Editor({
    ajax: '/ax/tableProductList.php?list_id='+$('#list-id').html(),
    table: '#recordsTable',
    fields: [
        { label: 'Order', name: 'table1.item_order' },
        { label: 'List', name: 'table1.product_list_id' },
        { label: 'Name', name: 'table2.name_external' },
        { label: 'Item #', name: 'table1.product_id' },
        { label: 'Last Update', name: 'table1.last_update' },
        { label: 'Created By', name: 'table1.created_by' }
    ]
    });

    var table = $('#recordsTable').DataTable({
    ajax: '/ax/tableProductList.php?list_id='+$('#list-id').html(),
    dom: "<'row'<'col w-50'B><'col w-50 text-right'fr>><'row'<'col't>>",
    serverSide: false,
    paging: false,
    Filter: true,
    bSort: true,
    columnDefs: [
        { orderable: true, className: 'reorder', targets: 0 },
        { orderable: false, targets: '_all' }
    ],
    bFilter: true,
    bServerSide: false,
    sAjaxSource: false,
    ordering: true,
    orderMulti: false,
    searching: false,
    select: true,
    columns: [
        { data: 'table1.item_order' },
        { data: 'table1.product_list_id' },
        { data: 'table2.name_external' },
        { data: 'table1.product_id' },
        { data: 'table1.last_update' },
        { data: 'table1.created_by' },
    ],
    rowReorder: {
        dataSrc: 'table1.item_order',
        editor: editor
    },
    buttons: [
        { extend: 'create', editor: editor },
        { extend: 'remove', editor: editor }
    ],
    idSrc: 'table1.item_order'
    })

    editor
    .on('postCreate postRemove', function() {
        table.ajax.reload(null,false);
    })
    .on('initCreate', function() {
        editor.field('table1.item_order').enable();
        editor.field('table1.created_by').disable();
        editor.field('table1.product_list_id').disable();
        editor.field('table1.last_update').disable();
        editor.field('table2.name_external').disable();
    })
    .on('initEdit', function() {
        editor.field('table1.item_order').disable();
    })


});

server-side script

<?php
 
/*
 * Example PHP implementation used for the index.html example
 */
 
// DataTables PHP library
include( "../../app/includes/Editor/DataTables.php" );

$table1 = "mz_product_list_items_bak";
$table2 = "mz_getProducts_dyfs";
 
// 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, "$table1 as table1", array('product_list_item_id','product_list_id'))
    ->debug(true)
    ->fields(
        Field::inst( 'table1.product_list_item_id' )->validator( 'Validate::numeric' ),
        Field::inst( 'table1.item_order' )->validator( 'Validate::numeric' ),
        Field::inst( 'table1.product_id' )->validator( 'Validate::numeric' ),
        Field::inst( 'table1.product_list_id' )->validator( 'Validate::numeric' ),
        Field::inst( 'table1.created_by' )->validator( 'Validate::notEmpty' ),
        Field::inst( 'table1.last_update' )->validator( 'Validate::notEmpty' ),
        Field::inst( 'table2.name_external' )->validator( 'Validate::notEmpty' )
    )
    ->where('product_list_id', $_GET['list_id'], '=')
    ->leftJoin("$table2 as table2", 'table1.product_id', '=', 'table2.sku')
    ->on( 'preCreate', function ( $editor, $values ) {
        // On create update all the other records to make room for our new one
        $editor->db()
            ->query( 'update', $table1 )
            ->set( 'table1.item_order', 'table1.item_order+1', false )
            ->where( 'table1.item_order', $values['table1.item_order'], '>=' )
            ->exec();
    } )
    ->on( 'preRemove', function ( $editor, $id, $values ) {
        // On remove, the sequence needs to be updated to decrement all rows
        // beyond the deleted row. Get the current reading order by id (don't
        // use the submitted value in case of a multi-row delete).
        $order = $editor->db()
            ->select( $table1, 'table1.item_order', array('id' => $id) )
            ->fetch();
 
        $editor->db()
            ->query( 'update', $table1 )
            ->set( 'item_order', 'item_order-1', false )
            ->where( 'item_order', $order['item_order'], '>' )
            ->exec();
    } )
    ->process( $_POST )
    ->json();

found this now:
if I attempt a drag and drop row reorder and then click on the NEW button, it will show an error message:
An SQL error occurred: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '28-1' for key 'product_list_id'
1 was the item_order of the item i drag and dropped. I get that it is a duplicate entry, but Editor code should take care of that, right? Database is MySQL, product_list_id+list_id is uniique. The tabel also has a primary key, which is not used here.

Answers

  • mokozukimokozuki Posts: 13Questions: 3Answers: 0

    found debug data:

    {"fieldErrors":[],"error":"An SQL error occurred: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '28-2' for key 'product_list_id'","data":[],"ipOpts":[],"cancelled":[],"debug":[{"query":"SELECT  * FROM  mz_product_list_items_bak as table1 WHERE `table1`.`product_list_item_id` = :where_0 AND `table1`.`product_list_id` = :where_1 ","bindings":[{"name":":where_0","value":"922","type":null},{"name":":where_1","value":"28","type":null}]},{"query":"UPDATE  mz_product_list_items_bak as table1 SET  `item_order` = :item_order WHERE `table1`.`product_list_item_id` = :where_0 AND `table1`.`product_list_id` = :where_1 ","bindings":[{"name":":item_order","value":"2","type":null},{"name":":where_0","value":"922","type":null},{"name":":where_1","value":"28","type":null}]}]}
    

    it is trying to set item_order, but item_order 2 already exists. and the compound key is unique. should it not be unique? if not, then we may get multiple entries with the same item_order?

  • allanallan Posts: 61,438Questions: 1Answers: 10,049 Site admin

    What happens if you add or delete a row before you do any row reordering? Does it give an error - and if so, what is the error?

    It sounds like you've hit a limitation of the existing software - in that if the order column is included in the primary key, then it runs into issues since for a brief moment it would have two rows with matching keys. If you need that compound key, what I think you would need to do is assign a temporary value to the item that was moved (MAX_INT for example), then change the values of the other rows like in the event handlers that you have, before the software then sets the actual value for the row that was moved. It is all done in a transaction so it should be safe.

    Allan

  • mokozukimokozuki Posts: 13Questions: 3Answers: 0

    i get this message on deleting row 2:

    {"fieldErrors":[],"error":"An SQL error occurred: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '2-30' for key 'product_list_id'","data":[],"ipOpts":[],"cancelled":[],"debug":[{"query":"SELECT  `mz_product_list_items_bak`.`item_order` as 'mz_product_list_items_bak.item_order' FROM  `mz_product_list_items_bak` WHERE `item_order` = :where_0 ","bindings":[{"name":":where_0","value":"9214919c96d28","type":null}]},{"query":"UPDATE  `mz_product_list_items_bak` SET  `item_order` = item_order-1 WHERE `item_order` IS NOT NULL ","bindings":[]}]}
    

    again, the duplicate key error. when i was doing reordering manually i used to give the to be moved row a negative item_order and then reorder existing rows beofre i gave the to be reordered row it's new item_order, similar to what you suggested, but this could cause problems if more than 1 person is reordering at the same time.
    having the list_id and item_order as a unique key is just soemthing that seemed logical. should i do it differently in a way that would make this work?

  • mokozukimokozuki Posts: 13Questions: 3Answers: 0

    trying to get it to work with some custom queries before processing the changes. i have started trying to get CREATE to work first. But no matter what I try to add to the table, $values which is passed to the function for preCreate always has the value 1. and not the item_order which I am expecting. data sent to the server does contain the right information:

    action:create
    data[0][mz_product_list_items_bak][item_order]:15
    data[0][mz_product_list_items_bak][product_list_id]:
    data[0][mz_product_list_items_bak][product_id]:8180
    data[0][mz_product_list_items_bak][last_update]:
    data[0][mz_product_list_items_bak][created_by]:
    data[0][mz_getProducts_dyfs][name_external]:
    

    can you tell me what i am doing wrong?

  • allanallan Posts: 61,438Questions: 1Answers: 10,049 Site admin

    having the list_id and item_order as a unique key is just soemthing that seemed logical. should i do it differently in a way that would make this work?

    Agreed - it does make sense, its just that the software doesn't currently support that I'm afraid. You'd need to have an auto incrementing primary key on a single field.

    $values which is passed to the function for preCreate always has the value 1

    I'm afraid I don't understand what would cause that. $values should never just be a single number - it should always be an array of the values that was submitted.

    Allan

  • mokozukimokozuki Posts: 13Questions: 3Answers: 0

    almost there for adding rows. i only need to add a ORDER BY clause to the preCreate UDPATE query, but i can't get it to work or find the documentation. what i have now is:

    ->on( 'preCreate', function ( $editor, $values ) {
            // On create update all the other records to make room for our new one
            $editor->db()
                ->query( 'update', 'mz_product_list_items_bak' )
                ->set( 'mz_product_list_items_bak.item_order', 'mz_product_list_items_bak.item_order+1', false )
            ->where( 'mz_product_list_items_bak.product_list_id', $_GET['list_id'], '=' )
                ->and_where( 'mz_product_list_items_bak.item_order', $values['mz_product_list_items_bak']['item_order'], '>=' )
      ->order('item_order DESC')
                ->exec();
    

    debug message:

    {"fieldErrors":[],"error":"An SQL error occurred: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '28-11' for key 'product_list_id'","data":[],"ipOpts":[],"cancelled":[],"debug":[{"query":"UPDATE  `mz_product_list_items_bak` SET  `mz_product_list_items_bak`.`item_order` = mz_product_list_items_bak.item_order+1 WHERE `mz_product_list_items_bak`.`product_list_id` = :where_0 AND `mz_product_list_items_bak`.`item_order` >= :where_1 ","bindings":[{"name":":where_0","value":"28","type":null},{"name":":where_1","value":"10","type":null}]}]}
    

    there is no ORDER BY clause in the debug message, so it looks like it is not being applied. if i don't add the ORDER BY clause it will update item_order and hit the unique key contraint. but if it does it DESC it will be ok.

    so how should i use ->order() to make it work?

  • mokozukimokozuki Posts: 13Questions: 3Answers: 0

    i have managed to get deleting rows working

    where can i find in the documentation how to override create() on the servers side? at the moment it wants to update the joined table, but i don't want it to do that.

    can i do a preCreate to get the last used item_order and then use that value in postCreate? i am missing a few more its to getting this working. once i have the recipe i will post what i have to get this working for others.

  • allanallan Posts: 61,438Questions: 1Answers: 10,049 Site admin

    at the moment it wants to update the joined table, but i don't want it to do that.

    Add ->set( false ) to any field that you don't want to be updated (in this case the joined fields).

    Allan

  • mokozukimokozuki Posts: 13Questions: 3Answers: 0

    what about using order() on the update query? i tried different ways, but none of them work.
    ->order('item_order DESC')
    ->order('item_order', 'DESC')

    i can't find anything in the documentation, comments in Query.php just mention it being a string, so the first of the above two ways of writing it should be the correct one. but nothing happens. it's not sorted.

  • allanallan Posts: 61,438Questions: 1Answers: 10,049 Site admin

    I'm not clear what an ORDER would do on an update query? The order shouldn't make any difference when updating records.

    Allan

  • mokozukimokozuki Posts: 13Questions: 3Answers: 0

    if i add the item_order DESC to the update query it will update from the highest item_order first and thus item_order+1 will not create any duplicates while updating each row.

  • allanallan Posts: 61,438Questions: 1Answers: 10,049 Site admin

    I must confess that I didn't know that SQL UPDATE statements could accept an ORDER BY clause, but looking at the MySQL documentation it is indeed possible (in MySQL at least). Not in Postgres though.

    I'm afraid that this is not something that Editor currently supports.

    A database trigger might be the best way to go here.

    Allan

  • mokozukimokozuki Posts: 13Questions: 3Answers: 0

    http://dev.mysql.com/doc/refman/5.1/en/stored-program-restrictions.html - "Within a stored function or trigger, it is not permitted to modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger."

    any chance you could add the option to use order by in editor?

  • allanallan Posts: 61,438Questions: 1Answers: 10,049 Site admin

    Editor supports multiple databases at the backend, so I need to play to the lowest common denominator. Since this feature isn't available in all of the databases supported, it isn't something that will be added to the libraries I'm afraid.

    Allan

This discussion has been closed.