Editor: optional field from joined table (OUTER JOIN)

TonyR

I am using Editor and struggling with one text field from a side table that I want to be optional, like in an OUTER JOIN. (I am using MySQL.) I've found a few other threads on that topic here, but those situations seem more complex than my issue. I have set my side_table.id to be an auto-increment, primary index. I have set the main_table.notes field as NULL (yes), and it should get the value of the side_table.id field or remain null if there is no corresponding row in the side_table.

The following javascript code produces an SQL error saying that whatever text I attempted is an incorrect integer value for the field 'notes.' Here is the actual text that appears in red on my Editor form: "An SQL error occurred: SQLSTATE[HY000]: General error: 1366 Incorrect integer value: 'This is a note.' for column 'notes' at row 1"

{ label: 'Notes',   name: 'main_table.notes',  type: "textarea"},     // Produces SQL error

I thought about switching the name part of the javascript to the side_table, but when I do no error is returned but main_table.notes remains a null value:

{ label: 'Notes',   name: 'side_table.note_text',  type: "textarea"},    // No error, but no value in main_table.notes field

I think that my server script code is correct:

server script:

       Field::inst( 'main_table.notes')    // main_table.notes is an integer
            ->options( options::inst()
                ->table( 'side_table')
                ->value( 'id') 
                ->label( 'note_text')
            ),
        field::inst( 'side_table.note_text'),

server script join clause:

->leftJoin( 'side_table', 'side_table.id', '=', 'main_table.notes')

As a test, I manually entered a single record into the side table causing its id field to equal 1, and in the main table I modified the 'notes' integer field to equal 1 for an existing record. The text field now displays properly in my Table and in my Editor form. I just can't enter a the note using the Editor form either by 'edit' or by 'new.'

I don't need a 1-to-many relationship because for any main_table record, I would only need one corresponding notes record in the side table.

Does anyone have just a basic example of how to do this? I realize that I could just keep the text (notes) in my main table, but I would like to learn how to do it this way just so I know how it can be done.

allan

Its a little tricky this one and you'll need a little custom code to do it (although not much!). The problem is that you don't want the text value to be written to the main table, it needs to be written into the child table, so your thought about changing the name of the field to match that is correct. However, it isn't as simple as that (of course).

While Editor can insert into a side table, it does so after the main table has been written to. In this case you want it the other way around, so you can reference the newly created id for the side table in the main table.

Server-side events are the way to handle this. Something like:

->on( 'preCreate', function ( $editor, $values ) use ( $db ) {
  $res = $db->insert( 'side_table', [
    "note_text" => $values['side_table']['note_text']
  ] );

  $editor->field('main_table.notes')->setValue( $res->insertId() );
} );

You'd need something similar for preEdit and also take into account that you might want to set the value to null if the submitted text is empty. Note that you will need to include main_table.notes in the list of fields.

Full docs for the PHP database class Editor uses are available here.

Allan

TonyR

Allan, many thanks for your response! I will delve into this. Thanks again for your products and customer service, both excellent.

TonyR

The recommendation above by Allan works very well when note_text is entered, but a record is still inserted in side_table, and that record's id is stored in main_table.notes even when no text is entered. Below, I am trying to avoid that by testing for a null integer value for main_table.notes:

    ->on( 'preCreate', function ( $editor, $values ) use ( $db ) {
        if (!is_null($editor->field('side_table.note_text'))){
            $res = $db->insert( 'side_table', 
                ["note_text" => $values['side_table']['note_text']] ); 
            $editor->field('main_table.notes')
            ->setValue( $res->insertId() );
        }
    } ) ...

Am I attempting something that is not recommended i.e. keep optional text data in separate tables? I am not sure if it is a 'best practices' approach. It would be easy enough to keep the text in the main table.

allan

That looks fine to me. If that is the only field in the other table then I would be tempted to fold it into the main table myself, but I'm not a DBA so I might be missing something!

Allan