Delete child row using WHERE condition

ccantey

I have created parent/child tables using 3 different editors (create, edit, delete) because the child member table uses many tables... IE, there are many members tables (child) to commission tables (parent).

To delete members from a commission is a straight forward process: Delete from MCT where memid = 'xx' and commid= 'xx' and session='xx'

The delete object if very simple:

    var members_deletor = new $.fn.dataTable.Editor( {
        ajax: {
            url:'/appointments/editor/lib/table.delete.php',
            data: function (d){

                var selected = members_table.row({selected: true});         
                if (selected.any()){
                    d.commissionid = selected.data().member_comm_term.commid;
                    d.mct = selected.data().member_comm_term.session;
                }
            }
        },
        table: '#members',
        fields: [
            {
                label: "mct.Appointed By:",
                name: "memid"
            },
            {
                label: "mct.Session:",
                name: "session"
            },
            {
                label: "mct.Start Date:",
                name: "term_start",
                type: "datetime"
            },
            {
                label: "mct.End Date:",
                name: "term_end",
                type: "datetime"
            },
            {
                label: "mct.Appointed By:",
                name: "sess_id"
            },
            {
                label: "mct.Appointed By:",
                name: "status"
            },
            {
                label: "mct.Appointed By:",
                name: "commid"
            },
            {
                label: "mct.Officer:",
                name: "officer"
            },
            {
                label: "mct.Appointed By:",
                name: "appt_by"
            },
            {
                label: "mct.Appointed By:",
                name: "year"
            },
            {
                label: "mct.Representing:",
                name: "representing"
            }       
        ]
    } );

Editor::inst( $db, 'member_comm_term', 'memid' )
    ->debug( true )
    ->fields(
        //Field::inst( 'members_commissions.member_id' ),
        //Field::inst( 'members_commissions.commission_id' ),
        Field::inst( 'member_comm_term.memid' ),
        Field::inst( 'member_comm_term.session' ),
        Field::inst( 'member_comm_term.term_start' ),
        Field::inst( 'member_comm_term.term_end' ),
        Field::inst( 'member_comm_term.sess_id' ),
        Field::inst( 'member_comm_term.status' ),
        Field::inst( 'member_comm_term.commid' ),
        Field::inst( 'member_comm_term.officer' ),
        Field::inst( 'member_comm_term.appt_by' ),
        Field::inst( 'member_comm_term.year' ),
        Field::inst( 'member_comm_term.representing' )

    )
    ->where('member_comm_term.commid ', $_POST['commissionid'])
    ->where('member_comm_term.session ', $_POST['mct'])
    ->process( $_POST )
    ->json();

However, when i delete a member in the member table, it deletes all records of that member in the MCT table with that memid (one member can have many commissions). It seems like the two ->where() conditions should prevent this from happening. But those conditions are being completely ignored.

There are no errors, here is the response:
{"data":[],"debug":[{"query":"DELETE FROM \"member_comm_term\" WHERE (\"memid\" = :where_1 )","bindings":[{"name":":where_1","value":"10558","type":null}]}]}

allan

Editor's delete operation assumes that the primary key used for the delete command will be unique. i.e. in this case, memid should be unique - Editor would submit the row id to delete (e.g. 39, or whatever) and just that row would be deleted from member_comm_term.

Is memid not unique on that table?

However, when i delete a member in the member table, it deletes all records of that member in the MCT

Are you referring to a member table, or member_comm_term here? If there is a parent member table, and you delete a record from it, and the member_comm_term table has a foreign key reference to member and you have on cascade delete set in your database, then yes, that would be expected and isn't caused by Editor. It is a database mechanism.

Sorry this isn't a perfect answer, but I'm missing a little bit of knowledge about the setup

Allan

ccantey

So this response makes sense... I have 3 tables in use for this example: members table, MCT table, commissions table... I had a members_commissions link table, but it seemed unnecessary by using a direct 1:m reference to MCT.

"However, when i delete":

I am actually referring to the MCT table.. For the delete operation, this is the only table that should be altered..... The members and commissions tables should never really change, except when adding new m's and c's.

ccantey

Correct. memid is not unique on MCT table

ccantey

using the members_commisions link table, I can add and delete correctly. However, members need to be added to commisions over different sessoins....

For example:

member1 belongs to commision2 in session1
member1 belongs to commision2 in session2

Currently if I try to add members in that way with members_commisions link table, i get ERROR: duplicate key value violates unique constraint "members_commissions_pkey"

I guess that's why I abandoned the link table.

I need to rethink my schema, unless you have other thoughts.

allan

Correct. memid is not unique on MCT table

Right - that's going to cause Editor problems. Can you add a serial to that table? Even if it isn't used for anything else, it would allow a unique identifier for each row, which your Editor / DataTable that operates on that table could use.

Allan