Leftjoin table not updating after edit

JuanPi

I have an issue when i try to edit the fields of the tables im joining dont update, but if i modify the fields of the table im not joining it update correctly, dont know what im missing

allan

If you are trying to edit data of joined tables, you need to also submit the joined table's primary id column data. That will let the server-side libraries know which row in the joined tables needs to be updated.

Allan

JuanPi

Sorry for the late response but yes you were right, thanks for the help

nicontrols

Hi! I am suffering the same problem. Where do you submit the joined table primary id?

allan

Include it in your Editor form as a hidden field type.

Allan

nicontrols

Thanks Allan.

I still can't get this to work - maybe I'm missing something obvious.

On the server side, I have joined 3 tables like so:

        Editor::inst( $db, 'enqitem', 'enqitemid')
        ->fields(
            Field::inst( 'stock.salmasid' ),
            Field::inst( 'stock.partnum' ),
            Field::inst( 'stockitem.salsubid' ),
            Field::inst( 'stockitem.condition1' ),
            Field::inst( 'stockitem.supplier' ),
            Field::inst( 'enqitem.enqitemid' ),
            Field::inst( 'enqitem.cost' ),
            Field::inst( 'enqitem.sale' ),
            Field::inst( 'enqitem.qty' ),
            Field::inst( 'enqitem.linedisc' ),
            Field::inst( 'enqitem.leadtime' )            
        )
        ->leftJoin('stock', 'stock.salmasid', '=', 'enqitem.itemmasid')
        ->leftJoin('stockitem', 'stockitem.salsubid', '=', 'enqitem.itemid')
        ->where('enqitem.enqnr',141316)
        ->debug( true )
        ->process( $_POST )
        ->json();

Then in the editor I have the following:

            let editor = new $.fn.dataTable.Editor( {
                ajax: {
                    url: "ajax.php",
                    type: "POST"
                },
                
                table: "#dataTableSites",
                fields: [{
                        label: "ID:",
                        name: "stock.salmasid",
                        type: "hidden"
                    },{
                        label: "ID:",
                        name: "stockitem.salsubid",
                        type: "hidden"
                    },{
                        label: "ID:",
                        name: "enqitem.enqitemid",
                        type: "hidden"
                    },{
                        label: "Part Number:",
                        name: "stock.partnum"
                    },{
                        label: "Condition:",
                        name: "stockitem.condition1",
                        type: "select",
                        options: [
                            { label: "New", value: "New" },
                            { label: "Used", value: "Used" },
                            { label: "Repair", value: "Repair" }
                        ]
                    },{
                        label: "Supplier:",
                        name: "stockitem.supplier",
                        type: "select"
                    },{
                        label: "Cost:",
                        name: "enqitem.cost"
                    },{
                        label: "Sale:",
                        name: "enqitem.sale"
                    },{
                        label: "Qty:",
                        name: "enqitem.qty"
                    },{
                        label: "Disc:",
                        name: "enqitem.linedisc"
                    },{
                        label: "Delivery Time:",
                        name: "enqitem.leadtime"
                    }
                ]
            } );

The data displays perfectly, however when I try to update a field I get the following error:

An SQL error occurred: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'enqitem.enqitemid' in 'field list'

In the debug I can see the following:

{"query":"SELECT  `enqitem`.`enqitemid` as 'enqitem.enqitemid' FROM  `stock` WHERE `salmasid` = :where_0....

This is where it fails, as enqitemid doesn't exist in the stock table.

What have I messed up?

allan

Ah no - that isn't you messing up, its me! That's a bug in 1.8.0 I'm afraid. This commit fixes it and that will be released in 1.8.1 (very soon), or you can grab the updated file from the repo.

Allan

nicontrols

Nice one Allan - thats fixed it!

Your support on here is incredible - appreciate the quick responses.
Cheers mate.