Jquery DataTables -将表与查找表连接起来

Question

我有一个查找(或链接)表learning_event_presentation_lookup

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

这是为了方便连接表learning_event和presentation。

在下面的代码中，我需要在联接中使用该表。

目前，我正在收到错误：

DataTables警告:表id=learning_event_table -发生了id=learning_event_table错误: SQLSTATE42000:语法错误或访问冲突: 1066非唯一表/别名：“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
                } ]
            } );

和

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

Answer 1

好的，我查看了关于使用链接表的文档：

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

所以现在我有了以下内容，它没有产生任何错误，而且工作正常。不过，用dataTables格式进行联接并不是很直观.

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