Mjoin - return multiple columns and concatenate

peterbrowne

I have the following Mjoin and want to add another column to be returned. Then concatenate the two returned columns - unit_code and unit_name. Is this possible in the code?

->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_name asc' )
            ->fields(
                Field::inst( 'unit_pk' )
                    ->options( Options::inst()
                        ->table( 'unit' )
                        ->value( 'unit_pk' )
                        ->label( 'unit_name' )              
                    ),
                Field::inst( 'unit_name' )
            )
    )

so something like:

->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_name asc' )
            ->fields(
                Field::inst( 'unit_pk' )
                    ->options( Options::inst()
                        ->table( 'unit' )
                        ->value( 'unit_pk' )
                        ->label( 'unit_name' )              
                    ),
                Field::inst( 'unit_code'  +  '  '  +  'unit_name' )
            )
    )   

rf1234

->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_name asc' )
        ->fields(
            Field::inst( 'unit_pk' )
                ->options( Options::inst()
                    ->table( 'unit' )
                    ->value( 'unit_pk' )
                    ->label( 'unit_name' )             
                ),
            Field::inst( 'unit_code' )->set( false ),
            Field::inst( 'unit_name AS unit_code_with_name' )->set( false )
                ->getFormatter( function($val, $data, $opts) {
                    return $data['unit_ocde'] . ' ' . $val;
                })
        )
    )

This returns your new field "unit_code_with_name".

peterbrowne

Well thanks for this.

In the datatable section of code, I have:

var table = $( '#unit_outcome_table' ).DataTable( {
                responsive: true,
                ajax: "program_data/unit_outcome_data.php",
                dom: "Blfrtip",
                columns: [ {
                data: "unit_outcome.unit_outcome",
                width: '50%'
                }, {
                data: "unit",
                render: "[, ].unit_code_with_name"
                }, {
                data: "unit_outcome.blooms_taxonomy"
                }, {
                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: []
            } );

which renders unit_code_with_name just fine.

However, in the editor section,

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_code_with_name",
                type: "select",
                placeholder: 'No selection',
                placeholderDisabled: false,
                placeholderValue: 0,
                multiple: true
            }, {
                    label: "Blooms Taxonomy:",
                    name: "unit_outcome.blooms_taxonomy",
                    type:  "select",
                    placeholder: 'Select an option...',
                    placeholderDisabled: false,
                    placeholderValue: 0,
                     options: [
                         { label: "Knowledge", value:'knowledge' },
                         { label: "Comprehension", value: 'comprehension' },
                         { label: "Application", value: 'application' },
                         { label: "Analysis", value: 'analysis' },
                         { label: "Synthesis", value: 'synthesis' },
                         { label: "Evaluation", value: 'evaluation' }
                        ]
                }, {
                label: "Program Outcomes:",
                name: "program_outcome[].program_outcome_pk",
                type: "select",
                placeholder: 'No selection',
                placeholderDisabled: false,
                placeholderValue: 0,
                multiple: true
            } ]
            } );

I don't get anything showing in the Units select list.

I have:

        <div id="unit_outcome_form">
            <editor-field name="unit_outcome.unit_outcome"></editor-field>
            <editor-field name="unit[].unit_code_with_name"></editor-field>
            <editor-field name="unit_outcome.blooms_taxonomy"></editor-field>
            <editor-field name="program_outcome[].program_outcome_pk"></editor-field>
        </div>

peterbrowne

I'm obviously doing something wrong as the datatables view is fine with the unit code and name being concatenated...

allan

The field to edit in Editor is unit[].unit_pk. You don't want to reference unit_code_with_name in Editor since that is not the edit point. Indeed, @rf123's code has ->set(false) which I heartly agree with.

If you need to be able to edit the MJoined data in the Editor, use nested editing.

Allan

peterbrowne

Thanks for the reply Allan.

Using the below shows the units in the editor select now, but just the unit_name, but not prefixed with the unit code:

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
            }, 

peterbrowne

image of the editor:

peterbrowne

The concatenated unit_code and unit_name in the table is fine...

peterbrowne

so what do I change to get the unit code and the unit name concatenated in the unit editor field?

rf1234

You need a renderer for your options instance. Here is one from my own coding. Feel free to adapt to whatever is suitable for you.

In this case the rendered label that is shown to the user is even from multiple database tables. So I need a join. I think the Editor options instance still doesn't have a join method so I did a 1990's style join through the WHERE clause. Works

Field::inst( 'proposal.govdept_id' )->validator( 'Validate::notEmpty', array('message' => $msg[0]) )
        ->options( Options::inst()
            ->table('govdept, gov')
            ->value('govdept.id')
            ->label( array('gov.name', 'govdept.name', 'gov.type', 'gov.regional_12') )
            ->render( function ( $row ) {               
                return $row['gov.name'].' / '.$row['govdept.name'].' ('.$row['gov.regional_12'].'); '
                        .renderGovType($row['gov.type']); 
            } )
            ->order( 'gov.name asc' )
            //where clause MUST be a closure function in Options!!!
            ->where( function($q) {
                //only govs that are already clients can be selected
                $q ->where( function($r) {
                    $r ->where( function($s) {
                        $s ->where('gov.is_client_fin', 1 );
                        $s ->or_where('gov.is_client_marketplace', 1 );
                    });
                    $r ->where('govdept.gov_id', 'gov.id', '=', false); //join
                });
            } )
        ),

Take a look at these methods
"table" also works with multiple tables that need to be joined
"label" works with multiple fields from the tables above
"render" works with the "label" fields
"where" that is actually the join (an INNER JOIN to be precise: INNER JOIN govdept ON govdept.gov_id = gov.id - "gov" means government agency that can have multiple "govdept" departments.)

peterbrowne

Thanks. But your server side code works for the datatable, so those concatenated columns are showing fine there. Eg:

, {
                data: "unit",
                render: "[, ].unit_code_with_name"
                },

But not in the Units select field in the Editor.

The problem then seems to be with rendering the concatenated columns in that client side code e.g. the Unit field:

, {
                label: "Units:",
                name: "unit[].unit_pk",
                type: "select",
                placeholder: 'No selection',
                placeholderDisabled: false,
                placeholderValue: 0,
                multiple: true
            },

rf1234

But your server side code works for the datatable, so those concatenated columns are showing fine there.

Fine, but that is irrelevant for the options displayed in Editor. The options are NOT the data table content! Funny ... I got confused with that too some time ago. Psycho logic isn't logic unfortunately. We all get trapped with these things every once in a while, I guess.

The problem then seems to be with rendering the concatenated columns in that client side code

No, you must do the rendering in the server side code and that is what my example is about. (see "label" and "render" above in my PHP code).

This is the client side code of my example above. As you can see: No rendering there. It is all done on the server.

table: "#tblProposalCred",
fields: [ {
        label: lang === 'de' ? 'Für:' : 'For:',
        name:  "proposal.govdept_id", //render gov_name, govdept_name
        type: "selectize", 
        opts: {
            create: false,
            maxItems: 1,
            openOnFocus: false,
            allowEmptyOption: false,
            placeholder: lang === 'de' ? 'Bitte Abteilung auswählen' :
                                         'Please select a Department'
            }
    },

ok, my example is not an Mjoin but a simple join but the options instance works the same way.

And here I found a nice Mjoin with really complex rendering: ALL DONE ON THE SERVER

->join(
Mjoin::inst( 'predecessor' )
    ->link( 'fixed.id', 'fixed_has_predecessor.fixed_id' )
    ->link( 'predecessor.prefixed_id', 'fixed_has_predecessor.prefixed_predecessor_id' )
    ->order( 'serial, element, type asc' )
    ->fields(
        Field::inst( 'prefixed_id' )->set( false )
            ->options( Options::inst()
                ->table( 'predecessor' )
                ->value( 'prefixed_id' )
                ->label( array('serial', 'element', 'end_date', 'type', 'derivative', 'number') )
            //render serial, instrument, type, number
                ->render( function ( $row ) {               
                    return '# '.$row['serial']
                            .' / '.renderElement($row['element'])
                            .' / ' . getEnd() . getFormatterDate($row["end_date"])
                            .' / '.renderTypeDerivative($row['type'], $row['derivative'])
                            .' ('.$row['number'].')';
                } )
                ->order( 'end_date desc, serial asc, element asc, type asc' )
                //where clause MUST be a closure function in Options!!!
                ->where( function($q) {
                    $q ->where( function($r) {
                        if ( isset($_SESSION['govdept_id'] ) ) {
                            $r ->where('govdept_id', $_SESSION['govdept_id'] );
                        } else {
                            $r ->where('govdept_id', 0 ); //if we have no gov entity we don't want any contracts to be returned!!
                        }
                    });
                } )
            ),

And the Javascript of the Mjoin:

}, {
    label: lang === 'de' ? 
            'Wählen Sie ein oder mehrere Vorläufer-Geschäfte:' : 
            'Select one or more predecessor deals:',
    name:  "predecessor[].prefixed_id", //render serial, instrument, type, number
    type: "select",
    multiple: true,
    placeholderDisabled: false,
    placeholder: lang === 'de' ? 
            'Kein Vorläufer vorhanden bzw. ausgewählt' : 'No predecessor exists or is selected'
}

This rendering here comes from the server:

allan

Oh! You want to use the rendered value in the dropdown select options? Yup, rf1234 is spot on as usual - use the ->render() option of the Options class. That class will get the list of options to display - it does not use them directly from the DataTable.

Allan

peterbrowne

Mmm, actually I already had a solution in the existing code.

->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( array('type', 'program_outcome') )
                        ->render( function ( $row ) {
                               return $row['type'] . ' - ' . $row['program_outcome'];
                 } )
                        ->order( 'type' )
                    ),
                Field::inst( 'program_outcome' )
            )
    )

peterbrowne

So I just used that and modified it for the unit table:

    ->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_name asc' )
            ->fields(
                Field::inst( 'unit_pk' )
                    ->options( Options::inst()
                        ->table( 'unit' )
                        ->value( 'unit_pk' )
                        ->label( array('unit_code', 'unit_name') )
                        ->render( function ( $row ) {
                               return $row['unit_code'] . ' ' . $row['unit_name'];
                 } )
                        ->order( 'unit_code' )
                    ),
                Field::inst( 'unit_code' )
            )
        )

Works fine and I only need to show the unit_code in the datatable (esp given the width of that column).

peterbrowne

Thanks guys for your help - greatly appreciated!