Joining tables with lookup table

peterbrowne

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();

peterbrowne

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();

peterbrowne

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"
                } ]
            } );

peterbrowne

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:

allan

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