Editor trouble - left join & inline editing not saving changes

jtmega

Link to test case: https://dev-sfscomtool.pantheonsite.io/test/
Debugger code (debug.datatables.net): https://debug.datatables.net/uzuqut
Error messages shown: No error message.
Description of problem:

Hello, thanks for making this super handy tool. I'm hoping you can help with a problem I'm having, it feels like I'm just missing something but I can't figure out what it is! I found a couple of other forum posts with similar-seeming questions, but they were closed without final answers, and I wasn't able to find any workable ideas in those.

I'm using the PHP version of the Editor. I have two tables I'm dealing with here, a products table sfProducts and a table to store user-product relationships sfUserInput.

Here's the goal:
- Display the product data along with the individual user's input for three values related to each of those products
- When a user changes one of those values, update only the values in sfUserInput. If a relevant row does not exist, create it.
- Treat the values from sfProducts in the table as read-only, never update those.

Here's what happens:
- The products are correctly displayed, as are the user-related values (the darker-colored columns on the test page, there are a few in there.
- If I click a field to change it, i can enter something, but it just goes away when I click away. Looking at the JSON response, I only see a "select" statement (not sure if I should see anything else). No changes are being made to the data table, no console errors showing up.

I boiled it all down to a simplified example (fewer table columns, mostly) to help me debug. Code is below. The test link above will show you this simplified example, and I manually entered a few values in the sfUserInput table for the test user, you should see those appear at the top.

Thank you for any help you might be able to give!!

controller.php

$editor = Editor::inst( $db, 'sfProducts', array('sfProducts.End_Product_Code','sfProducts.WBSCM_USDA_Foods_Material_Code') )
    ->fields(
        Field::inst( 'sfProducts.End_Product_Code' )->set( Field::SET_NONE ),
        Field::inst( 'sfProducts.End_Product_Description' )->set( Field::SET_NONE ),
        Field::inst( 'sfProducts.Category' )->set( Field::SET_NONE ),
        Field::inst( 'sfProducts.WBSCM_USDA_Foods_Material_Code' )->set( Field::SET_NONE ),
        Field::inst( 'sfUserInput.userId' )->set( Field::SET_CREATE )->setValue( $currentUser ),
        Field::inst( 'sfUserInput.End_Product_Code' )->set( Field::SET_CREATE ),
        Field::inst( 'sfUserInput.WBSCM_USDA_Foods_Material_Code' )->set( Field::SET_CREATE ),
        Field::inst( 'sfUserInput.ServDay' )->set( Field::SET_BOTH ),
        Field::inst( 'sfUserInput.MenuDays' )->set( Field::SET_BOTH ),
        Field::inst( 'sfUserInput.EstServ' )->set( Field::SET_BOTH )
    )
    ->leftJoin('sfUserInput', 'sfProducts.End_Product_Code = sfUserInput.End_Product_Code AND sfProducts.WBSCM_USDA_Foods_Material_Code = sfUserInput.WBSCM_USDA_Foods_Material_Code AND sfUserInput.userId = ' . $currentUser )
    ->debug(true)
    ->process( $_POST )
    ->json();

javascript

var editor;

jQuery(document).ready(function( $ ) {
    editor = new $.fn.dataTable.Editor({
        ajax: "<?=$tablepath?>/controllers/productTEST.php",
        table: "#<?=$tableID?>",
        formOptions: {
            inline: {
                submit: 'allIfChanged'
            }
        },
        fields: [
            {
                type: "hidden",
                label: "User ID",
                name: "sfUserInput.userId"
            },
            {
                label: "Product Code:",
                name: "sfProducts.End_Product_Code"
            },
            {
                label: "Product:",
                name: "sfProducts.End_Product_Description"
            },
            {
                label: "Category:",
                name: "sfProducts.Category"
            },
            {
                label: "WBSCM USDA Code",
                name: "sfProducts.WBSCM_USDA_Foods_Material_Code"
            },
            {
                label: "Servings Per Day:",
                name: "sfUserInput.ServDay",
                def: "0"
            },
            {
                label: "Menu Days:",
                name: "sfUserInput.MenuDays",
                def: "0"
            },
            {
                label: "Estimated Servings",
                name: "sfUserInput.EstServ",
                def: "0"
            }
        ]
    });

    let productTable = $('#<?=$tableID?>').DataTable({
        dom: 'Bfrtip',
        ajax: {
            url: '<?=$tablepath?>/controllers/product.php',
            type: 'POST'
        },
        paging: false,
        fixedHeader: true,
        order: [[ 2, 'asc' ], [ 1, 'asc' ]],
        columns: [
            { 
                data: 'sfProducts.End_Product_Code',
                orderable: false 
            },
            { 
                data: 'sfProducts.End_Product_Description', 
                orderable: false 
            },
            { 
                data: 'sfProducts.Category', 
                orderable: false 
            },
            { 
                data: 'sfProducts.WBSCM_USDA_Foods_Material_Code', 
                orderable: false 
            },
            {
                data: 'sfUserInput.ServDay',
                className: 'nspd editable',
                orderable: false
            },
            {
                data: 'sfUserInput.MenuDays',
                className: 'nmd editable',
                orderable: false
            },
            {
                data: 'sfUserInput.EstServ',
                className: 'esd editable',
                orderable: false
            },  
        ],
        buttons: [
            'csv',
            {
                extend: 'colvis',
                columns: 'th:nth-child(n+7)'
            }
        ],
        
    });

    // Activate an inline edit on click of a table cell
    $('#<?=$tableID?>').on('click', 'tbody td.editable', function (e) {
        editor.inline( productTable.cells(this.parentNode, '.editable').nodes(), {
            onBlur: 'submit',
            submit: 'allIfChanged'
        });
    });
});

HTML

<table id="<?=$tableID?>" class="display" cellspacing="0" width="100%">
    <thead>
        <tr>
            <!-- <th></th> -->
            <th>Code</th>
            <th>Name</th>
            <th>Category</th>
            <th>WBSCM USDA Material Code</th>
            <th>Number of<br>Servings/Day<br><span class="optional">(optional)</span></th>
            <th>Number of<br>Menu Days<br><span class="optional">(optional)</span></th>
            <th>Estimated<br>Number of<br>Serving Desired</th>
        </tr>
    </thead>
</table>

jtmega

After some more searching, I've found some information that might help me narrow down my problem. I've refactored some things, but I'm still not quite there.

@allan posted in another support thread that Editor cannot create rows in a secondary joined table, it can only update them.

This was crucial information. My solution, just for posterity: first, I set up a process to create rows in the user-product, one row per product per user. (This will result in a lot of rows, but it should be fine.) Then, after some more testing, I actually reversed the join - since I want to update the user-product table, I made that the primary table, and made the product table the secondary table. This is a little goofy, and I'll have to make sure to watch out for some problems that could arise from this, but it was the only way I could get those fields to update.

So now it's working - not 100% ideal, but good enough! DataTables is still worth it as a time saving tool.

rf1234

Just to share my experience. I frequently use proprietary setFormatters on aliased fields or the numerous Editor events to handle real complex updating.

My favorite event handlers are "validatedEdit" and "validatedCreate" to do more complex follow on processing. At that time I am sure that the "primary" create or edit action will come through.
https://editor.datatables.net/manual/php/events

jtmega

Interesting, I'll take a look at those. Thanks for the tip!