Datatables Editor - Mjoin with Index Table

ricardobl

Hello,

I´ve been struggling with this simple doubt on editing succesfully a field from a link table.
I already saw the examples provided on the site, table successfully shows the fields from from the 'book' and 'user' or users associated with it, from the Link Table.
Problem is to update on the Link Table (books_users) the fields provided on the editor, on this case associating one or more users to a book... Maybe someone could enlighten me of what is failing on the SQL update

Thanks in advance,
Ricardo

Table books (books -> PK):
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| book  | varchar(100) | NO   | PRI |         |       |
+-------+--------------+------+-----+---------+-------+

Table users (user->PK)
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| user  | varchar(100) | NO   | PRI |         |       |
+-------+--------------+------+-----+---------+-------+

Table books_users (Link Table with book_id FK of books.book and user_id FK of users.user
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| book_id | varchar(100) | YES  | MUL | NULL    |       |
| user_id | varchar(100) | YES  | MUL | NULL    |       |
+---------+--------------+------+-----+---------+-------+

table.books.php:

Editor::inst( $db, 'books', 'book' )
    ->fields(
        Field::inst( 'books.book' )->set( false )
    )
    ->join(
        Mjoin::inst( 'users' )
            ->link( 'books.book', 'books_users.book_id' )
            ->link( 'users.user', 'books_users.user_id' )
            ->fields(
                Field::inst( 'user' )
                  )
    )


    ->process( $_POST )
    ->json();

Datatable:

        var editor = new $.fn.dataTable.Editor( {

                ajax: {
                    url: 'editor-php/table.books.php',
                },
                table: '#books',
                fields: [
                        {
                                "label": "Book:",
                                "name": "books.book",
                                "type": "readonly"
                        },

                        {
                                "label": "User:",
                                "name": "users.user"

                        }

                ]
        } );
        var table = $('#books').DataTable( {
                ajax: 'editor-php/table.books.php',
                columns: [
                        {
                            data: null,
                            className: "text-center",
                            width: "10",
                            defaultContent: '<button id="editor_edit" type="button" class="btn btn-success">EDIT</button></a>'
                        },
                        {
                                "data": "books.book", width: "118px"
                        },
                        {
                                "data": "users", render: "[, ]
                        }

                ],
(...)

allan

Hi,

It doesn't appear that you are actually submitting the information to the server to connect the users with books.

For example in the example uses:

            {
                "label": "Permissions:",
                "name": "permission[].id",
                "type": "checkbox"
            }

You might use:

            {
                "label": "Users:",
                "name": "users[].user",
                "type": "checkbox"
            }

You will also need to add options to the user column in your join statement so the checkboxes can be populated:

                Field::inst( 'name' )
                    ->validator( 'Validate::required' )
                    ->options( Options::inst()
                        ->table( 'names' )
                        ->value( 'name' )
                        ->label( 'name' )
                    ),

Allan

ricardobl

Hi Allan, Yep that did the trick.
Thanks so much for your help

Best regards,
Ricardo

Here is the working code

        var editor = new $.fn.dataTable.Editor( {

                ajax: {
                    url: 'editor-php/table.books.php',
                },
                table: '#books',
                fields: [
                        {
                                "label": "Book:",
                                "name": "books.book"
                        },

                        {
                                "label": "User:",
                                "name": "users[].user",
                                "type": "checkbox"
                        }

                ]
        } );


    // Edit button action when inline edit button is clicked
    $('#books').on('click', 'button', function (e) {
        e.preventDefault();

        editor.edit( $(this).closest('tr'), {
            title: 'Edit record',
            buttons: 'Update'
        } );
    } );


        var table = $('#books').DataTable( {
                ajax: 'editor-php/table.books.php',
                columns: [
                        {
                            data: null,
                            className: "text-center",
                            width: "10",
                            defaultContent: '<button id="editor_edit" type="button" class="btn btn-success">EDIT</button></a>'
                        },
                        {
                                "data": "books.book", width: "118px"
                        },
                        {
                                "data": "users", render: "[, ].user"
                        }

                ],
                select: true,
                lengthChange: true,
                lengthMenu: [[20, 50, 500, -1], [20, 50, 500, "All"]],
            } );

        table.buttons().container()
            .appendTo( $('.col-sm-6:eq(0)', table.table().container() ) );
} );

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'books', 'book' )
    ->fields(
        Field::inst( 'books.book' )

    )

    ->join(
        Mjoin::inst( 'users' )
            ->link( 'books.book', 'books_users.book_id' )
            ->link( 'users.user', 'books_users.user_id' )
            ->fields(
                Field::inst( 'user' )
                 //->validator( 'Validate::required' )
                ->options( Options::inst()
                    ->table( "books_users" )
                    ->value( "user_id" )
                    ->label( "user_id" )
                )
            )
    )

    ->process( $_POST )
    ->json();