Editing a SQL VIEW containing GROUP BY Clause

Khalid Teli

I have created a simple SQL VIEW and used GROUP BY clause and read that using datatables.

Select member_name, product_name, product_code from contract_return_bal_forecast group by product_name

I know it is not possible to edit the records when using GROUP BY clause because of the unique id column complexities.

However, I need to find a way to edit the records on the table created by VIEW which uses GROUP BY clause.

What is the best way to do it?

I guess the only way will be save the results in new table and then use editor on that newly created table?
or is there any way in datatables I can do it?

Thank you

allan

Let's step back for a second and confirm what it is that you need. Are you saying that you want to be able to edit a row in the grouped table - therefore possibly updating multiple rows in the original table?

If so, you would need some way to either identify the group, or to identify the rows within that group. Then you can apply an UPDATE statement to it based on whatever input you have.

That is not something our Editor libraries provide for I'm afraid.

Allan

Khalid Teli

@allan
Thank you.
I don't want to edit row in the grouped table.
What I want is , add additional information to the grouped table.

In other words, if sql View with group by clause gives me a table with 5 columns, I want to add two more columns where I can add add other relevant information .
(In the SQL query above, for each grouped product returned , I need to add column were Supplier can add price for those products as a group)

Khalid Teli

@allan

I was going through it and it seems, this suggestion given by you will work

If so, you would need some way to either identify the group, or to identify the rows within that group. Then you can apply an UPDATE statement to it based on whatever input you have.

However, I am not sure how to apply UPDATE statement.
I understand the logic but don't not know how and where to implement it.
Is it possible to provide a very basic example or hint?
That will he highly appreciated.

Thank you

allan

I'm not sure how to apply an UPDATE statement in this case either! You say:

I need to add column were Supplier can add price for those products as a group)

But where should it write the value to? The group might contain information from multiple rows, so should it write to them all, or to a new record or something else?

Allan

Khalid Teli

@allan
Thank you

When adding the information to the group, it should reflect in all the rows associated with that group.

In the image below, If I add the price for the the two groups (group1 and group2) it should reflect in all the rows which are associated with these two groups.

rf1234

It would be very helpful to see an E/R-diagram of your database. But let me try guessing ... You have a table contract_return_bal_forecast that has all of those columns: product_code, product_name, pack_size, contract_prod, mfl, price.

Since you can't update the GROUP BY view you would need to set all the Editor fields on the back end to false. Then on "validatedEdit" (i.e. after all of the field validations have passed successfully) you can update the real table with your own SQL or using Editor's ->update method.

Editor::inst( $db, 'yourView' ) 
    ->field(
        Field::inst( 'member_name' )->set( false ),
        Field::inst( 'product_name' )->set( false ),
        Field::inst( 'product_code' )->set( false ),
        Field::inst( 'pack_size' )->set( false ),
        Field::inst( 'contract_prod' )->set( false ),  
        Field::inst( 'mfl' )->set( false ),
        Field::inst( 'price' )->set( false )
    )
->on( 'validatedEdit', function ( $editor, $id, $values ) use ( $db ) {
   $res = $db->update( 'contract_return_bal_forecast ', array( 
                'pack_size'  => $values["pack_size"],
                'contract_prod' =>$values["contract_prod"],
                'mfl '    => $values["mfl "],
                'price'    => $values["price"]
            ), array( 'product_name' => $values["product_name"] ) ); 
})
->process($_POST)
->json();

allan

If I add the price for the the two groups (group1 and group2) it should reflect in all the rows which are associated with these two groups.

but you originally said:

I know it is not possible to edit the records when using GROUP BY clause because of the unique id column complexities.

That point still stands. Our Editor libraries on the server-side do not directly support what you are trying t do.

Your only option would be to have some custom code on the server-side which would be sent a list of the row id's that need to be updated, and then update them that way. @rf1234's code is a cunning way of doing that, but you need to make sure you get your grouping correct.

Allan

Khalid Teli

@allan
Thank you very much. I think I understand what you are saying.
I need to get row id's of all rows in a group and use an update statement based on those row id's.

However, I will need to get my head around and see how I can get the id's of rows in an group and update them accordingly.

@rf1234 thanks for the piece of code, it works perfect.

You guys have been of great help and the plugin is amazing. Thank you very much!
For the support, I tried to make a donation using this link https://legacy.datatables.net/donate but it shows page not found. Can you provide me a working link please?

colin

Thank you, that's very kind. This page here, https://datatables.net/purchase/index , has a few options for donating.

Colin

Khalid Teli

@rf1234
As, I mentioned earlier the piece of code works fine but how would you proceeded when a SQL VIEW includes LEFT JOINS. As in my SQL VIEW product_code and product_name comes from a different table called products which is joined to 'contract_return_bal_forecast using LEFT join :

in the code below , let us say, we are updating contract_return_bal_forecast table where product name in group = product name in the table array( 'product_name' => $values["product_name"] )

AND if the product_name come from different table and is joined using left join like this
->leftJoin( 'products', 'products.product_code', '=', 'contract_return_bal_forecast.product_code_fk' )

Editor::inst( $db, 'yourView' )
    ->field(
        Field::inst( 'member_name' )->set( false ),
        Field::inst( 'product_name' )->set( false ),
        Field::inst( 'product_code' )->set( false ),
        Field::inst( 'pack_size' )->set( false ),
        Field::inst( 'contract_prod' )->set( false ), 
        Field::inst( 'mfl' )->set( false ),
        Field::inst( 'price' )->set( false )
    )
->on( 'validatedEdit', function ( $editor, $id, $values ) use ( $db ) {
   $res = $db->update( 'contract_return_bal_forecast ', array(
                'pack_size'  => $values["pack_size"],
                'contract_prod' =>$values["contract_prod"],
                'mfl '    => $values["mfl "],
                'price'    => $values["price"]
            ), array( 'product_name' => $values["product_name"] ) );
})
->process($_POST)
->json();

In the case when I use joins, it shows unknown column 'product_name'

Khalid Teli

@allan

Your only option would be to have some custom code on the server-side which would be sent a list of the row id's that need to be updated, and then update them that way. @rf1234's code is a cunning way of doing that, but you need to make sure you get your grouping correct.

In terms of my database when I group by any column name (for example product_name). each grouped results will be differentiated by two variables product_name and start_date (which is start date of a contract). Which means everytime a group by clause is called each returned group will be differentiated by product_name and start_date which will be a kind of unique id for group.

so , in order to make the grouping correct can we use something like this in where clause

->on( 'validatedEdit', function ( $editor, $id, $values ) use ( $db ) {
   $res = $db->update( 'contract_return_bal_forecast ', array(
                'pack_size'  => $values["pack_size"],
                'contract_prod' =>$values["contract_prod"],
                'mfl '    => $values["mfl "],
                'price'    => $values["price"]
            ), array( 'product_name' => $values["product_name"] ) , array( 'start_date' => $values["start_date"] ));
})

rf1234

if you have multiple tables you would need to prefix the column names with the table name anyway I guess. Like 'yourView.member_name' and 'products.product_name'. If you don't do that Editor doesn't know that product_name is in a different table. It will search for it in "yourView" where it isn't found.

Then $values is different as well. Like this: $values["products"]["product_name"] etc.

It is good practice to always prefix the column names with the table name - even if you only use one table. Then you don't get confused ... Since I never use unprefixed column names I can't guarantee though ...

rf1234

for more complex queries I would use the -.>raw() method. Here is an example:
https://datatables.net/forums/discussion/comment/179968/#Comment_179968

Khalid Teli

@rf1234

i used the prefix name for column names and everything works fine. However, on validateEdit function when using the prefix name as shown in code below it shows me the error

Notice: Undefined index: product_name in C:\wamp64\www\Editor-PHP-1.9.4\controllers\contract_return_forecast_supplier_fetch.php on line 67

I assume I will have to use join inside the editvalidate function and then use prefix. I tried different ways but couldn't manage to work it out. Can you please help Thank you
suppliergroupby2 is name of the sql VIEW

Editor::inst( $db, 'suppliergroupby2', 'contract_id' )

    ->field(


        Field::inst( 'suppliergroupby2.contract_id' )->set( false ),
        Field::inst( 'products.product_code','product_code' )->set( false ),
        Field::inst( 'products.product_name','product_name' )->set( false ),
        Field::inst( 'suppliergroupby2.member_name','member_name' )->set( false ),
        Field::inst( 'suppliergroupby2.start_date' ,'start_date')->set( false ),
        Field::inst( 'supplier.supplier_name','supplier_name' )->set( false ),
        Field::inst( 'suppliergroupby2.contract_prod','contract_prod' )->set( false ),
        Field::inst( 'products.pack_size','pack_size' )->set( false ),
        Field::inst ( '(suppliergroupby2.sumone + suppliergroupby2.sumtwo + suppliergroupby2.sumthree + suppliergroupby2.sumfour + suppliergroupby2.sumfive)', 'total_return' )
                ->set(false),
        Field::inst( 'suppliergroupby2.opt_one' )->set( false ),
        Field::inst( 'suppliergroupby2.opt_two' )->set( false ),
        Field::inst( 'suppliergroupby2.opt_three' )->set( false ),
        Field::inst( 'suppliergroupby2.opt_four' )->set( false ),
        Field::inst( 'suppliergroupby2.opt_five' )->set( false ),
        Field::inst( 'suppliergroupby2.sumone', 'sumone' )->set( false ),
        Field::inst( 'suppliergroupby2.sumtwo','sumtwo' )->set( false ),
        Field::inst( 'suppliergroupby2.sumthree','sumthree' )->set( false ),
        Field::inst( 'suppliergroupby2.sumfour' ,'sumfour')->set( false ),
        Field::inst( 'suppliergroupby2.sumfive','sumfive' )->set( false ),

    )

        ->on( 'validatedEdit', function ( $editor, $id, $values ) use ( $db ) {
           $res = $db->update( 'contract_return_bal_forecast ', array(

                        'opt_one'    => $values["opt_one"],
                'opt_two '    => $values["opt_two"],
                'opt_three '    => $values["opt_three"],
                'opt_four '    => $values["opt_four"],
                   'opt_five '    => $values["opt_five"],
                    ), array( 'product.product_name' => $values["product"]["product_name"]));

        })

    ->leftJoin( 'products', 'products.product_code', '=', 'suppliergroupby2.product_code_fk' )

          ->leftJoin( 'supplier', 'supplier.supplier_id', '=', 'products.supplier_id_fk' )
        ->debug(true)
    ->process( $_POST )
    ->json();
  //}

I assums I will have to do something like this but not really sure

->on( 'validatedEdit', function ( $editor, $id, $values ) use ( $db ) {
           $res = $db->update( 'contract_return_bal_forecast '

                          ->leftJoin( 'products', 'products.product_code', '=', 'contract_return_bal_forecast.product_code_fk' ),
               array(
                        'contract_return_bal_forecast.opt_one'    => $values["contract_return_bal_forecast"]["opt_one"],

                        'contract_return_bal_forecast.opt_two '    => $values["contract_return_bal_forecast"]["opt_two"],

                        'contract_return_bal_forecast.opt_three '    => $values["contract_return_bal_forecast"]["opt_three"],

                        'contract_return_bal_forecast.opt_four '    => $values["contract_return_bal_forecast"]["opt_four"],

                        'contract_return_bal_forecast.opt_five '    => $values["contract_return_bal_forecast"]["opt_five"],

                    ), array( 'products.product_name' => $values["products"]["product_name"]));

        })

rf1234

Your update statement is wrong. You are referring to a different table ("product") but are not joining it.

Instead of $db->update you could use the raw method to execute your own SQL like this:

$db->raw()
   ->bind( ':opt_one',          $values["suppliergroupby2"]["opt_one"] ) 
   ->bind( ':opt_two',          $values["suppliergroupby2"]["opt_two"] ) 
   ->bind( ':product_name',     $values["products"]["product_name"] )      
   ->exec( 'UPDATE contract_return_bal_forecast a 
        INNER JOIN products b    ON    a.product_code_fk = b.product_code
               SET a opt_one        = :opt_one,
                   a opt_two        = :opt_two
             WHERE b.product_name   = :product_name' );

Don't know what this is:

Field::inst( 'products.product_code','product_code' )->set( false ),
Field::inst( 'products.product_name','product_name' )->set( false ),

If that is some kind of aliasing then $values["products"]["product_name"] might not work. You might have to use $values["product_name"] instead.

rf1234

Ok, I found this in the docs that I wasn't aware of:

So, you would definitely need to use $values["product_name"] then.

Khalid Teli

@rf1234 Thank you
When using this

 Field::inst( 'suppliergroupby2.opt_one')->set( false ),

And then this 

  ->bind( ':opt_one',   $values["suppliergroupby2"]["opt_one"] ) )

it shows invalid
Notice: Undefined index: suppliergroupby2.opt_one

Therefore I changed it to

Field::inst( 'suppliergroupby2.opt_one','opt_one' )->set( false ),

and used 
   ->bind( ':opt_one',   $values["opt_one"]) )

This works fine .However, after updating the opt_one = 'xxx value' , it sjhows me error:

debug: [{,…}]
error: "An SQL error occurred: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'opt_one = '12',↵ a opt_two = ''↵ WHE' at line 3"

 ->on( 'validatedEdit', function ( $editor, $id, $values ) use ( $db ) {
          $db->raw()
   ->bind( ':opt_one',          $values["opt_one"] )
   ->bind( ':opt_two',          $values["opt_two"] )
   ->bind( ':product_name',     $values["product_name"] )     
   ->exec( 'UPDATE contract_return_bal_forecast a
        INNER JOIN products b    ON    a.product_code_fk = b.product_code
               SET a opt_one        = :opt_one,
                   a opt_two        = :opt_two
             WHERE b.product_name   = :product_name' );
        })

allan

Should SET a opt_one = :opt_one, be:

SET a.opt_one        = :opt_one,

<?php I'm not sure what your SQL server will make of `a opt_one`. ?>

Or if opt_one is unique as a name across the two tables just use opt_one.

Allan

rf1234

Sure, Allan is right! I forgot the periods.

->on( 'validatedEdit', function ( $editor, $id, $values ) use ( $db ) {
         $db->raw()
  ->bind( ':opt_one',          $values["opt_one"] )
  ->bind( ':opt_two',          $values["opt_two"] )
  ->bind( ':product_name',     $values["product_name"] )    
  ->exec( 'UPDATE contract_return_bal_forecast a
       INNER JOIN products b    ON    a.product_code_fk = b.product_code
              SET a.opt_one        = :opt_one,
                  a.opt_two        = :opt_two
            WHERE b.product_name   = :product_name' );

I have a very similar SQL statement in my code. I remember it was important to have the INNER JOIN before SET. Otherwise it didn't work ... and of course you mustn't forget the periods between alias and field while you can't have periods between table name and alias. This can be confusing at times ...

Khalid Teli

@allan @rf1234

While using
SET a.opt_one = :opt_one,

It give some error ,

error: "An SQL error occurred: SQLSTATE[HY000]: General error: 1288 The target table suppliergroupby2 of the UPDATE is not updatable"

somehow it is confusing between suppliergroupby2 and contract_return_bal_forecast

    >on( 'validatedEdit', function ( $editor, $id, $values ) use ( $db ) {
             $db->raw()
      ->bind( ':opt_one',          $values["opt_one"] )
      ->bind( ':opt_two',          $values["opt_two"] )
      ->bind( ':product_name',     $values["product_name"] )   
      ->exec( 'UPDATE contract_return_bal_forecast a
           INNER JOIN products b    ON    a.product_code_fk = b.product_code
                  SET a.opt_one        = :opt_one,
                      a.opt_two        = :opt_two
                WHERE b.product_name   = :product_name' );

rf1234

Are you sure the error is caused by this statement? And not by Editor?

Khalid Teli

Hi @rf1234
I am sorry if I ask too many stupid questions. How would I know if the error is by editor? not the particular statement?

Having said that, Just for the test purpose, I used the simple Update sql command without where clause and it is working as it is supposed to be . On this basis can we say there is no problem with editor?

->on( 'validatedEdit', function ( $editor, $id, $values ) use ( $db ) {
           $res = $db->update( 'contract_return_bal_forecast ',

                        'opt_one'    => $values["opt_one"]
                'opt_two '    => $values["opt_two"],

                    ) );

        })

Thank you

rf1234

On this basis can we say there is no problem with editor?

No, not really. suppliergroupby2 is not in the UPDATE statement. Unless contract_return_bal_forecast is some kind of alias of suppliergroupby2 the UPDATE statement cannot have caused this error.

It is much more likely that something in your Editor instance caused the error, e.g. a missing or syntactically wrong ->set( false ).

This here from your code above has a syntax error that your IDE should have shown you and which you should have seen in your browser's console, too.

Field::inst( 'suppliergroupby2.sumthree','sumthree' )->set( false ),
        Field::inst( 'suppliergroupby2.sumfour' ,'sumfour')->set( false ),
        Field::inst( 'suppliergroupby2.sumfive','sumfive' )->set( false ),
 
    )

The error is the final comma after the last ->set( false ). And surprise: This is exactly in conjunction with a field definition of the view in question: suppliergroupby2. That makes me suspicious that Editor caused the error.

In addition you could have tested the UPDATE statement interactively with MySQLWorkbench, PHPMyAdmin or whatever you use. All you would have needed to do that is to copy this into the tool and replace the host variables with real values.

UPDATE contract_return_bal_forecast a
       INNER JOIN products b    ON    a.product_code_fk = b.product_code
              SET a.opt_one        = 1
                  a.opt_two        = 2
            WHERE b.product_name   = 'yourProduct'

That would have shown you immediately whether or not the UPDATE statement has a syntax error. I would have done that too if I had your database available. It is good practice to develop all of your SQL interactively and only copy tested statements into your code as embedded SQL because IDEs won't show you SQL syntax errors.

If that doesn't help either I suppose all you could do is to give me or @allan a link to a test page showing the issue.

Khalid Teli

@rf1234

I guess It is working now, updating all the fields in contract_return_bal_forecast
based onvalues given in 'suppliergroupby2 view.

Only issue is every time I update a row, I have to manually input the product name instead of it reading itself from the field.

below is my code and it works fine.

<?php

/*
 * Example PHP implementation used for the index.html example
 */

// DataTables PHP library
include( "../lib/DataTables.php" );

// 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,
  DataTables\Editor\ValidateOptions;


Editor::inst( $db, 'contract_return_bal_forecast' ,'contract_id')
    ->readTable('suppliergroupby2') // The VIEW to read data from

    ->field(

 Field::inst( 'product_code' ),
        Field::inst( 'product_name')

        ->options( Options::inst()
                ->table( 'products' )
                ->value( 'product_name' )
                ->label( 'product_name' )
            )
            ->validator( Validate::dbValues() ),

        Field::inst( 'member_name' ),
        Field::inst( 'start_date'),
        Field::inst( 'supplier_name' ),
        Field::inst( 'contract_prod' ),
        Field::inst( 'pack_size' ),
        Field::inst ( '(sumone + sumtwo + sumthree + sumfour + sumfive)', 'total_return' ),
        Field::inst( 'opt_one' ),
        Field::inst( 'opt_two'),
        Field::inst( 'opt_three' ),
        Field::inst( 'opt_four' ),
        Field::inst( 'opt_five' ),
        Field::inst( 'sumone' ),
        Field::inst( 'sumtwo' ),
        Field::inst( 'sumthree' ),
        Field::inst( 'sumfour'),
        Field::inst( 'sumfive' ),
        Field::inst( 'opt_one_price'),
        Field::inst( 'opt_two_price'),
        Field::inst( 'opt_three_price' ),
        Field::inst( 'opt_four_price' ),
        Field::inst( 'opt_five_price' )
    )

      ->on( 'validatedEdit', function ( $editor, $id, $values ) use ( $db ) {
         $db->raw()
  ->bind( ':opt_one_price',          $values["opt_one_price"] )
  ->bind( ':opt_two_price',          $values["opt_one_price"] )
  ->bind( ':product_name',     $values["products"]["product_name"] )   
  ->exec( 'UPDATE contract_return_bal_forecast a
       INNER JOIN products b    ON    a.product_code_fk = b.product_code
              SET a.opt_one_price        = :opt_one_price,
                  a.opt_two_price        = :opt_two_price
            WHERE b.product_name   = :product_name' );
    })

    ->debug(true)
  ->process( $_POST )
  ->json();

The only issue with this is when I use

  Field::inst( 'product_name')

            ->options( Options::inst()
                    ->table( 'products' )
                    ->value( 'product_name' )
                    ->label( 'product_name' )
                )
                ->validator( Validate::dbValues() ),

and then on client side when I use

  editor = new $.fn.dataTable.Editor( {
        "ajax": "../../controllers/contract_return_forecast_supplier_fetch.php",
        "table": "#contracts",
        "fields": [
         {
                    label: "Product Name:",
                    name: "products.product_name"

                },

It does not read the product name, I have to manually put it and it works like that.

rf1234

I think there are a couple of problems with your approach.
- Why do you need the grouping approach and why do you need to do a mass update? The reason is that your data model is not normalized meaning you save a lot of redundant data like product name opt_one_price etc. multiple times.
- If you had your data model structured properly according to the principles of entitiy relationship modeling you wouldn't need any of this. You would simply join your tables and done.
- Having redundancies like this will cause serious trouble because you will have to control the redundancies "manually". You will make mistakes and you will face a mess in your database. I am pretty sure this will happen.

But anyway ... looking at your code there are a few inconsistencies:
- PHP: You are not joining table products but you seem to be willing to save the product name redundantly ... With proper data modeling you would only save the id of the product as a foreign key. With your approach you will have update anomalies: If the product name changes you will have to manually make sure that all of the redundantly saved product names across all of your tables are updated! That will be difficult - and completely unnecessary with proper modeling.
- Javascript: You want to use a field called "products.product_name" that you don't retrieve server side. That can't work.

All in all I think you need help ... but not the kind we can provide here in the forum I am afraid. You would need training with data modeling first, at least that would be my recommendation. Good luck! I keep my fingers crossed.

Khalid Teli

@rf1234

• Why do you need the grouping approach and why do you need to do a mass update?

I will try my best to expain using the pictures below.

I have two tables in database Products and contract_return_bal_forecast. Products contains all the basic product information and other table contains the contract terms and price for the products under contract

In contract_return_bal_forecast table:
Opt_one, opt_two and opt three corresponds to different product packing types.
For example, opt_one can be = Mixed full container
Opt_two can be = 2-4 pallet size
and Opt_one_price, Opt_two_price corresponds to the price for those quantities

Note: Product_code and Product_Name (and any other relevant information) comes from products table using foreign key (Product_code_fk).

Now we must send this list to suppliers to get price, but we send them as a group not individual product. That is why I use group_by clause to group products by name

So, I create a groupbysupoplier view, which groups by product_name and looks like below and

This table then gets send to suppliers to put price in, once the price gets inserted then I want this price to be automatically updated for each individual product in Contract Return Balance Forecast table.

But anyway ... looking at your code there are a few inconsistencies:
- PHP: You are not joining table products but you seem to be willing to save the product name redundantly

in the sql view I created , I joined the products table with contracts_return_bal_forecast table using

left joincrg_intranet.productson((crg_intranet.contract_return_bal_forecast.product_code_fk=crg_intranet.products.product_code)

So when I was reading the this view from php script , I was expecting the product_name to fetch using this piece of code in sql view crg_intranet`.`products`.`product_name` AS `product_name which didn't work.

I am not sure, if I need to left join it again in php script? that is why I was confused

Khalid Teli

Hi @rf1234
Everytghing works fine now Thank you very much.
There were three things that were causing the problem

1)Using the wrong foreign key product_code instead of using product_code_fk

2) not setting the fields values read from SQL View to false ->set(false),which created this error
error: "An SQL error occurred: SQLSTATE[HY000]: General error: 1288 The target table suppliergroupby2 of the UPDATE is not updatable"

3) Onlient side in editor field not using all the fields used inside update command on serverside.

After fixing these two problems, It works fine

server side code:

<?php

/*
 * Example PHP implementation used for the index.html example
 */

// DataTables PHP library
include( "../lib/DataTables.php" );

// 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,
  DataTables\Editor\ValidateOptions;


Editor::inst( $db, 'suppliergroupby2' ,'contract_id')

    ->field(

        Field::inst( 'product_code_fk' )->set(false),
        Field::inst( 'product_name')->set(false),
        Field::inst( 'member_name' )->set(false),
        Field::inst( 'start_date')->set(false),
        Field::inst( 'supplier_name' )->set(false),
        Field::inst( 'contract_prod' )->set(false),
        Field::inst( 'pack_size' )->set(false),
        Field::inst( 'opt_one_price')->set(false),
        Field::inst( 'opt_two_price')->set(false)

    )

      ->on( 'validatedEdit', function ( $editor, $id, $values ) use ( $db ) {
         $db->raw()
  ->bind( ':opt_one_price',          $values["opt_one_price"] )
  ->bind( ':opt_two_price',          $values["opt_two_price"] )
  ->bind( ':product_name',           $values["product_name"] )  
  ->bind( ':start_date',              $values["start_date"] )   

  ->exec( 'UPDATE contract_return_bal_forecast a
       INNER JOIN products b    ON    a.product_code_fk = b.product_code
              SET a.opt_one_price        = :opt_one_price,
                  a.opt_two_price        = :opt_two_price
            WHERE b.product_name   = :product_name

             AND a.start_date   = :start_date');
    })

  ->debug(true)
  ->process( $_POST )
  ->json();

And on client-side in editor field it looks like

 editor = new $.fn.dataTable.Editor( {
    "ajax": "../../controllers/contract_return_forecast_supplier_fetch.php",
    "table": "#contracts",
    "fields": [
     {
                label: "Product code:",
                name: "product_code_fk"

            },
             {
                label: "Product Name:",
                name: "product_name"

            },

         {
          label: "opt one price:",
          name: "opt_one_price"
        },
         {
          label: "opt two price:",
          name: "opt_two_price"
        },
          {
          label: "Start Date:",
          name: "start_date"
        }

    ]
  } );