Leftjoin table not updating after edit

Leftjoin table not updating after edit

JuanPiJuanPi Posts: 4Questions: 0Answers: 0



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

Replies

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

    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

  • JuanPiJuanPi Posts: 4Questions: 0Answers: 0

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

  • nicontrolsnicontrols Posts: 32Questions: 16Answers: 1

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

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

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

    Allan

  • nicontrolsnicontrols Posts: 32Questions: 16Answers: 1

    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?

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

    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

  • nicontrolsnicontrols Posts: 32Questions: 16Answers: 1

    Nice one Allan - thats fixed it!

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

This discussion has been closed.