Field::SET_EDIT will update fields on CREATE aswell...

ztevie · May 2017

A problem occured. I have a few fields set to SET_EDIT. Still when doing a CREATE those SET_EDIT fields will be set to their default value in the database, which is false(0). Of course I only notice this when the values are already true(1).
These fields are from a joined table, and hence their values already exist and are not created. the main table is having a row created, but why are the joined SET_EDIT fields also updated when creating a new row in the main table? The fields are holes.hole_boostered, holes.hole.redrill and holes.hole_water.
Do Editor assume that all fields does not exist when CREATE. And because no value is submitted it will "create" those values, but since it's ajoined table they will be updated instead?

Editor::inst( $db, 'holeactions', 'holeaction_id' )
    ->debug(true)
    ->field(
        Field::inst( 'holeactions.hole_id' )
            ->set(Field::SET_CREATE),
        Field::inst( 'holeactions.holeaction_id' )
            ->set(false),
        Field::inst( 'holeactions.shift_id' )
            ->set(Field::SET_CREATE)
            ->setValue($_POST["sid"])
            ->get(false),
        Field::inst( 'holeactions.holeaction_active' )
            ->set(Field::SET_EDIT),
        Field::inst( 'holeactions.holeaction_start' )
            ->set(Field::SET_EDIT),
        Field::inst( 'holeactions.holeaction_end' )
            ->set(Field::SET_EDIT),
        Field::inst( 'holeactions.userid_started' )
            ->set(Field::SET_EDIT),
        Field::inst( 'holeactions.userid_ended' )
            ->set(Field::SET_EDIT),
        Field::inst( 'holeactions.holeaction_depthprod' )
            ->set( Field::SET_EDIT ),
        Field::inst( 'holeactions.holeaction_depthredrill' )
            ->set( Field::SET_EDIT ),
        Field::inst( 'holeactions.holeaction_depthupdrill' )
            ->set( Field::SET_EDIT ),
        Field::inst( 'holes.hole_id' )
            ->set(false),
        Field::inst( 'holes.hole_nr' )
            ->set(false),
        Field::inst( 'holes.hole_depth' )
            ->set(false),
        Field::inst( 'holes.hole_drilled' )
            ->set(false),
        Field::inst( 'holes.hole_boostered' )
            ->set(Field::SET_EDIT),
        Field::inst( 'holes.hole_redrill' )
            ->set(Field::SET_EDIT),
        Field::inst( 'holes.hole_sylta' )
            ->set(Field::SET_EDIT),
        Field::inst( 'holes.hole_water' )
            ->set(Field::SET_EDIT),
        Field::inst( 'holes.blast_id' )
            ->set(false),
        Field::inst( 'blasts.blast_name' )
            ->set(false)
    )
    ->leftJoin( 'holes', 'holes.hole_id', '=', 'holeactions.hole_id' )
    ->leftJoin( 'blasts', 'blasts.blast_id', '=', 'holes.blast_id' )
    ->leftJoin( 'shifts', 'shifts.shift_id', '=', 'holeactions.shift_id' )
    ->where('holeactions.shift_id', $_POST["sid"], '=')
    ->on( 'preEdit', function ( $editor, $id, $values ) {
        if(isset($values["holes"]["hole_redrill"])){
            $editor
                ->field('holes.hole_id')
                ->setValue($_POST["hid"]);
        }
    })
    ->process($_POST)
    ->json();

//The create portion is within another function
hEditor
                            .create(false)
                            .set('holeactions.hole_id', this_id)
                            .submit();

//And the editor which holds the field values, submit also set to changed, but even if it was "all", SET_EDIT should prevent them from being updated on CREATE?
 hEditor = new $.fn.dataTable.Editor( {
            ajax: {
                url: "../_includes/process_shiftholes.php",
                type: "POST",
                data: function ( d ) {
                    return $.extend( {}, d, {
                        hid: hidActive, sid: shiftActive, rid: rigActive
                    } );
                }   
            },
            table: "#tbl-shiftholes",
            formOptions: {
                main: {
                    submit: 'changed'
                }
            },
            fields: [
                { 
                    name: "holeactions.hole_id"
                },
                {
                    label: "#",
                    name:  "holes.hole_nr"
                },
                {
                    label: "Salva:",
                    name:  "blasts.blast_name"
                },
                {
                    label: "Uppborrning:",
                    name:  "holes.hole_redrill"
                },
                {
                    label: "Uppborrning:",
                    name:  "holes.hole_water"
                },
                {
                    label: "Uppborrning:",
                    name:  "holes.hole_boostered"
                },
                {
                    label: "Hålid:",
                    name:  "holes.hole_id",
                    type: "hidden"
                }
            ]
        } );

ztevie · May 2017

Here's the sql returned, it obviously updates those fields (Look at hole_redrill and hole_water) I could set those values on the create on the client side, but I wonder why those fields are updated even when they're set to just SET_EDIT? If set to false they will not be updated.

{"data":[{"DT_RowId":"row_26","holeactions":{"hole_id":"7304","holeaction_id":"26","holeaction_active":"0","holeaction_start":"","holeaction_end":"","userid_started":null,"userid_ended":null,"holeaction_depthprod":"0","holeaction_depthredrill":"0","holeaction_depthupdrill":"0"},"holes":{"hole_id":"7304","hole_nr":"280","hole_depth":"17.6","hole_drilled":"0","hole_boostered":"1","hole_redrill":"0","hole_sylta":null,"hole_water":"0","blast_id":"87"},"blasts":{"blast_name":"N6_345_8"}}],"debugSql":[{"query":"INSERT INTO holeactions ( hole_id, shift_id ) VALUES ( :hole_id, :shift_id )","bindings":[{"name":":hole_id","value":"7304","type":null},{"name":":shift_id","value":"11","type":null}]},{"query":"SELECT * FROM holes WHERE hole_id = :where_0 ","bindings":[{"name":":where_0","value":"7304","type":null}]},{"query":"UPDATE holes SET hole_redrill = :hole_redrill, hole_water = :hole_water WHERE hole_id = :where_0 ","bindings":[{"name":":hole_redrill","value":"","type":null},{"name":":hole_water","value":"","type":null},{"name":":where_0","value":"7304","type":null}]},

allan · May 2017

Still when doing a CREATE those SET_EDIT fields will be set to their default value in the database

This is expected. The SQL that Editor builds basically doesn't include the fields which are marked as to bot be set when creating the row. Therefore the database will always use the default, just like if you were doing a regular INSERT (which is really all that is happening).

If you don't want the database's defined default values, you need to tell it to set it to something else.

Allan

ztevie · May 2017

Ok, so if I want to keep the values already in there, since I don't use a form. I will have to first set them in a data-attribute in the HTML-element they belong to. Them set their values in the create again? I guess I have to do that if there's no other way... There are six additional fields to send, altough four of them are boolean so it's not heavy data.
I started doing something like that, it will look something like this, I use a slick carousel, and a bootbox confirm when clicking the hole-element that will create a new holeaction:

$('.slickholes').on( 'click', 'a.holeclick', function () {
            var this_id = this.id;
            var _dr = $(this).attr("data-dr");
            var _bo = $(this).attr("data-bo");
            var _rd = $(this).attr("data-rd");
            var _wa = $(this).attr("data-wa");
            var _sy = $(this).attr("data-sy");
            var _in = $(this).attr("data-in");
            bootbox.confirm({
                size: "small",
                title: "<b>Stämmer nedanstående?</b>",
                message: "Hålnummer: <b>" + $(this).text() + "</b><br>"
                    + "Salva: <b>" + $('#sel_newhole_blasts option:selected').text() + "</b><br>"
                    + "Status: <b>" + $(this).attr("data-s"),
                buttons: {
                    confirm: {
                        label: '<i class="fa fa-check"></i> OK, välj hål',
                        className: 'btn-primary active pull-left'
                    },
                    cancel: {
                        label: '<i class="fa fa-undo"></i> Avbryt',
                        className: 'btn-danger active pull-right'
                    }
                },
                callback: function(result){
                    if(result){
                        bootbox.hideAll();
                        slickSet = false;
                        $('.slickholes').slick('unslick');
                        $( ".slickholes" ).empty();
                        $("#slickhelp").collapse("hide");
                        $("#shift-hole-list").collapse("hide");
                        hEditor
                            .create(false)
                            .set('holeactions.hole_id', this_id)
//SET ALL REMAINING FIELDS HERE LIKE THE ROW ABOVE
                            .submit();
                    }
                }
            });
        });

allan · May 2017

Ok, so if I want to keep the values already in there, since I don't use a form. I will have to first set them in a data-attribute in the HTML-element they belong to. Them set their values in the create again?

I'm not quite getting it, sorry! How can they have values to keep if this is an insert? They will either take a default value (if not specified) or take the value that is submitted.

Allan

ztevie · May 2017

To sum it up: Why does editor update the values in the second joined table when I try to do a create on the first main table?
Even though the fields in the joined table are set for edit only? If you see the UPDATE being made from this debug sql:

debugSql":[{"query":"INSERT INTO holeactions ( hole_id, shift_id ) VALUES ( :hole_id, :shift_id )","bindings":[{"name":":hole_id","value":"7304","type":null},{"name":":shift_id","value":"11","type":null}]},{"query":"SELECT * FROM holes WHERE hole_id = :where_0 ","bindings":[{"name":":where_0","value":"7304","type":null}]},{"query":"UPDATE holes SET hole_redrill = :hole_redrill, hole_water = :hole_water WHERE hole_id = :where_0 ","bindings":[{"name":":hole_redrill","value":"","type":null},{"name":":hole_water","value":"","type":null},{"name":":where_0","value":"7304","type":null}]}

allan · May 2017

It would only update the joined table if the id for the joined table is being submitted and there is data for that table also being submitted.

It looks like hole_id is being submitted as 7304 and there are two empty values being submitted for that row - which is why it is writing to it.

Do you mean to submit the holes.hole_id value?

Allan

ztevie · May 2017

Yes. This is connected with the issue: https://datatables.net/forums/discussion/comment/111082
Conclusion there is I need to send the hole_id when editing fields that belong to that table.
When I create I will need to send that hole_id to fetch the values from that table. However, as we discussed earlier, editor will update those fields on create. Therefore I need to resend all the values from that table and let them be updated with those same values, otherwise they will get default db values....

Well, it does work to set all of them and resend on create, but it's not optimal for code and data slimming.

I think my approach is a bit unusual, I'll try to explain my tables connections with a more simple example to clarify.
Think of a repair shop having different cars in a table. They will make a Datatables script for repairing those cars, it's the tabel carrepairs that is the main table for Datatables script. Table carrepair can have the same cars in for repair several times. Datatables will also be able to show some values from each car, and change some of them. This is kind of similar to what I'm doing.

--Table carrepairs--
Field 1: carrepair_id
Field 2: car_id
Field 3: carrepair_active
Field 4: carrepair_started
Field 5: carrepair_ended
Field 6: carrepair_name

--Table cars--
Field 1: car_id
Field 2: car_color
Field 3: car_owner
Field 4: car_wheeltype

So you see "cars" already have values set, each car can be brought in for repair but can also have some of their values changed in the carrepair Datatables. For example color if it's repainted or wheeltype if they are changed in the repairshop....
Hope it's clearer?

allan · May 2017

Thanks for the explanation.

It sounds like you on't want to send cars.car_id when something in its fields has changed. If it hasn't changed, then don't sent it and the cars table won't be written to. If it has, then yes, you would need to send cars.car_id with the new values.

I still don't really get:

However, as we discussed earlier, editor will update those fields on create. Therefore I need to resend all the values from that table and let them be updated with those same values, otherwise they will get default db values

The default db values would only be used when a new row is inserted.

Equally, Editor will only update the cars fields if cars.car_id is sent to the server on create. For a standard create you would just send carrepairs.car_id which would link it to an existing car.

I think there is a disconnect that I'm not quite getting. Are you doing any processing to add a new cars row on each submit?

Allan

ztevie · May 2017

No, I'm not adding a new row in the 'cars' table....
I am sending holes.hole_id on edit, not on create.
On create I'm sending holeactions.hole_id (carrepairs.car_id). Here's the form data when doing create, I'm not setting any parameters except holeactions.hole_id (carrepairs.car_id). As you see, all parameters are being sent as empty together with that id. I haven't set them in my client code...
holeactions = carrepairs, holes = cars

action:create
data[0][holeactions][hole_id]:7638
data[0][holeactions][holeaction_depthprod]:
data[0][holeactions][holeaction_depthredrill]:
data[0][holeactions][holeaction_depthupdrill]:
data[0][holeactions][holeaction_status]:
data[0][holes][hole_redrill]:
data[0][holes][hole_drilled]:
data[0][holes][hole_boostered]:
data[0][holes][hole_water]:
data[0][holes][hole_sylta]:
data[0][holes][hole_left]:
data[0][holes][hole_info]:
data[0][holes][hole_active]:
hid:7638
sid:12
rid:1
bid:90

All of these holes fields are included in the client editor for the possibility to edit them later, maybe they get included by default? I think this is the problem? For some reason they're being sent on create but of course they're empty cause they haven't been set by me and have not even been fetched from the db to datatables yet?
Here's where I call create, you see I've commented those fields out for testing purposes :

hEditor
                            .create(false)
                            .set('holeactions.hole_id', this_id)
                            /*.set('holes.hole_drilled', _dr)
                            .set('holes.hole_redrill', _rd)
                            .set('holes.hole_water', _wa)
                            .set('holes.hole_sylta', _sy)
                            .set('holes.hole_info', _in)
                            .set('holes.hole_left', _le)
                            .set('holes.hole_active', "1")*/
                            .submit();

I don't use the normal create button, instead I have made a slick carousel where all the holes(cars) are. When clicking on one of those, create will kick in. The create code is from within that function. What I need to do to make this work is: When I create the slick carousel with holes(cars), I have to include all the values in data-attributes, then when clicking one hole(car) when create I will get these values from the <a> being clicked, setting them in create to make them be included in the form data, so their original value is kept. Here's that full click function uncommented and also where I set them when creating the slick carousel with holes(cars). Hope you now understand why I need to get these values, resending them to the server again to avoid default db values being set?

//Creating slick carousel with holes(cars), harr is an array of all holes incl. values sent into the function
function setHoleList(harr){
            var appHTML; 
            var udC = 0;
            var drC = 0;
            var boC = 0;
            var rdC = 0;
            var sCls;
            var sSta;
            var _dr;
            var _bo;
            var _rd;
            var _wa;
            var _sy;
            var _in;
            var _le;
            var _ac;
            $.each(harr, function(i, val){
                sCls = "slick-none";
                sSta = "Oborrad";
                _dr = harr[i]["h_drilled"];
                _bo = harr[i]["h_boost"];
                _rd = harr[i]["h_redrill"];
                _wa = harr[i]["h_water"];
                _sy = harr[i]["h_sylta"];
                _in = harr[i]["h_info"];
                _le = harr[i]["h_left"];
                _ac = harr[i]["h_active"];
                udC++;
                if(_dr){
                    sCls = "slick-drill";
                    sSta = "Borrad";
                    udC--;
                    drC++;
                }
                if(_bo){
                    sCls = "slick-boost";
                    sSta = "Boostrad";
                    boC++;
                }
                if(_rd){
                    sCls = "slick-redrill";
                    sSta = "Uppborrning";
                    rdC++;
                }
                sCls = _dr ? "slick-drill" : sCls;
                sCls = _bo ? "slick-boost" : sCls;
                sCls = _rd ? "slick-redrill" : sCls;
                appHTML = '<div><a id="'+harr[i]["h_id"]
                + '" data-s="'+sSta
                + '" data-dr="'+_dr
                + '" data-rd="'+_rd
                + '" data-sy="'+_sy
                + '" data-wa="'+_wa
                + '" data-in="'+_in
                + '" data-le="'+_le
                + '" data-ac="' +_ac
                + '" class="btn btn-sm '+sCls+' fa-alph holeclick">'+harr[i]["h_nr"]+'</a></div>';
                $('.slickholes').append(appHTML);
            });
            $("#udC").html("Oborrad ("+udC+")");
            $("#drC").html("Borrad ("+drC+")");
            $("#boC").html("Boostrad ("+boC+")");
            $("#rdC").html("Uppborrning ("+rdC+")");
            $('.slickholes').slick({
                infinite: true,
                dots: true,
                rows: 5,
                slidesPerRow: 5,
                slidesToScroll: 1,
                customPaging : function(slider, i) {
                    var thumb = $(slider.$slides[i]).data();
                    return '<a>'+(i*25+1)+'</a>';
                }
            });


//AND THE FULL CLICK FUNCTION WHEN CLICKING A HOLE, THUS CREATING A NEW ROW
$('.slickholes').on( 'click', 'a.holeclick', function () {
            var this_id = this.id;
            var _dr = $(this).attr("data-dr");
            var _rd = $(this).attr("data-rd");
            var _wa = $(this).attr("data-wa");
            var _sy = $(this).attr("data-sy");
            var _in = $(this).attr("data-in");
            var _ac = $(this).attr("data-ac");
            var _le = $(this).attr("data-le");
            if(_ac === "1"){
                bootbox.alert({
                    message: "Hålet är redan aktivt!",
                    size: 'small'
                    });
                    return;
            };
            bootbox.confirm({
                size: "small",
                title: "<b>Stämmer nedanstående?</b>",
                message: "Hålnummer: <b>" + $(this).text() + "</b><br>"
                    + "Salva: <b>" + $('#sel_newhole_blasts option:selected').text() + "</b><br>"
                    + "Status: <b>" + $(this).attr("data-s"),
                buttons: {
                    confirm: {
                        label: '<i class="fa fa-check"></i> OK, välj hål',
                        className: 'btn-primary active pull-left'
                    },
                    cancel: {
                        label: '<i class="fa fa-undo"></i> Avbryt',
                        className: 'btn-danger active pull-right'
                    }
                },
                callback: function(result){
                    if(result){
                        bootbox.hideAll();
                        slickSet = false;
                        $('.slickholes').slick('unslick');
                        $( ".slickholes" ).empty();
                        $("#slickhelp").collapse("hide");
                        $("#shift-hole-list").collapse("hide");
                        hTable.buttons().enable();
                        blastActive = $('#sel_newhole_blasts').val();
                        hEditor
                            .create(false)
                            .set('holeactions.hole_id', this_id)
                            .set('holes.hole_drilled', _dr)
                            .set('holes.hole_redrill', _rd)
                            .set('holes.hole_water', _wa)
                            .set('holes.hole_sylta', _sy)
                            .set('holes.hole_info', _in)
                            .set('holes.hole_left', _le)
                            .set('holes.hole_active', "1")
                            .submit();
                    }
                }
            });
        });

And the form data when being submitted by setting all values in create, now because I set the values in create they will get the right values instead of being empty, getting the default db values:
action:create
data[0][holeactions][hole_id]:7472
data[0][holeactions][holeaction_depthprod]:
data[0][holeactions][holeaction_depthredrill]:
data[0][holeactions][holeaction_depthupdrill]:
data[0][holeactions][holeaction_status]:
data[0][holes][hole_redrill]:1
data[0][holes][hole_drilled]:1
data[0][holes][hole_boostered]:
data[0][holes][hole_water]:0
data[0][holes][hole_sylta]:0.0
data[0][holes][hole_left]:0
data[0][holes][hole_info]:
data[0][holes][hole_active]:1
hid:7472
sid:12
rid:1
bid:89

allan · June 2017

Just a quick one to say I haven't forgotten about this! I'm going to have to actually concentrate and focus properly on it to help I think, and haven't had a chance to give it the time it deserves yet. I'll get back to you, likely tomorrow.

Allan

ztevie · June 2017

No problem. It is working with my workaround so I can move forward with the project anyway. But it would be interesting to see if there's a solution, or maybe I just made some stupid mistake somewhere....
If I find some extra time I'll try to make a much simpler example and try to recreate what's going on.

ztevie · June 2017

A quick question until I can set up a basic example you can test:
Is there a difference between set_edit and set (false) when doing a create?
Because set (false) fields do not update, only the set_edit ones?
I would have thought that when doing a create, set_edit field would basically be considered to have the set(false) flag set?

allan · June 2017

Is there a difference between set_edit and set (false) when doing a create?

There shouldn't be. On a create action if you have used field->set( Field::SET_EDIT ) it should only set a value when an edit action is performed.

I would have thought that when doing a create, set_edit field would basically be considered to have the set(false) flag set?

That's correct. That is what should be happening. Are those the columns which are taking the database default values. If so, that's the issue. They aren't being written to, therefore on INSERT the database it writing its own default values.

Allan

ztevie · June 2017

Ok, I thought so. But still, set (false) field are not updated on create, set_edit fields are. Note it is not an insert, because it's fields from a joined table they get their own sql update as seen in the debug sql.
I'll come back when I have something for you to view publicly and debug...

allan · June 2017

That's weird and it certainly sounds like there might be an issue. If you could perhaps show me the SQL Editor is executing (->debug(true)) for both cases, that might yield some interesting information.

Regards,
Allan

ztevie · June 2017

OK, let's see if you find something weird:

First, my fields in editor server side. The fields affected are those om the table 'holes':

Editor::inst( $db, 'holeactions', 'holeaction_id' )
    ->debug(true)
    ->field(
        Field::inst( 'holeactions.hole_id' )
            ->set(Field::SET_CREATE),
        Field::inst( 'holeactions.holeaction_id' )
            ->set(false),
        Field::inst( 'holeactions.shift_id' )
            ->set(Field::SET_CREATE)
            ->setValue($_POST["sid"])
            ->get(false),
        Field::inst( 'holeactions.bit_id' )
            ->set(Field::SET_CREATE),
        Field::inst( 'holeactions.holeaction_status' )
            ->set(Field::SET_EDIT),
        Field::inst( 'holeactions.holeaction_start' )
            ->set(Field::SET_EDIT),
        Field::inst( 'holeactions.holeaction_end' )
            ->set(Field::SET_EDIT),
        Field::inst( 'holeactions.userid_started' )
            ->set(Field::SET_EDIT),
        Field::inst( 'holeactions.userid_ended' )
            ->set(Field::SET_EDIT),
        Field::inst( 'holeactions.holeaction_depthprod' )
            ->set(Field::SET_EDIT)
            ->validator( 'Validate::numeric', array(
                "message" => "Värdet måste vara numeriskt..."
            ))
            ->validator( 'Validate::minNum', array(
                "min" => 0,
                "message" => "Får ej vara minus..."
            ))
            ->setFormatter( function ( $val, $data, $opts ) {
                if($val > 0){
                    return number_format($val, 1, '.', '');
                }
                else{
                    return 0;
                }
            })
            ->getFormatter( function ( $val, $data, $opts ) {
                return number_format($val, 1, '.', '');
            }),
        Field::inst( 'holeactions.holeaction_depthredrill' )
            ->set(Field::SET_EDIT)
            ->validator( 'Validate::numeric', array(
                "message" => "Värdet måste vara numeriskt..."
            ))
            ->validator( 'Validate::minNum', array(
                "min" => 0,
                "message" => "Får ej vara minus..."
            ))
            ->setFormatter( function ( $val, $data, $opts ) {
                if($val > 0){
                    return number_format($val, 1, '.', '');
                }
                else{
                    return 0;
                }
            })
            ->getFormatter( function ( $val, $data, $opts ) {
                return number_format($val, 1, '.', '');
            }),
        Field::inst( 'holeactions.holeaction_depthupdrill' )
            ->set(Field::SET_EDIT)
            ->validator( 'Validate::numeric', array(
                "message" => "Värdet måste vara numeriskt..."
            ))
            ->validator( 'Validate::minNum', array(
                "min" => 0,
                "message" => "Får ej vara minus..."
            ))
            ->setFormatter( function ( $val, $data, $opts ) {
                if($val > 0){
                    return number_format($val, 1, '.', '');
                }
                else{
                    return 0;
                }
            })
            ->getFormatter( function ( $val, $data, $opts ) {
                return number_format($val, 1, '.', '');
            }),
        Field::inst( 'holes.hole_id' )
            ->set(false),
        Field::inst( 'holes.hole_nr' )
            ->set(false),
        Field::inst( 'holes.hole_active' )
            ->set(Field::SET_EDIT),
        Field::inst( 'holes.hole_depth' )
            ->set(false),
        Field::inst( 'holes.hole_drilled' )
            ->set(Field::SET_EDIT),
        Field::inst( 'holes.hole_boostered' )
            ->set(false),
        Field::inst( 'holes.hole_redrill' )
            ->set(Field::SET_EDIT),
        Field::inst( 'holes.hole_sylta' )
            ->set(Field::SET_EDIT)
            ->validator( 'Validate::numeric', array(
                "message" => "Värdet måste vara numeriskt..."
            ))
            ->validator( 'Validate::minNum', array(
                "min" => 0,
                "message" => "Får ej vara minus..."
            ))
            ->setFormatter( function ( $val, $data, $opts ) {
                if($val > 0){
                    return number_format($val, 1, '.', '');
                }
                else{
                    return 0;
                }
            })
            ->getFormatter( function ( $val, $data, $opts ) {
                return number_format($val, 1, '.', '');
            }),
        Field::inst( 'holes.hole_water' )
            ->set(Field::SET_EDIT),
        Field::inst( 'holes.hole_left' )
            ->set(Field::SET_EDIT),
        Field::inst( 'holes.hole_info' )
            ->set(Field::SET_EDIT),
        Field::inst( 'holes.blast_id' )
            ->set(false),
        Field::inst( 'blasts.blast_name' )
            ->set(false)
    )
    ->leftJoin( 'holes', 'holes.hole_id', '=', 'holeactions.hole_id' )
    ->leftJoin( 'blasts', 'blasts.blast_id', '=', 'holes.blast_id' )
    ->leftJoin( 'shifts', 'shifts.shift_id', '=', 'holeactions.shift_id' )
    /*->join(
        Mjoin::inst( 'blasts' )
            ->link( 'drillrigs.rig_id', 'blastrigconn.rig_id' )
            ->link( 'blasats.blast_id', 'blastrigconn.blast_id' )
            ->order( 'blast_name asc' )
            ->fields(
                Field::inst( 'blasts.blast_id' )
                    ->options( 'blasts', 'blast_id', 'blast_name' ),
                Field::inst( 'blast_name' )
            )
            ->where('drillrigs.rig_id', $_POST["rid"], '=')
            ->where('blasts.blaststatus_name', 'Aktiv', '=')
    )*/
    /*->join(
        Mjoin::inst( 'drillrigs' )
            ->link( 'blasts.blast_id', 'blastrigconn.blast_id' )
            ->link( 'drillrigs.rig_id', 'blastrigconn.rig_id' )
            ->order( 'rig_name asc' )
            ->fields(
                Field::inst( 'rig_id' )
                    ->options( 'drillrigs', 'rig_id', 'rig_name' ),
                Field::inst( 'rig_name' )
            )
    )*/
    ->where('holeactions.shift_id', $_POST["sid"], '=')
    ->on( 'preEdit', function ( $editor, $id, $values ) {
        if(isset($values["holes"]["hole_sylta"])
                || isset($values["holes"]["hole_water"])
                || isset($values["holes"]["hole_info"])
                || isset($values["holes"]["hole_redrill"])
                || isset($values["holes"]["hole_left"])
                || isset($values["holes"]["hole_drilled"])
                || isset($values["holes"]["hole_active"])
                ){
            $editor
                ->field('holes.hole_id')
                ->setValue($_POST["hid"]);
        }
        if(isset($_POST["setStart"]) && $_POST["setStart"] === "true"){
            $editor
                ->field('holeactions.userid_started')
                ->setValue(getUser());
            $editor
                ->field('holeactions.holeaction_start')
                ->setValue(date('Y-m-d H:i:s'));
        }
        if(isset($_POST["setEnd"]) && $_POST["setEnd"] === "true"){
            $editor
                ->field('holeactions.userid_ended')
                ->setValue(getUser());
            $editor
                ->field('holeactions.holeaction_end')
                ->setValue(date('Y-m-d H:i:s'));
        }
    })
    
    ->on( 'preCreate', function ( $editor, $values ) {
        setBlastActive($editor->db(), $_POST["rid"], $_POST["bid"]);
    })
    ->process($_POST)
    ->json();

ztevie · June 2017

SCENARIO 1, I DO NOT SET ANY VALUES BY MYSELF. HERE IS WHERE I GET PROBLEMS AND FIELDS GET UPDATED WITH DEFAULT DB VALUES UNDER UPDATE 'holes'...

Post Form Data from client:

action:create
data[0][holeactions][hole_id]:7914
data[0][holeactions][holeaction_depthprod]:
data[0][holeactions][holeaction_depthredrill]:
data[0][holeactions][holeaction_depthupdrill]:
data[0][holeactions][holeaction_status]:
data[0][holeactions][bit_id]:
data[0][holes][hole_redrill]:
data[0][holes][hole_drilled]:
data[0][holes][hole_boostered]:
data[0][holes][hole_water]:
data[0][holes][hole_sylta]:
data[0][holes][hole_left]:
data[0][holes][hole_info]:
data[0][holes][hole_active]:
sid:12
rid:1
bid:90
setStart:false
setEnd:false

And debug sql result:

{
    "debugSql": [{
        "query": "UPDATE  `drillrigs` SET  `blast_id` = :blast_id WHERE `rig_id` = :where_0 ",
        "bindings": [{
            "name": ":blast_id",
            "value": "90",
            "type": null
        }, {
            "name": ":where_0",
            "value": "1",
            "type": null
        }]
    }, {
        "query": "INSERT INTO  `holeactions`  ( `hole_id`, `shift_id`, `bit_id` ) VALUES (  :hole_id,  :shift_id,  :bit_id )",
        "bindings": [{
            "name": ":hole_id",
            "value": "7914",
            "type": null
        }, {
            "name": ":shift_id",
            "value": "12",
            "type": null
        }, {
            "name": ":bit_id",
            "value": "",
            "type": null
        }]
    }, {
        "query": "SELECT  * FROM  `holes` WHERE `hole_id` = :where_0 ",
        "bindings": [{
            "name": ":where_0",
            "value": "7914",
            "type": null
        }]
    }, {
        "query": "UPDATE  `holes` SET  `hole_active` = :hole_active, `hole_drilled` = :hole_drilled, `hole_redrill` = :hole_redrill, `hole_sylta` = :hole_sylta, `hole_water` = :hole_water, `hole_left` = :hole_left, `hole_info` = :hole_info WHERE `hole_id` = :where_0 ",
        "bindings": [{
            "name": ":hole_active",
            "value": "",
            "type": null
        }, {
            "name": ":hole_drilled",
            "value": "",
            "type": null
        }, {
            "name": ":hole_redrill",
            "value": "",
            "type": null
        }, {
            "name": ":hole_sylta",
            "value": 0,
            "type": null
        }, {
            "name": ":hole_water",
            "value": "",
            "type": null
        }, {
            "name": ":hole_left",
            "value": "",
            "type": null
        }, {
            "name": ":hole_info",
            "value": "",
            "type": null
        }, {
            "name": ":where_0",
            "value": "7914",
            "type": null
        }]
    }, {
        "query": "SELECT  `holeactions`.`holeaction_id` as 'holeactions.holeaction_id', `holeactions`.`hole_id` as 'holeactions.hole_id', `holeactions`.`bit_id` as 'holeactions.bit_id', `holeactions`.`holeaction_status` as 'holeactions.holeaction_status', `holeactions`.`holeaction_start` as 'holeactions.holeaction_start', `holeactions`.`holeaction_end` as 'holeactions.holeaction_end', `holeactions`.`userid_started` as 'holeactions.userid_started', `holeactions`.`userid_ended` as 'holeactions.userid_ended', `holeactions`.`holeaction_depthprod` as 'holeactions.holeaction_depthprod', `holeactions`.`holeaction_depthredrill` as 'holeactions.holeaction_depthredrill', `holeactions`.`holeaction_depthupdrill` as 'holeactions.holeaction_depthupdrill', `holes`.`hole_id` as 'holes.hole_id', `holes`.`hole_nr` as 'holes.hole_nr', `holes`.`hole_active` as 'holes.hole_active', `holes`.`hole_depth` as 'holes.hole_depth', `holes`.`hole_drilled` as 'holes.hole_drilled', `holes`.`hole_boostered` as 'holes.hole_boostered', `holes`.`hole_redrill` as 'holes.hole_redrill', `holes`.`hole_sylta` as 'holes.hole_sylta', `holes`.`hole_water` as 'holes.hole_water', `holes`.`hole_left` as 'holes.hole_left', `holes`.`hole_info` as 'holes.hole_info', `holes`.`blast_id` as 'holes.blast_id', `blasts`.`blast_name` as 'blasts.blast_name' FROM  `holeactions` LEFT JOIN `holes` ON `holes`.`hole_id` = `holeactions`.`hole_id`  LEFT JOIN `blasts` ON `blasts`.`blast_id` = `holes`.`blast_id`  LEFT JOIN `shifts` ON `shifts`.`shift_id` = `holeactions`.`shift_id` WHERE `holeactions`.`shift_id` = :where_0 AND `holeactions`.`holeaction_id` = :where_1 ",
        "bindings": [{
            "name": ":where_0",
            "value": "12",
            "type": null
        }, {
            "name": ":where_1",
            "value": "40",
            "type": null
        }]
    }]
}

SCENARIO 2, I PICK UP THE VALUES FROM DATABASE AND SEND THEM BACK AGAIN SO THEY ARE UPDATED WITH THE RIGHT VALUES

Post Form Data from client:

action:create
data[0][holeactions][hole_id]:7914
data[0][holeactions][holeaction_depthprod]:
data[0][holeactions][holeaction_depthredrill]:
data[0][holeactions][holeaction_depthupdrill]:
data[0][holeactions][holeaction_status]:
data[0][holeactions][bit_id]:89
data[0][holes][hole_redrill]:0
data[0][holes][hole_drilled]:0
data[0][holes][hole_boostered]:
data[0][holes][hole_water]:0
data[0][holes][hole_sylta]:0.0
data[0][holes][hole_left]:0
data[0][holes][hole_info]:
data[0][holes][hole_active]:1
sid:12
rid:1
bid:90
setStart:false
setEnd:false

And debug sql result:

{
    "debugSql": [{
        "query": "UPDATE  `drillrigs` SET  `blast_id` = :blast_id WHERE `rig_id` = :where_0 ",
        "bindings": [{
            "name": ":blast_id",
            "value": "90",
            "type": null
        }, {
            "name": ":where_0",
            "value": "1",
            "type": null
        }]
    }, {
        "query": "INSERT INTO  `holeactions`  ( `hole_id`, `shift_id`, `bit_id` ) VALUES (  :hole_id,  :shift_id,  :bit_id )",
        "bindings": [{
            "name": ":hole_id",
            "value": "7914",
            "type": null
        }, {
            "name": ":shift_id",
            "value": "12",
            "type": null
        }, {
            "name": ":bit_id",
            "value": "89",
            "type": null
        }]
    }, {
        "query": "SELECT  * FROM  `holes` WHERE `hole_id` = :where_0 ",
        "bindings": [{
            "name": ":where_0",
            "value": "7914",
            "type": null
        }]
    }, {
        "query": "UPDATE  `holes` SET  `hole_active` = :hole_active, `hole_drilled` = :hole_drilled, `hole_redrill` = :hole_redrill, `hole_sylta` = :hole_sylta, `hole_water` = :hole_water, `hole_left` = :hole_left, `hole_info` = :hole_info WHERE `hole_id` = :where_0 ",
        "bindings": [{
            "name": ":hole_active",
            "value": "1",
            "type": null
        }, {
            "name": ":hole_drilled",
            "value": "0",
            "type": null
        }, {
            "name": ":hole_redrill",
            "value": "0",
            "type": null
        }, {
            "name": ":hole_sylta",
            "value": 0,
            "type": null
        }, {
            "name": ":hole_water",
            "value": "0",
            "type": null
        }, {
            "name": ":hole_left",
            "value": "0",
            "type": null
        }, {
            "name": ":hole_info",
            "value": "",
            "type": null
        }, {
            "name": ":where_0",
            "value": "7914",
            "type": null
        }]
    }, {
        "query": "SELECT  `holeactions`.`holeaction_id` as 'holeactions.holeaction_id', `holeactions`.`hole_id` as 'holeactions.hole_id', `holeactions`.`bit_id` as 'holeactions.bit_id', `holeactions`.`holeaction_status` as 'holeactions.holeaction_status', `holeactions`.`holeaction_start` as 'holeactions.holeaction_start', `holeactions`.`holeaction_end` as 'holeactions.holeaction_end', `holeactions`.`userid_started` as 'holeactions.userid_started', `holeactions`.`userid_ended` as 'holeactions.userid_ended', `holeactions`.`holeaction_depthprod` as 'holeactions.holeaction_depthprod', `holeactions`.`holeaction_depthredrill` as 'holeactions.holeaction_depthredrill', `holeactions`.`holeaction_depthupdrill` as 'holeactions.holeaction_depthupdrill', `holes`.`hole_id` as 'holes.hole_id', `holes`.`hole_nr` as 'holes.hole_nr', `holes`.`hole_active` as 'holes.hole_active', `holes`.`hole_depth` as 'holes.hole_depth', `holes`.`hole_drilled` as 'holes.hole_drilled', `holes`.`hole_boostered` as 'holes.hole_boostered', `holes`.`hole_redrill` as 'holes.hole_redrill', `holes`.`hole_sylta` as 'holes.hole_sylta', `holes`.`hole_water` as 'holes.hole_water', `holes`.`hole_left` as 'holes.hole_left', `holes`.`hole_info` as 'holes.hole_info', `holes`.`blast_id` as 'holes.blast_id', `blasts`.`blast_name` as 'blasts.blast_name' FROM  `holeactions` LEFT JOIN `holes` ON `holes`.`hole_id` = `holeactions`.`hole_id`  LEFT JOIN `blasts` ON `blasts`.`blast_id` = `holes`.`blast_id`  LEFT JOIN `shifts` ON `shifts`.`shift_id` = `holeactions`.`shift_id` WHERE `holeactions`.`shift_id` = :where_0 AND `holeactions`.`holeaction_id` = :where_1 ",
        "bindings": [{
            "name": ":where_0",
            "value": "12",
            "type": null
        }, {
            "name": ":where_1",
            "value": "41",
            "type": null
        }]
    }]
}

As you see, all set_edit fields are included in the UPDATE, set(false) are not...
I do not send holes.hole_id, but it seems Editor is clever enough to get the hole_id from holeactions.hole_id (since they're linked together in the join?) and for some reason want to update the set_edit fields?
In preEdit I do set the holes.hole_id but it shouldn't enter into that function on create?

allan · August 2017

Apologies for dropping the ball on this one. We didn't get to the bottom of it here - but I have a feeling we might have in a private message or e-mail?

Allan

Field::SET_EDIT will update fields on CREATE aswell...

Field::SET_EDIT will update fields on CREATE aswell...

Answers

Howdy, Stranger!

Categories

DataTables

Field::SET_EDIT will update fields on CREATE aswell...

Field::SET_EDIT will update fields on CREATE aswell...

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables