Child editing without loading the parent

Child editing without loading the parent

joris.kempenjoris.kempen Posts: 6Questions: 2Answers: 0

In the past i had a flat "complaints" table in mysql.
table structure (simplified):
ID (primary key)
description (text field)
category (dropdown hard coded)
contact (dropdown hard coded)

As the number of categories / contacts is growing i want to make a nicer datamodel:
ID
description
category_id
contact_id

and make 2 dimensional tabels category and contacts with nice list of categories / contacts

Get this data with a PHP / SQL statement and push to Datatables (JSON / AJAX) is no problem.

But how do i make the editor work? I don't need to edit the parents (categorie / contacts) table, as they don't change that much.

But how do i make an editor where:
- the complaints.category_id is updated nicely
- the dropdown is fully populated with all possible options.

Is this something done in the PHP Editor side?

What i can think of is:
- return the rows normally:
ID description category_id contact_id
1 text 2 4

and also give back an array of the possible combo's for each dropdown.
category
1 = product_failure
2 = service_failure

1 = Angelo johnson
2= Joe Doo

So the user only sees readable but the column edit is adjusting the foreign key.

Is this the right way, or are there other options?

This question has an accepted answers - jump to answer

Answers

  • rf1234rf1234 Posts: 2,856Questions: 85Answers: 409

    I don't quite understand your data model. If you post an E/R diagram of your data model I might be able to help you.

    And yes: You would do most of this in PHP on the server.

  • rf1234rf1234 Posts: 2,856Questions: 85Answers: 409
    edited July 8 Answer ✓

    You are saying you don't want to update the "categories" and the "contacts" tables, but just the "complaints" table.

    That is fairly easy: Instead of having hard coded options for "category" and "contact" you need to retrieve them from the database.

    Here is an options instance for "category_id" assuming that "categories" has a column "name" and a column "whatever".

    Field::inst( 'complaints.category_id' )
        ->options( Options::inst()
            ->table('category')
            ->value( 'id' )
            ->label( array('name', 'whatever') )
            ->render( function ( $row ) { 
                return $row["name"] . ": " . $row["whatever"];
            } )
            ->order( 'name asc' )
        ),
    

    and js using field type "select":

    }, {
        label: 'Category:',
        name: "complaints.category_id", //render name: whatever
        type: "select",
        placeholder: "select category"
    }
    

    As you see this does require loading the parent. The options ARE the content of the parent table ... How else would you do this? I mean you need to see the parent table's contents if it contains the options.

  • joris.kempenjoris.kempen Posts: 6Questions: 2Answers: 0

    I solved it indeed in the server side, by loading all parent values as option. I just didn't want to show them in the datatable

  • joris.kempenjoris.kempen Posts: 6Questions: 2Answers: 0

    this is the PHP code to load all the options:
    // Build the Editor instance and process the data coming from _POST
    $editor = Editor::inst($db, 'medicura_ContactDB.klacht', 'klacht_nr')
    ->fields(

        Field::inst('klacht_nr'),
        Field::inst( 'klacht.klacht_categorie_id' )
            ->options( Options::inst()
                ->table( 'medicura_ContactDB.klacht_categorie' )
                ->value( 'klacht_categorie_id' )
                ->label( 'klacht_categorie_tekst' )
            )
            ->validator( Validate::dbValues() ),
        Field::inst( 'klacht_categorie.klacht_categorie_tekst' )
    )
    ->leftJoin( 'medicura_ContactDB.klacht_categorie', 'klacht_categorie.klacht_categorie_id', '=', 'klacht.klacht_categorie_id' )
    ->process( $_POST )
    ->json();
    
  • joris.kempenjoris.kempen Posts: 6Questions: 2Answers: 0

    Hi RF1234,

    I meant i didn't want to show the Parent table in the UI.

    My ERD if fairly simple:

    complaints table:
    ID Description_field, Category_ID, Contact_ID

    and 2 dimension tables (for the dropdowns) connecting on the Primaray key

    Category Tabel:
    Category_ID Category_Text

    And after fiddling i have found out indeed i need to load the possible options in the Serverside like this:

    // Build the Editor instance and process the data coming from _POST
    $editor = Editor::inst($db, 'medicura_ContactDB.klacht', 'klacht_nr')
    ->fields(
    Field::inst( 'klacht.klacht_nr' )->set(false),
    Field::inst( 'klacht.klantnaam' )
    ->validator( Validate::notEmpty( ValidateOptions::inst()
    ->message( 'A customer name is required' )
    ) ),
    Field::inst( 'klacht.klacht_categorie_id' )
    ->options( Options::inst()
    ->table( 'medicura_ContactDB.klacht_categorie' )
    ->value( 'klacht_categorie_id' )
    ->label( 'klacht_categorie_tekst' )
    )
    ->validator( Validate::dbValues() ),
    Field::inst( 'klacht_categorie.klacht_categorie_tekst' )
    )
    ->leftJoin( 'medicura_ContactDB.klacht_categorie', 'klacht_categorie.klacht_categorie_id', '=', 'klacht.klacht_categorie_id' )
    ->process( $_POST )
    ->json();

    And this Left Join (with options) i can perform on each linked column.

    Thanks for getting me on the right way!

Sign In or Register to comment.