Joining tables with lookup table

Joining tables with lookup table

peterbrownepeterbrowne Posts: 45Questions: 12Answers: 0
edited October 29 in Editor

I have a lookup table learning_event_presentation_lookup:

+---------------------------------------------------+-----------------------+--------------------+
| learning_event_presentation_lookup_pk | learning_event_fk | presentation_fk |
+---------------------------------------------------+-----------------------+--------------------+

This is to facilitate joining tables learning_event and presentation.

I need to use that table in a join in the following code. Currently I'm getting the error:

DataTables warning: table id=learning_event_table - An SQL error occurred: SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 'learning_event'

$( '#learning_event_table' ).DataTable( {
                ajax: "program_data/learning_event_data.php",
                dom: "Bfrtip",
                columns: [ {
                    data: "learning_event.learning_event_name"
                }, {
                    data: "learning_event.learning_event_outcome"
                }, {
                    data: "rdb_group.rdb_group_name"
                }, {
                    data: "presentation.presentation_name"
                }, {
                    data: "rotation_discipline_block.rotation_discipline_block_name"
                } ],
                select: {
                    style: 'os',
                    selector: 'td:first-child'
                },
                buttons: [ {
                    extend: "create",
                    editor: editor
                }, {
                    extend: "edit",
                    editor: editor
                }, {
                    extend: "remove",
                    editor: editor
                } ]
            } );

and

        Editor::inst( $db2, 'learning_event', 'learning_event_pk' )    
            ->field(
             Field::inst( 'learning_event.learning_event_name' ),
             Field::inst( 'learning_event.learning_event_outcome' ),
            Field::inst( 'presentation.presentation_name' ),
             Field::inst( 'learning_event.rdb_group_fk' )
                    ->options( Options::inst()
                        ->table( 'rdb_group' )
                        ->value( 'rdb_group_pk' )
                        ->label( 'rdb_group_name' )
                    )
                    ->validator( 'Validate::notEmpty' ),
                Field::inst( 'rdb_group.rdb_group_name' ),
                Field::inst( 'learning_event.rotation_discipline_block_fk' )
                    ->options( Options::inst()
                        ->table( 'rotation_discipline_block' )
                        ->value( 'rotation_discipline_block_pk' )
                        ->label( 'rotation_discipline_block_name' )
                    )
                    ->validator( 'Validate::dbValues' ),
                Field::inst( 'rotation_discipline_block.rotation_discipline_block_name' )

            )
            ->leftJoin( 'rdb_group', 'rdb_group.rdb_group_pk', '=', 'learning_event.rdb_group_fk' )
            ->leftJoin( 'rotation_discipline_block', 'rotation_discipline_block.rotation_discipline_block_pk', '=', 'learning_event.rotation_discipline_block_fk' )
            ->leftJoin( 'presentation', 'presentation.presentation_pk', '=', 'learning_event_presentation_lookup.presentation_fk' )
            ->leftJoin( 'learning_event', 'learning_event.learning_event_pk', '=', 'learning_event_presentation_lookup.learning_event_fk' )
            ->process($_POST)
            ->json();

This question has an accepted answers - jump to answer

Answers

  • peterbrownepeterbrowne Posts: 45Questions: 12Answers: 0
    edited October 29

    UPDATE

    OK, I looked at the documenation on using a linked table at:

    https://editor.datatables.net/examples/advanced/joinLinkTable.html

    So now I have the following which is not producing any errors and working correctly.

    Editor::inst( $db2, 'learning_event', 'learning_event_pk' )    
        ->field(
         Field::inst( 'learning_event.learning_event_name' ),
         Field::inst( 'learning_event.learning_event_outcome' ),
        Field::inst( 'presentation.presentation_name' ),
         Field::inst( 'learning_event.rdb_group_fk' )
                ->options( Options::inst()
                    ->table( 'rdb_group' )
                    ->value( 'rdb_group_pk' )
                    ->label( 'rdb_group_name' )
                )
                ->validator( 'Validate::notEmpty' ),
            Field::inst( 'rdb_group.rdb_group_name' ),
            Field::inst( 'learning_event.rotation_discipline_block_fk' )
                ->options( Options::inst()
                    ->table( 'rotation_discipline_block' )
                    ->value( 'rotation_discipline_block_pk' )
                    ->label( 'rotation_discipline_block_name' )
                )
                ->validator( 'Validate::dbValues' ),
            Field::inst( 'rotation_discipline_block.rotation_discipline_block_name' )
    
        )
        ->leftJoin( 'rdb_group', 'rdb_group.rdb_group_pk', '=', 'learning_event.rdb_group_fk' )
        ->leftJoin( 'rotation_discipline_block', 'rotation_discipline_block.rotation_discipline_block_pk', '=', 'learning_event.rotation_discipline_block_fk' )
        ->leftJoin( 'learning_event_presentation_lookup', 'learning_event.learning_event_pk', '=', 'learning_event_presentation_lookup.learning_event_fk' )
        ->leftJoin( 'presentation', 'learning_event_presentation_lookup.presentation_fk', '=', 'presentation.presentation_pk' )
        ->process($_POST)
        ->json();
    
  • peterbrownepeterbrowne Posts: 45Questions: 12Answers: 0
    edited October 29

    Except that in the editor template, presentation.presentation_name is not showing in the select.

    with

    <div id='learning_event_table_container'>
                <table id='learning_event_table' class='display' style="width:100%">
                    <thead>
                        <tr>
                            <th>Learning Event</th>
                            <th>Learning Event Outcomes</th>
                            <th>Presentations</th>
                            <th>Rotation/Discipline/Block Sub-group</th>
                            <th>Rotation/Discipline/Block</th>
                        </tr>
                    </thead>
                    <tbody>
                    </tbody>
                </table>
            </div>
            <div id="learning_event_form">
                <editor-field name="learning_event.learning_event_name"></editor-field>
                <editor-field name="learning_event.learning_event_outcome"></editor-field>
                <editor-field name="presentation.presentation_name"></editor-field>
                <editor-field name="learning_event.rdb_group_fk"></editor-field>
                <editor-field name="learning_event.rotation_discipline_block_fk"></editor-field>
            </div>
    

    and

    var editor = new $.fn.dataTable.Editor( {
                    ajax: "program_data/learning_event_data.php",
                    table: "#learning_event_table",
                    template: '#learning_event_form',
                    fields: [ {
                        label: "Learning Event:",
                        name: "learning_event.learning_event_name"
                    }, {
                        label: "Learning Event Outcomes:",
                        name: "learning_event.learning_event_outcome",
                        type: "ckeditorClassic"
                    }, {
                        label: "Presentations:",
                        name: "presentation.presentation_name",
                        type: "select"
                    },{
                        label: "Rotation/Discipline/Block Sub-group:",
                        name: "learning_event.rdb_group_fk",
                        type: "select",
                        placeholder: 'No selection',
                        placeholderDisabled: false,
                        placeholderValue: 0
                    }, {
                        label: "Rotation/Discipline/Block:",
                        name: "learning_event.rotation_discipline_block_fk",
                        type: "select"
                    } ]
                } );
    
  • peterbrownepeterbrowne Posts: 45Questions: 12Answers: 0
    edited October 29

    UPDATE 2

    Now using the following, which is good on the editor template side, but still can't get the names to show in the table for two columns from the link table:

    Editor::inst( $db2, 'learning_event', 'learning_event_pk' )    
        ->field(
         Field::inst( 'learning_event.learning_event_name' ),
         Field::inst( 'learning_event.learning_event_outcome' ),
        Field::inst( 'learning_event_presentation_lookup.presentation_fk' )
                ->options( Options::inst()
                    ->table( 'presentation' )
                    ->value( 'presentation_pk' )
                    ->label( 'presentation_name' )
                )
                ->validator( 'Validate::notEmpty' ),
            Field::inst( 'learning_event_mcondition_lookup.mcondition_fk' )
                ->options( Options::inst()
                    ->table( 'mcondition' )
                    ->value( 'mcondition_pk' )
                    ->label( 'mcondition_name' )
                )
                ->validator( 'Validate::notEmpty' ),
         Field::inst( 'learning_event.rdb_group_fk' )
                ->options( Options::inst()
                    ->table( 'rdb_group' )
                    ->value( 'rdb_group_pk' )
                    ->label( 'rdb_group_name' )
                )
                ->validator( 'Validate::notEmpty' ),
            Field::inst( 'rdb_group.rdb_group_name' ),
            Field::inst( 'learning_event.rotation_discipline_block_fk' )
                ->options( Options::inst()
                    ->table( 'rotation_discipline_block' )
                    ->value( 'rotation_discipline_block_pk' )
                    ->label( 'rotation_discipline_block_name' )
                )
                ->validator( 'Validate::dbValues' ),
            Field::inst( 'rotation_discipline_block.rotation_discipline_block_name' )
    
        )
        ->leftJoin( 'rdb_group', 'rdb_group.rdb_group_pk', '=', 'learning_event.rdb_group_fk' )
        ->leftJoin( 'rotation_discipline_block', 'rotation_discipline_block.rotation_discipline_block_pk', '=', 'learning_event.rotation_discipline_block_fk' )
        ->leftJoin( 'learning_event_presentation_lookup', 'learning_event.learning_event_pk', '=', 'learning_event_presentation_lookup.learning_event_fk' )
        ->leftJoin( 'presentation', 'learning_event_presentation_lookup.presentation_fk', '=', 'presentation.presentation_pk' )
        ->leftJoin( 'learning_event_mcondition_lookup', 'learning_event.learning_event_pk', '=', 'learning_event_mcondition_lookup.learning_event_fk' )
        ->leftJoin( 'mcondition', 'learning_event_mcondition_lookup.mcondition_fk', '=', 'mcondition.mcondition_pk' )
        ->process($_POST)
        ->json();
    

    and

            var editor = new $.fn.dataTable.Editor( {
                            ajax: "program_data/learning_event_data.php",
                            table: "#learning_event_table",
                            template: '#learning_event_form',
                            fields: [ {
                                label: "Learning Event:",
                                name: "learning_event.learning_event_name"
                            }, {
                                label: "Learning Event Outcomes:",
                                name: "learning_event.learning_event_outcome",
                                type: "ckeditorClassic"
                            }, {
                                label: "Presentations:",
                                name: "learning_event_presentation_lookup.presentation_fk",
                                type: "select",
                                placeholder: 'No selection',
                                placeholderDisabled: false,
                                placeholderValue: 0
                            }, {
                                label: "Conditions:",
                                name: "learning_event_mcondition_lookup.mcondition_fk",
                                type: "select",
                                placeholder: 'No selection',
                                placeholderDisabled: false,
                                placeholderValue: 0
                            }, {
                                label: "Rotation/Discipline/Block Sub-group:",
                                name: "learning_event.rdb_group_fk",
                                type: "select",
                                placeholder: 'No selection',
                                placeholderDisabled: false,
                                placeholderValue: 0
                            }, {
                                label: "Rotation/Discipline/Block:",
                                name: "learning_event.rotation_discipline_block_fk",
                                type: "select"
                            } ]
                        } );
    

    and

    $( '#learning_event_table' ).DataTable( {
                    ajax: "program_data/learning_event_data.php",
                    dom: "Bfrtip",
                    columns: [ {
                        data: "learning_event.learning_event_name"
                    }, {
                        data: "learning_event.learning_event_outcome"
                    }, {
                        data: "learning_event_presentation_lookup.presentation_fk"
                    }, {
                        data: "learning_event_mcondition_lookup.mcondition_fk"
                    },{
                        data: "rdb_group.rdb_group_name"
                    }, {
                        data: "rotation_discipline_block.rotation_discipline_block_name"
                    } ],
                    select: {
                        style: 'os',
                        selector: 'td:first-child'
                    },
                    buttons: [ {
                        extend: "create",
                        editor: editor
                    }, {
                        extend: "edit",
                        editor: editor
                    }, {
                        extend: "remove",
                        editor: editor
                    } ]
                } );
    

    However, in the table, only the ID (pk) of the prersentation and mcondition are showing, not the names. How do I get the names to display?

    In the Editor template everything shows OK:

  • allanallan Posts: 50,737Questions: 1Answers: 7,581 Site admin
    Answer ✓

    Going back to the original for a moment, the issue was with the use of

    ->leftJoin( 'learning_event', 'learning_event.learning_event_pk', '=', 'learning_event_presentation_lookup.learning_event_fk' )
    

    which is a self referencing join. That's possible, but you need to use an alias.

    However, in the table, only the ID (pk) of the prersentation and mcondition are showing, not the names. How do I get the names to display?

    You want to reference the joined data in your columns.data property. e.g.:

    data: 'presentation.presentation_name'
    

    note you'll need to add a Field::inst() on the server-side to get that field's data.

    Allan

Sign In or Register to comment.