Automate entry in a field

jalape

Hi,
I have two tables: “tb_detalles” y “tb_palabras”, united by a third table, “detalles_palabras”. This last table contains two fields: “detalles_id” y “palabras_id”, which are the main key and which are related to the id of each of the respective “tb_detalles” y “tb_palabras”.
With the Datatable editor I can fill a column with multiple words for each record:

detalles_palabras (table)
detalles_id        palabras_id (fields)
12               2
12               7
12               15
17               2

The summary code that I am using is the following:

$.fn.dataTable.Editor( {
    fields: [ 
    {
        label: "Pal. Clave:",
        name: "tb_palabra[].id",
        type: "select2",
        opts: {
            "placeholder": "Select State",
            "multiple": "multiple",
            "allowClear": true
            } 
        },  
    ]
} );

$('#imagen_tbl').DataTable( {
    columns: [
        { data: "tb_palabra", render: "[, ].palabra" }, 
    ]
} );

Data:

Editor::inst( $db, 'tb_detalle' )

    ->join(
        Mjoin::inst( 'tb_palabra' )
        ->link( 'tb_detalle.id', 'palabra_detalle.detalle_id' )
        ->link( 'tb_palabra.id', 'palabra_detalle.palabra_id' )
        ->order( 'palabra asc' )
        ->fields(
            Field::inst( 'id' )
            ->validator( Validate::required() )
            ->options( Options::inst()
                ->table( 'tb_palabra' )
                ->value( 'id' )
                ->label( 'palabra' )
            ),
            Field::inst( 'palabra' )
        )
        ->where( 'palabra_detalle.modo_id', 1 )
    )
)

I want to add a new functionality and I need, that the table: “detalles_palabras” differentiate the data from other tables. For that I added a new field: “modo_id”

(Before)
detalles_palabras (table)
detalles_id      palabras_id (fields)
12              2
12              7
12              15
17               2

(Now)
detalles_palabras (table)
detalles_id     palabras_id     modo_id (fields)
12               2                   1
12               7                   1
12              15                   1
17              2                    1
Data from a second Datatable
12              7                    2
12              15                   2
23              7                    2
Data from a third Datatable
12              15                   3
23              7                    3
23              5                    3

The question is: How can I do that when I add a word from each of the datatables, I automatically insert the data in the field mode_id, with the information of the corresponding table. With the example code, it would be a 1.

Thank you very much and happy year to all.

allan

Hi,

That unfortunately isn't going to work with the Mjoin ability of Editor I'm sorry to say. The link table (detalles_palabras in this case) can only hold the two joined keys - it can't have any joined information. The reason for this is how the edit action is implemented. It does a delete on all matching records for the join and then inserts them again (a diff would be difficult and slow to implement!).

What it should do is at this point apply the _apply_where method similar to what happens when a single hop join is performed (i.e. without a join table).

I will take a look into that, as you would then be able to do something like:

Field::inst( 'modo_id' )
  ->setValue( 1 )

Thanks,
Allan

jalape

Hi Allan, thanks for answering.
Indeed, I believe that the action must take place within Join. Maybe creating and launching a function that points to: palabra_detalle.modo_id = 1

jalape

It has managed to solve it, although outside of Datatable Editor.
As it seems difficult to be able to automate the writing from the editor. The solution is to keep the tables separate. The field "modo_id" automatically writes 1, 2 or 3, according to the table, setting the default property to customize.
To join each table use of the SQL form:
SELECT modo_id FROM palabra_detalle
UNION ALL
SELECT modo_id FROM palabra_libro

This causes all the values in the modo_id field of the tables to be displayed: palabra_detalle and palabra_libro as a single table.