Deleting a row in joined table generate an SQL error. What do I do wrong?
Deleting a row in joined table generate an SQL error. What do I do wrong?
edwardc
Posts: 30Questions: 11Answers: 0
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
Joinmust be read only. Useset(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 )
This question has accepted answers - jump to:
Answers
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
Mjoinpoint primarily towards the order table and use itslink()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
Hi Allan,
Please check joinLinkTable exemple
Same error. So maybe a bug?
Update: I tested with editor 1.5 and it is working!
So it must be a bug in 1.6 :-)
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
I've just sent a PM with the updated code to address this.
Regards,
Allan
Hi Allan,
I have the same error, thank you for sending me the release version.
Patrick
Hi Allan,
I have the same error, thank you for sending me the release version.
Patrick
Same issue here, please provide me with the fix.
Hi Allan,
I have the same issue here, could you send me the version fixed ?
Editor 1.6.2 will be released in the next couple of days with the fix.
Regards,
Allan