Compound Key using Alias names not working

Khalid Teli

Hi I am encountering a strange situation while inserting , editing and uploading a csv file to database using editor

1)
$editor = Editor::inst( $db, 'contract_return_bal_forecast', array('product_code', 'member_name') )
->fields(

        Field::inst( 'contract_return_bal_forecast.product_code', 'product_code'),

when I use a alias name for the compound keys , while trying to insert the new data it shows primary key element is not available however I am able to edit the data

2) when i use sth like this

$editor = Editor::inst( $db, 'contract_return_bal_forecast',  array('product_code', 'member_name', 'start_date') )
    ->fields(



        Field::inst( 'contract_return_bal_forecast.product_code'),

I am able to edit data, insert new data but when I try to insert data using CSV import file it does not read the fields associated with the compound keys , as shown in the figure below for product code. However, when using
Field::inst( 'contract_return_bal_forecast.product_code', 'product_code'), CSV Import seems to work fine and read all fields

rf1234

The easiest way to fix this is to get rid of compound keys. They were popular in the 1980's and 1990's, I remember that. But they were always a terrible nightmare. Replace them with auto-increment ids if you can. Good luck!

allan

Can you give me a link to your page an also show me your full PHP code please?

Allan

Khalid Teli

Hi
Before, My serverside code used to be like below. I wanted to have a primary key to be combination of three columns ('product_code', 'member_name', 'start_date') which will differentiate each row in table.

<?php
session_start();

include( "../lib/DataTables.php" );
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 = Editor::inst( $db, 'contract_return_bal_forecast',  array('product_code', 'member_name', 'start_date') )
    ->fields(

        Field::inst( 'contract_return_bal_forecast.product_code')

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

        Field::inst( 'contract_return_bal_forecast.product_name', 'product_name' )

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

        Field::inst( 'products.pack_size' , 'pack_size'),
        Field::inst( 'contract_return_bal_forecast.member_name'),
      Field::inst( 'contract_return_bal_forecast.supplier_name', 'supplier_name' )
            ->options( Options::inst()
                                            ->table( 'supplier' )
                                            ->value( 'supplier_name' )
                                            ->label( 'supplier_name' )
                                    )
                                    ->validator( Validate::dbValues() ),

        Field::inst( 'contract_return_bal_forecast.contract_prod' ,'contract_prod'),
        Field::inst( 'contract_return_bal_forecast.opt_one', 'opt_one' ),
        Field::inst( 'contract_return_bal_forecast.opt_two', 'opt_two' ),
        Field::inst( 'contract_return_bal_forecast.opt_three' , 'opt_three'),
        Field::inst( 'contract_return_bal_forecast.opt_four' , 'opt_four'),
        Field::inst( 'contract_return_bal_forecast.opt_five', 'opt_five' ),
        Field::inst ( '(contract_return_bal_forecast.opt_one + contract_return_bal_forecast.opt_two + contract_return_bal_forecast.opt_three +contract_return_bal_forecast.opt_four +contract_return_bal_forecast.opt_five)', 'total_est' )
                         ->set(false),
        Field::inst( 'contract_return_bal_forecast.opt_one_firm', 'opt_one_firm' ),
        Field::inst( 'contract_return_bal_forecast.opt_two_firm', 'opt_two_firm' ),
        Field::inst( 'contract_return_bal_forecast.opt_three_firm', 'opt_three_firm' ),
        Field::inst( 'contract_return_bal_forecast.opt_four_firm' , 'opt_four_firm'),
        Field::inst( 'contract_return_bal_forecast.opt_five_firm', 'opt_five_firm' ),
        Field::inst( 'contract_return_bal_forecast.opt_one_price' , 'opt_one_price'),
        Field::inst( 'contract_return_bal_forecast.opt_two_price' , 'opt_two_price'),
        Field::inst( 'contract_return_bal_forecast.opt_three_price' , 'opt_three_price'),
        Field::inst( 'contract_return_bal_forecast.opt_four_price' , 'opt_four_price'),
        Field::inst( 'contract_return_bal_forecast.opt_five_price', 'opt_five_price' ),

        Field::inst( 'contract_return_bal_forecast.comments', 'comments' ),
        Field::inst( 'contract_return_bal_forecast.created_at', 'created_at' ),
        Field::inst( 'contract_return_bal_forecast.start_date'),
        Field::inst( 'contract_return_bal_forecast.end_date','end_date'),
        Field::inst( 'contract_return_bal_forecast.edited_by' )->set( Field::SET_EDIT )
    );

if ($_SESSION["username"] == 'crg')
{
  $editor->fields(Field::inst( 'supplier.supplier_name')
);

}
else{
$editor->where( 'supplier.supplier_name', $_SESSION["username"] );
}

 $editor
 ->on( 'preEdit', function ( $editor, $values ) {
        $editor
            ->field( 'contract_return_bal_forecast.edited_by' )
            ->setValue( $_SESSION['username'] );

         } )

                 ->leftJoin( 'products', 'products.product_code', '=', 'contract_return_bal_forecast.product_code_fk' )
                 ->leftJoin( 'supplier', 'supplier.supplier_id', '=', 'products.supplier_id_fk' )

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

it is important to note, in case of the three keys used in code above
Field::inst( 'contract_return_bal_forecast.product_code', 'product_code')
Field::inst( 'contract_return_bal_forecast.member_name','member_name'),
Field::inst( 'contract_return_bal_forecast.start_date', 'start_date'),

using Alias names wont work and will give the error (primary key not found).

Now what I have done is , I have set these three columns to be unique in database using phpmyAdmin. and set up a new column contract_idas a auto increment primary key , as suggested by @rf1234 like in the code below and It is working now using alias names
<?php
session_start();

include( "../lib/DataTables.php" );
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 = Editor::inst( $db, 'contract_return_bal_forecast',  'contract_id' )
    ->fields(

        Field::inst( 'contract_return_bal_forecast.contract_id'),
        Field::inst( 'contract_return_bal_forecast.product_code', 'product_code')

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

        Field::inst( 'contract_return_bal_forecast.product_name', 'product_name' )

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

        Field::inst( 'products.pack_size' , 'pack_size'),
        Field::inst( 'contract_return_bal_forecast.member_name','member_name'),
      Field::inst( 'contract_return_bal_forecast.supplier_name', 'supplier_name' )
            ->options( Options::inst()
                                            ->table( 'supplier' )
                                            ->value( 'supplier_name' )
                                            ->label( 'supplier_name' )
                                    )
                                    ->validator( Validate::dbValues() ),

        Field::inst( 'contract_return_bal_forecast.contract_prod' ,'contract_prod'),
        Field::inst( 'contract_return_bal_forecast.opt_one', 'opt_one' ),
        Field::inst( 'contract_return_bal_forecast.opt_two', 'opt_two' ),
        Field::inst( 'contract_return_bal_forecast.opt_three' , 'opt_three'),
        Field::inst( 'contract_return_bal_forecast.opt_four' , 'opt_four'),
        Field::inst( 'contract_return_bal_forecast.opt_five', 'opt_five' ),
        Field::inst ( '(contract_return_bal_forecast.opt_one + contract_return_bal_forecast.opt_two + contract_return_bal_forecast.opt_three +contract_return_bal_forecast.opt_four +contract_return_bal_forecast.opt_five)', 'total_est' )
                         ->set(false),
        Field::inst( 'contract_return_bal_forecast.opt_one_firm', 'opt_one_firm' ),
        Field::inst( 'contract_return_bal_forecast.opt_two_firm', 'opt_two_firm' ),
        Field::inst( 'contract_return_bal_forecast.opt_three_firm', 'opt_three_firm' ),
        Field::inst( 'contract_return_bal_forecast.opt_four_firm' , 'opt_four_firm'),
        Field::inst( 'contract_return_bal_forecast.opt_five_firm', 'opt_five_firm' ),
        Field::inst( 'contract_return_bal_forecast.opt_one_price' , 'opt_one_price'),
        Field::inst( 'contract_return_bal_forecast.opt_two_price' , 'opt_two_price'),
        Field::inst( 'contract_return_bal_forecast.opt_three_price' , 'opt_three_price'),
        Field::inst( 'contract_return_bal_forecast.opt_four_price' , 'opt_four_price'),
        Field::inst( 'contract_return_bal_forecast.opt_five_price', 'opt_five_price' ),

        Field::inst( 'contract_return_bal_forecast.comments', 'comments' ),
        Field::inst( 'contract_return_bal_forecast.created_at', 'created_at' ),
        Field::inst( 'contract_return_bal_forecast.start_date', 'start_date'),
        Field::inst( 'contract_return_bal_forecast.end_date','end_date'),
        Field::inst( 'contract_return_bal_forecast.edited_by' )->set( Field::SET_EDIT )
    );



 $editor
 ->on( 'preEdit', function ( $editor, $values ) {
        $editor
            ->field( 'contract_return_bal_forecast.edited_by' )
            ->setValue( $_SESSION['username'] );

         } )

                 ->leftJoin( 'products', 'products.product_code', '=', 'contract_return_bal_forecast.product_code_fk' )
                 ->leftJoin( 'supplier', 'supplier.supplier_id', '=', 'products.supplier_id_fk' )

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