Child editing without loading the parent

joris.kempen

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?

rf1234

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.

rf1234

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.kempen

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.kempen

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.kempen

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!