Mjoin - Retreiving additional columns from joined table

Mjoin - Retreiving additional columns from joined table

peterbrownepeterbrowne Posts: 314Questions: 54Answers: 0
edited July 2021 in Editor

I have the following server and client scripts and need to retrieve additional columns from the joined table program_outcome. In this instance, the additional column is program_outcome.type.. How should I do this?

Server side:

Editor::inst( $db_cm_dopt, 'unit_outcome', 'unit_outcome_pk' )    
    ->field(
        Field::inst( 'unit_outcome.unit_outcome' ),
        Field::inst( 'unit_outcome.modified' ),
        Field::inst( 'unit_outcome.modified_by' )->setValue( $user )
    )
     ->join(
        Mjoin::inst( 'unit' )
            ->link( 'unit_outcome.unit_outcome_pk', 'unit_unit_outcome_lookup.unit_outcome_fk' )
            ->link( 'unit.unit_pk', 'unit_unit_outcome_lookup.unit_fk' )
            ->order( 'unit.unit_full_name asc' )
            ->fields(
                Field::inst( 'unit_pk' )
                    ->options( Options::inst()
                        ->table( 'unit' )
                        ->value( 'unit_pk' )
                        ->label( 'unit_full_name' )               
                    ),
                Field::inst( 'unit_full_name' )
            )
    )
     ->join(
        Mjoin::inst( 'program_outcome' )
            ->link( 'unit_outcome.unit_outcome_pk', 'program_outcome_unit_outcome_lookup.unit_outcome_fk' )
            ->link( 'program_outcome.program_outcome_pk', 'program_outcome_unit_outcome_lookup.program_outcome_fk' )
            ->order( 'program_outcome.program_outcome asc' )
            ->fields(
                Field::inst( 'program_outcome_pk' )
                    ->options( Options::inst()
                        ->table( 'program_outcome' )
                        ->value( 'program_outcome_pk' )
                        ->label( 'program_outcome' )
                        ->render( function ( $row ) {
            return strip_tags($row['program_outcome']);
        } )
                        ->order( 'program_outcome' )
                    ),
                Field::inst( 'program_outcome' )
            )
    )
    ->process($_POST)
    ->json();

This question has an accepted answers - jump to answer

Answers

  • peterbrownepeterbrowne Posts: 314Questions: 54Answers: 0

    Client side:

        var editor = new $.fn.dataTable.Editor( {
                    ajax: "program_data/unit_outcome_data.php",
                    table: "#unit_outcome_table",
                    template: '#unit_outcome_form',
                    fields: [ {
                        label: "Unit Outcome:",
                        name: "unit_outcome.unit_outcome",
                        type: "ckeditor"
                    }, {
                        label: "Units:",
                        name: "unit[].unit_pk",
                        type: "select",
                        placeholder: 'No selection',
                        placeholderDisabled: false,
                        placeholderValue: 0,
                        multiple: true
                    }, {
                        label: "Program Outcomes:",
                        name: "program_outcome[].program_outcome_pk",
                        type: "select",
                        placeholder: 'No selection',
                        placeholderDisabled: false,
                        placeholderValue: 0,
                        multiple: true
                    }]
                } );
    
    
                var table = $( '#unit_outcome_table' ).DataTable( {
                    responsive: true,
                    "autoWidth": false,
                    ajax: "program_data/unit_outcome_data.php",
                    dom: "Blfrtip",
                    columns: [ {
                        data: "unit_outcome.unit_outcome",
                        width: '50%'
                    }, {
                        data: "unit",
                        render: "[, ].unit_full_name"
                    }, {
                        data: "program_outcome",
                        render: "[, ].program_outcome"
                    }, {
                        data: "unit_outcome.modified"
                    }, {
                        data: "unit_outcome.modified_by"
                    }, {
                        data: null,
                        className: "center",
                        defaultContent: '<a href="" class="editor_edit">Edit</a>'
                    }],
                    select: {
                        style: 'os',
                        selector: 'td:first-child'
                    },
                    buttons: []
                } );
    
  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin
    Answer ✓

    Hi,

    At this time the only way to do this is to use a VIEW for the table you are Mjoin-ing onto. We don't yet have the ability to do a left join off the Mjoin class - although that is something we plan to add in future.

    Regards,
    Allan

Sign In or Register to comment.