using preCreate to create multiple records using Join

Khalid Teli

When I create a new row in a table using editor, just before inserting a new record into the datatable I want to JOIN the table to another table.
For example when I create a new row in Products table, before creating row, is it possible to call preCreate event (I assume) and link it to the Member table , so each product is associated with its member , which will eventually result in inserting/creating multiple records in database

INSERT INTO products (product_code,contract_prod,member_name) 
   select products.product_code , products.contract_prod, members.member_name FROM cproducts JOIN members;

This is how it will be done using normal SQL query but not sure how can this be implement using datatable

Khalid Teli

I used post create and it works,However, how can I use Join statement inside here?

->on( 'postCreate', function ( $editor, $id, $values, $row ) {
    $editor->db()->insert( 'crg_directory_contracts', array(
        'product_code_fk'   => '90300',
        'contract_prod' => 'Edit users table row',
        'member_name' => 'abc',
        'start_date'    => '2020-12-12',
        'end_date'   => '2020-10-12'
    ) );
} )

allan

There isn't a JOIN option for the insert() method I'm afraid. You'd need to use $editor->db()->sql() which let's you construct an SQL statement manually. Be sure to use the bind method if you are allowing user input data here. Full information on the API is here.

Just to confirm my understanding, you want to insert into both products and crg_directory_contracts with the single submit?

Allan

Khalid Teli

Hi Again,
Please ignore my previous comments, I managed to make it work using RAW SQLwith below code

    ->on( 'postCreate', function ( $editor, $id, $values ) use ( $db ) {
         $db->raw()


  ->exec( 'INSERT INTO crg_directory_contracts (product_code_fk,contract_prod,member_name,start_date,end_date) 
   select crg_directory_contracts.product_code_fk , crg_directory_contracts.contract_prod, crg_members.member_name,crg_directory_contracts.start_date, crg_directory_contracts.end_date  FROM crg_directory_contracts JOIN crg_members');
    })

Thanks for this amazing product This is just beautiful

Khalid Teli

@allan
Thank you very much.
Sorry, there was a mistake in my query.

I have two tables crg_directory_contracts and crg_members . crg_members holds the list of 12 different members. So when I create record in crg_directory_contracts it should combine crg_members table and link each row in crg_directory_contracts with data in members table.

For example , if I create one row in crg_directory_contracts it should actually create 12 rows ,linking the row with each members

I achieved this by using the code below:

 ->on( 'postCreate', function ( $editor, $id, $values ) use ( $db ) {
       $db->raw()


->exec( 'INSERT INTO crg_directory_contracts (product_code_fk,contract_prod,member_name,start_date,end_date)
 select crg_directory_contracts.product_code_fk , crg_directory_contracts.contract_prod, crg_members.member_name,crg_directory_contracts.start_date, crg_directory_contracts.end_date  FROM crg_directory_contracts JOIN crg_members');
  })

Khalid Teli

dear @allan
If you see in the code below , I used the bind method with $editor->db()->sql() However, the issue I am facing is , I don't want to feeds the values from datatble, I just need to capture the value generated using CREATE method. How can I capture the form data when a row is created and feed only those values to INSERT statement?

->on( 'postCreate', function ( $editor, $id, $values ) use ( $db ) {
         $db->raw()

  ->bind( ':a',     $values["product_code_fk"] )
   ->bind( ':b',      $values["contract_prod"] )
  ->bind( ':c',          $values["start_date"] )
    ->bind( ':d',          $values["end_date"] )

  ->exec( 'INSERT INTO crg_directory_contracts (product_code_fk,contract_prod,member_name,start_date,end_date) 
   select :a , :b, crg_members.member_name,:c, 
   :d  FROM crg_directory_contracts JOIN crg_members');
    })

Khalid Teli

@allan
please ignore my previous comments. All is working fine now.
There was a just error in my sql query

allan

Super - good to hear you got it working!

Allan