how do I remove a record in a linked table when editing a parent record

jimbiz · June 2020

In the link table example how would I add an option to remove an existing department?

For example, if someone temporarily had no department, I need an option in the select dropdown such as "No department" which would delete the record in the linked table.

rf1234 · June 2020

Many roads lead to Rome and here is one of them:

Create a view that includes the empty option, e.g. like this:

CREATE VIEW `dept_view` AS
SELECT `id`, `name`
FROM `dept`
     UNION ALL
SELECT 0 AS `id`, "No department" AS `name`
FROM `dept`
ORDER BY 1 ASC

Use this view in the options instance instead of table "dept"

Field::inst( 'user_dept.dept_id' )
    ->options( Options::inst()
        ->table( 'dept_view' )
        ->value( 'id' )
        ->label( 'name' )
    ),

on "writeCreate" and on "writeEdit" delete the potential link table entry WHERE id = 0 using the raw() method for example.
https://editor.datatables.net/manual/php/events

->on( 'writeEdit', function ( $editor, $id, $values ) {
        $editor->db()->raw()
           ->bind( ':fk', 0 )
           ->exec( 'DELETE FROM user_dept
                     WHERE dept_id = :fk' );
} )

You might get in trouble with referential integrity of your database when inserting the "crappy" link table entry. Hence you might also decide to have the dummy department in the real table and make sure it is never read when displaying its content. Could also work.

Unfortunately you can't use the cancellable Editor events because they are referring to the entire Editor instance, not (only) to the options instance.

jimbiz · June 2020

Thank you rf1234.

Makes sense using a view to add "No department".

Unfortunately that won't work for me because I use a where clause when fetching my options and this depends on which workspace the user is in.

rf1234 · June 2020

You can also add the option client side.
Either manipulate the options on "xhr" when table data are being loaded or do it with Editor on "open" and use "field().update()".
These threads have examples on the usage of both:
https://datatables.net/forums/discussion/comment/171918/#Comment_171918
https://datatables.net/forums/discussion/comment/169915/#Comment_169915

And here is the documenation:
https://datatables.net/reference/event/xhr
https://editor.datatables.net/reference/api/open()
https://editor.datatables.net/reference/api/field().update()

rf1234 · June 2020

This should do the trick but you might still have the referential integrity issue server side.

var table = $('#example').DataTable( { .....

table
.on('xhr', function( e, settings, json, xhr ) {
    if ( json != null ) {
        if ( typeof json.options !== 'undefined' ) {
            //Merges two arrays, altering the first argument.
            $.merge(json.options["user_dept.dept_id"], [{label: "No department", value: "0"}])
        }
    }          
});

rf1234 · June 2020

Using the "xhr" manipulation above your server side code looks like this:

Field::inst( 'user_dept.dept_id' )
    ->options( Options::inst()
        ->table( 'dept' )
        ->value( 'id' )
        ->label( 'name' )
    ),

In order to avoid trouble with referential integrity I would replace the "delete" solution for the "No department" selection by the one below which avoids an update of the field in that case. On "create" that's all you need to do but on "edit" you would still need to delete a potential link table entry because the user might have changed the selection from an existing department to "No department".

->on( 'validateCreate', function ( $editor, $values ) {
        if ( isset($values['user_dept']['dept_id'] ) {
           if ( $values['user_dept']['dept_id'] < 1 ) {
              $editor->field('user_dept.dept_id')->set( false );
           }
        }
} )
->on( 'validateEdit', function ( $editor, $id, $values ) {
        if ( isset($values['user_dept']['dept_id'] ) {
           if ( $values['user_dept']['dept_id'] < 1 ) {
              $editor->field('user_dept.dept_id')->set( false );
              $editor->db()->raw()
                  ->bind( ':fk', $id ) //id of table users
                  ->exec( 'DELETE FROM user_dept
                           WHERE user_id = :fk' );
           }
        }
} )

allan · June 2020

The placeholder option has a placeholder option which can be used to show a "No department selected" message - e.g.:

{
  name: 'user.department',
  placeholder: 'No department selected',
  placeholderDisabled: false,
  placeholderValue: ''
}

Then on the server-side use the ifEmpty formatter to write null into user.department if submitted with the placeholder selected.

This will not delete the department record from the table that contains the departments. For that, you would need to use a server-side event handler such as postEdit or postCreate. But I don't think that is what you want (despite the title of the post saying that) - unless I've misunderstood the rest of your post?

Allan

jimbiz · June 2020

Thank you rf1234 and Allan.

I did try placeholder before but I was missing "placeholderDisabled: false" which is what I needed to make it selectable when a value already existed.

Yes, I do want to delete the row in the linked table.

Here is my actual working code:

  ->on('preEdit', function ($editor, $id, $values) {
    global $getEvent;
    if (isset($values['team_pools']['pool_id'])) {
      if ($values['team_pools']['pool_id'] < 1) {
        $editor->db()->raw()
          ->bind(':efk', $getEvent->id) // event id
          ->bind(':tfk', $id) // team id
          ->exec('DELETE FROM team_pools WHERE event_id = :efk AND team_id = :tfk');
        return false;
      }
    }
  })

One small problem.. I need to return false in this case to prevent an error with updating a record with no pool_id. How do I refresh the table after returning false?

rf1234 · June 2020

Yes, and that's why I suggested a different solution. Your solution has two issues
- in case you have a validation error of the user entries and the validation fails you will still execute the DELETE.
- you are not avoiding the update of "pool_id" individually hence you are returning FALSE for the ENTIRE record and are not making any update except for the DELETE of the link table.

Both of your issues are avoided using the combination of "validatedCreate" and "validatedEdit" (excuse the typo above it is "validated" not "validate") which only sets the update of the id field to false not the entire record.

One small problem.. I need to return false in this case to prevent an error with updating a record with no pool_id. How do I refresh the table after returning false?

Well by just doing an ajax.reload(). But there is not much to refresh: You didn't make any changes except for the manual DELETE of the link table. https://datatables.net/reference/api/ajax.reload()

This line of code makes sure that the pool_id is not updated. Returning false is therefore not required.

$editor->field('team_pools.pool_id')->set( false );

jimbiz · June 2020

Thanks rf1234

I added check for pool_id and set to false on preEdit as well to prevent this error:

"An SQL error occurred: SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect integer value: '' for column team_pools.pool_id at row 1".

I see that on validatedEdit is firing now (after update to 1.9.3) but delete is not happening, only pool_id is set to null. Both $id and $getEvent->id are set correctly.

Is there something wrong with my delete code?

    if (isset($values['team_pools']['pool_id'])) {
      if ($values['team_pools']['pool_id'] < 1) {
        $editor->field('team_pools.pool_id')->set(false);
        $editor->db()->raw()
          ->bind(':efk', $getEvent->id) // event id
          ->bind(':tfk', $id) // team id
          ->exec('DELETE FROM team_pools WHERE event_id = :efk AND team_id = :tfk');
      }
    }

rf1234 · June 2020

That is not enough code to be able to tell. If you implemented my solution team_pools.pool id should not have been set to null. Please post your code or even better set up a test case replicating the issue. Somewhere you will have an invalid date time format ... certainly not in the code snippet that you posted.

And if you can also post your data model that would be helpful, too.

rf1234 · June 2020

And also, your link table isn't a regular link table:
A regular link table consists of just two columns: fk1 and fk2.
Your link table has at least three columns: pool_id, event_id and team_id. This won't work like this. Actually "team_pools" isn't a real link table.

Here is an example:
Let's assume ctr_govdept is the parent table, then ctr_has_ctr_govdept is the link table and ctr is the linkED table. You can also look at this vice versa. Doesn't matter.

Looking at the server side code of the link table example you can see that the link table is user_dept which has two foreign keys and no other columns: dept_id and user_id. Both are specified and marked in yellow.

jimbiz · June 2020

Thanks rf1234

Yes, originally the link table was set up with only team_id and pool_id but I didn't want to delete based on only the team_id since it may be there more than once under multiple pools.

So, in fact what I really need is a one-to-many relationship for that rather than a link table. Thank you for pointing that out.

I'll take a deeper look into it later.

jimbiz · June 2020

Correction, I do need the link table since I will only have one pool per team.

The delete is working now.

Thanks for your help!

how do I remove a record in a linked table when editing a parent record

how do I remove a record in a linked table when editing a parent record

Answers

Howdy, Stranger!

Categories

DataTables

how do I remove a record in a linked table when editing a parent record

how do I remove a record in a linked table when editing a parent record

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables