Deleting a row in joined table generate an SQL error. What do I do wrong?

edwardc

I have 2 tables. One (otc_transport) stores useful info. The second ('otc_transport_ord')is a link table that stores which transport is associated with a particular shipment order. So the link table has 2 columns: id_order and id_transport.
In my table, I need to display the list of transports, to add, edit and delete. As the table is in the current order page, the id_order is set directly.

I tried first the version with simple join.
I can add rows, I can edit rows, I can NOT delete a row.
Error when I confirm deleting the row is:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'otc_transport.id_transport' in 'where clause'

Here is the code:

Editor::inst( $db, 'otc_transport', 'id_transport' )
    ->fields(
        Field::inst( 'otc_transport.id_transport'),
                Field::inst( 'otc_transport_ord.id_order' ) ->setValue( $_GET['id_order']),
// useful fields:
        Field::inst( 'otc_transport.id_type' ),
        Field::inst( 'otc_transport.name' )->validator( 'Validate::notEmpty' ),
// more fields....
    )
    ->leftJoin( 'otc_transport_ord',   'otc_transport_ord.id_transport',   '=', 'otc_transport.id_transport' )
    ->where( 'otc_transport_ord.id_order', $_GET['id_order'] )
    ->process($_POST)
    ->json();

then I tried the Mjoin version for direct link as described in One-to-many joins documentation, but i get the warning:

DataTables warning: table id=tabel_transport - Table selected fields (i.e. '{table}.{column}') in Join must be read only. Use set(false) for the field to disable writing

Editor::inst( $db, 'otc_transport', 'id_transport' )
    ->fields(
        Field::inst( 'otc_transport.id_transport' ),
// useful fields:
        Field::inst( 'otc_transport.id_type' ),
        Field::inst( 'otc_transport.name' )->validator( 'Validate::notEmpty' ),
// more fields....
    )
    ->join(
        Mjoin::inst( 'otc_transport_ord.id_transport','otc_transport.id_transport' )
            ->fields(
                Field::inst( 'otc_transport_ord.id_order' ) ->setValue( $_GET['id_order'])
            )
    )
    ->process($_POST)
    ->json();

so..... what am I missing? What do I do wrong?
Why, if I can create a transport and the link tables is updated and all works perfect, I can not delete it? It makes no sense to me.

Other info:
1. Editor version is 1.6
2. I have no other bugs, all info are sent correct. Proof is that create and edit works ok.
3. if I remember well the delete was also working when I first created the table. We discover the bug now, on final testing. Maybe I did not had 1.6 back then? Can not tell for sure.
4. Debug with file_put_contents returns a wrong SQL: sql DELETE FROM `otc_transport` WHERE (`otc_transport`.`id_transport` = :where_1 AND `otc_transport_ord`.`id_ord` = :where_2 )

allan

If you are using a link table with a one to many join, you must actually have three tables. The host table (otc_transport) the link table (otc_transport_ord) and an order table. Is that correct?

You would actually have the Mjoin point primarily towards the order table and use its link() method to provide the information to link between the parent-link and link-child.

The link table example in the docs shows this a little more clearly.

Regards,
Allan

edwardc

Hi Allan,
Please check joinLinkTable exemple
Same error. So maybe a bug?

edwardc

Update: I tested with editor 1.5 and it is working!
So it must be a bug in 1.6 :-)

allan

Completely agree - my apologies!

I'll have that fixed soon and will let you know what the update is so you can apply it locally before the 1.6.2 release.

Allan

allan

I've just sent a PM with the updated code to address this.

Regards,
Allan

Kiweez

Hi Allan,
I have the same error, thank you for sending me the release version.
Patrick

Kiweez

Hi Allan,
I have the same error, thank you for sending me the release version.
Patrick

cbohner

Same issue here, please provide me with the fix.

jjzhang

Hi Allan,

I have the same issue here, could you send me the version fixed ?

allan

Editor 1.6.2 will be released in the next couple of days with the fix.

Regards,
Allan