How to Submit Data in Mjoin Link Table

vincmeister

Hi Allan,

According this example , and my question here, i need to submit data to the link table between 2 tables (create button)

Let say I have table | field
- act_log_header | act_log_id
- link_log_customer | act_log_id, customer_code
- master_customer | customer_code, customer_name

my php

->join(
            Mjoin::inst( 'master_customer' )
                ->link( 'act_log_head.act_log_id', 'link_log_customer.act_log_id' )
                ->link( 'master_customer.customer_code', 'link_log_customer.customer_code' )
                ->fields(
                    Field::inst( 'customer_code' )
                        ->options( 'master_customer', 'customer_code', 'customer_name' )
                        ->validator( 'Validate::notEmpty' ),
                    Field::inst( 'customer_name' )
                )
        )

the insert is only working on link_log_customer.customer_code
link_log_customer.act_log_id not submitted.

Can it be automatically submitted? or i must using postCreate ? I tried to put Field::inst( 'link_log_customer.act_log_id' ) , but no luck. Please advise, thank you

But it works on edit button

Danny

allan

Hi Danny,

Are you able to give me a link to the page so I can debug it directly please? So at the moment when you submit, and it creates a new entry in the link_log_customer is it only writing to the customer_code field?

Allan

vincmeister

Hi Allan,

Thank you for the respond, here's the test link please take a look

is it only writing to the customer_code field?

Yes, the act_log_id field insert as 0

Please help, thank you

allan

Have you fixed this since your post above? The "Customer" field appears to be working as I would have expected.

Allan

vincmeister

Hi Allan,

No I haven't, it still not working for me
New (create) button, can't submit the customer's field
but the Edit button works

allan

I've created a couple of dummy records and I can indeed see it not inserting the customer information. This is sent to the server:

action:create
data[0][act_log_head][act_log_id]:
data[0][act_log_head][act_log_cat_id]:2
data[0][act_log_head][act_date]:02 Aug 2016
data[0][act_log_head][expedition_code]:PDE
data[0][act_log_head][vehicle_type_id]:2
data[0][act_log_head][vehicle_number]:
data[0][act_log_head][container_number]:
data[0][act_log_head][name]:
data[0][act_log_head][co_driver]:
data[0][act_log_head][log_status_id]:11
data[0][master_customer][0][customer_code]:3
data[0][master_customer][1][customer_code]:4
data[0][master_customer-many-count]:2

So there is customer information being submitted, it just doesn't appear to be acted upon.

Is act_log_id the primary key in the act_log_header table, and the Editor instance has been told that with the third parameter for the constructor?

We might need to do a little debugging here. In the php/Database/Driver/Mysql/Query.php file (assuming it is MySQL you are using) there is a commented out line file_put_contents, could you put in:

file_put_contents( '/tmp/editor_sql', $sql."\n", FILE_APPEND );
file_put_contents( '/tmp/editor_sql', print_r( $this->_bindings, true )."\n", FILE_APPEND );

and then run an insert with two customers selected and then let me know what the contents of the file is (you might need to change the path of the output file to be suitable for your server).

Thanks,
Allan

vincmeister

Hi Allan,

Is act_log_id the primary key in the act_log_header table, and the Editor instance has been told that with the third parameter for the constructor?

Yes, here's my full php

Editor::inst( $db, 'act_log_head','act_log_head.act_log_id' )
        ->field(
            Field::inst( 'act_log_head.act_log_id' ),
            Field::inst( 'act_log_head.act_log_cat_id' )
                ->validator( 'Validate::notEmpty')
                ->options( 'master_log_category', 'act_log_cat_id', 'act_log_cat', function ($q) {
                    $q->where( 'act_log_type', 'WH', '=' );
                }),
            Field::inst( 'master_log_category.act_log_cat' ),
            Field::inst( 'act_log_head.act_date' )
                ->validator( 'Validate::dateFormat', array(
                    'empty' => false,
                    'format' => 'd M Y'
                ) )
                ->getFormatter( 'Format::datetime', array(
                    'from' => 'Y-m-d',
                    'to' =>   'd M Y'
                ) )
                ->setFormatter( 'Format::datetime', array(
                    'from' => 'd M Y',
                    'to' =>   'Y-m-d'
                ) ),
            Field::inst( 'act_log_head.expedition_code' )
                ->validator( 'Validate::notEmpty')
                ->options( 'master_expedition', 'expedition_code', 'expedition_name' ),
            Field::inst( 'master_expedition.expedition_name' ),
            Field::inst( 'act_log_head.vehicle_type_id' )
                ->validator( 'Validate::notEmpty')
                ->options( 'master_vehicle_type', 'vehicle_type_id', 'vehicle_type' ),
            Field::inst( 'master_vehicle_type.vehicle_type' ),
            Field::inst( 'act_log_head.vehicle_number' ),
            Field::inst( 'act_log_head.container_number' ),
            Field::inst( 'act_log_head.name' ),
            Field::inst( 'act_log_head.co_driver' ),
            Field::inst( 'act_log_head.log_status_id' )
                ->options( 'master_log_status', 'log_status_id', 'log_status' ),
            Field::inst( 'master_log_status.log_status' )
            )

        ->leftJoin( 'master_vehicle_type', 'master_vehicle_type.vehicle_type_id', '=', 'act_log_head.vehicle_type_id' )
        ->leftJoin( 'master_expedition', 'master_expedition.expedition_code', '=', 'act_log_head.expedition_code' )
        ->leftJoin( 'master_log_category', 'master_log_category.act_log_cat_id', '=', 'act_log_head.act_log_cat_id' )
        ->leftJoin( 'master_log_status', 'master_log_status.log_status_id', '=', 'act_log_head.log_status_id' )
        
        ->join(
            Mjoin::inst( 'master_customer' )
                ->link( 'act_log_head.act_log_id', 'link_log_customer.act_log_id' )
                ->link( 'master_customer.customer_code', 'link_log_customer.customer_code' )
                ->fields(
                    Field::inst( 'customer_code' )
                        ->options( 'master_customer', 'customer_code', 'customer_name' )
                        ->validator( 'Validate::notEmpty' ),
                    Field::inst( 'customer_name' )
                )
        )
        ->where( 'act_log_head.log_status_id', 6,'<>' )
        ->process($_POST)
        ->json();

Here's the editor_sql result on pastebin

allan

Editor::inst( $db, 'act_log_head','act_log_head.act_log_id' )

Could you remove the act_log_head. part from the primary key please? It shouldn't be required and I wonder if that is what is causing the issue.

Thanks,
Allan

vincmeister

Hi Allan,

No luck after remove the the act_log_head from the primary key. Test Link updated

Editor::inst( $db, 'act_log_head','act_log_id' )

allan

Thanks for the SQL in pastebin (sorry I missed that link yesterday). That confirms the issue is act_log_id:

INSERT INTO  `link_log_customer`  ( `act_log_id`, `customer_code` ) VALUES (  :act_log_id,  :customer_code )
Array
(
    [0] => Array
        (
            [name] => :act_log_id
            [value] =>
            [type] =>
        )
 
    [1] => Array
        (
            [name] => :customer_code
            [value] => 2
            [type] =>
        )
)

note that value in the first array is empty - it should be the id of the row that was inserted into the act_log_head table.

In Editor.php you'll find a function called _insert which has the following code in it:

        // Join tables
        for ( $i=0 ; $i<count($this->_join) ; $i++ ) {

Immediately before that line could you add:

file_put_contents( '/tmp/editor_sql', 'Insert id: '. var_export($id, true)."\n", FILE_APPEND );

That will let us see exactly what the new row's id is.

I presume it is an auto incrementing column, or is it defined some other way? I'm wondering if this:

Field::inst( 'act_log_head.act_log_id' )

is what is causing the problem.

Allan

vincmeister

Hi Allan,

Here's the editor_sql result

I presume it is an auto incrementing column, or is it defined some other way?

Yes it is an auto increment field

Please advise, thank you

allan

Thanks. However, I don't see the text Insert id: anywhere in the file. Did it definitely go into _insert rather than _edit?

Also, could you try changing: Field::inst( 'act_log_head.act_log_id' ) to be:

Field::inst( 'act_log_head.act_log_id' )->set( false )

Thanks,
Allan

vincmeister

Hi Allan,

It works after adding ->set( false )

Thanks again for the help
Danny