PHP Validation on mjoin

rlangille2

Hello,

Can somebody please show me how to write a validation to check if no option was selected for a multi-select drop down, and return a message on the form? I can't seem to do it with the mjoin. The value I want to check is Field::inst('DataSourceID').

->Join(
        Mjoin::inst( 'DataSources' )
            ->link( 'Templates.TemplateID', 'TemplateDataSources.TemplateID' )
            ->link( 'DataSources.DataSourceID', 'TemplateDataSources.DataSourceID' )
            ->order( 'DataSourceID asc' )
            ->fields(
                Field::inst( 'DataSourceID' )
                    ->validator( Validate::required() )
                    ->options( Options::inst()
                        ->table( 'DataSources' )
                        ->value( 'DataSourceID' )
                        ->label( 'DataSource' )
                        ->where( function ($r) {
                            $r->where( 'Enabled', '1', '=' );
                        })
                        ->order( 'DataSource' )
                    ),
                Field::inst( 'DataSource' ),
                Field::inst( 'Enabled' )
                    )
        )

allan

Hi,

You'd need to use a global validator for that. The field validators in the Mjoin just run on the individual inputs.

Allan

rlangille2

Thank you,

I checked out the global validator documentation but I am so far unable to get it working. Where in my php should I place this example..

->validator( function ( $editor, $action, $data ) {
    if ( $action === Editor::ACTION_CREATE || $action === Editor::ACTION_EDIT ) {
        foreach ( $data['data'] as $pkey => $values ) {
            if ( $values['country'] === 'US' && $values['location'] === 'London UK' ) {
                return 'London UK is not in the US';
            }
        }
    }
} )

There is no mjoin in the example provided, so I am unsure where in the code it should go.

<?php

include( "../DataTables/Editor-PHP/DataTables.php" );

use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions;

Editor::inst( $db, 'Templates', 'TemplateID' )
    ->field(
        Field::inst( 'Templates.ReportName' ),
        Field::inst( 'Templates.ReportTitle' ),
        Field::inst( 'Templates.ReportTypeID' )
            ->options( Options::inst()
                ->table ( 'ReportTypes' )
                ->value ( 'ReportTypeID' )
                ->label ( 'ReportType' )
              ),
        Field::inst ( 'ReportTypes.ReportType' ),
        Field::inst( 'Templates.ReportDesc' ),
        Field::inst ( 'Templates.ReportPath' ),
        Field::inst( 'Templates.FrequencyID' )
        ->options( 'Frequencies', 'FrequencyID', 'Frequency', function ($q) {
        $q->where( 'Enabled', '1', '=' );
        } ),
        Field::inst( 'Frequencies.Frequency' ),
        Field::inst( 'Templates.DailyOption' ),
        Field::inst( 'Templates.DO_Day' ),
        Field::inst( 'Templates.WO_Week' ),
        Field::inst( 'Templates.MonthlyOption' ),
        Field::inst( 'Templates.MO_Day' ),
        Field::inst( 'Templates.MO_Day_Month' ),
        Field::inst( 'Templates.MO_The_Week' ),
        Field::inst( 'Templates.MO_The_Day' ),
        Field::inst( 'Templates.MO_The_Month' ),
        Field::inst( 'Templates.MO_LastDay' ),
        Field::inst( 'Templates.MO_BusinessDay' ),
        Field::inst( 'Templates.MO_BusinessDay_Month' ),
        Field::inst( 'Templates.YO_Year' ),
        Field::inst( 'Templates.YearlyOption' ),
        Field::inst( 'Templates.YO_On_Month' ),
        Field::inst( 'Templates.YO_On_Day' ),
        Field::inst( 'Templates.YO_OnThe_Day' ),
        Field::inst( 'Templates.YO_OnThe_Weekday' ),
        Field::inst( 'Templates.YO_OnThe_Month' ),
        Field::inst( 'Templates.StartDateInbound' ),
        Field::inst( 'Templates.StartDateOutbound' ),
        Field::inst( 'Templates.EndOption' ),
        Field::inst( 'Templates.EndAfterThisManyInstances' ),
        Field::inst( 'Templates.EndByThisDate' ),
        Field::inst( 'Templates.RecipientID' )
        ->options( 'PersonInfo', 'PersonRoleID', 'FullName', function ($q) {
        $q->where( 'RoleID', '4', '=' );
        } ),
        Field::inst( 'PersonInfo.FullName' ),
        Field::inst ( 'Templates.SendingInstructions' ),
        Field::inst ( 'Templates.Enabled' )
        )
->leftJoin( 'ReportTypes', 'ReportTypes.ReportTypeID', '=', 'Templates.ReportTypeID' )
->leftJoin( 'Frequencies', 'Frequencies.FrequencyID', '=', 'Templates.FrequencyID' )
->leftJoin( 'PersonInfo', 'PersonInfo.PersonRoleID', '=', 'Templates.RecipientID' )
 ->Join(
        Mjoin::inst( 'PersonInfo' )
        ->where( function ($q) {
                            $q->where( 'RoleID', '1', '=' );
                        })
            ->name( 'PersonInfoAnalystTemplates' )
            ->link( 'Templates.TemplateID', 'AnalystTemplates.TemplateID' )
            ->link( 'PersonInfo.PersonRoleID', 'AnalystTemplates.PersonRoleID' )
            ->order( 'PersonID asc' )
            ->fields(
                Field::inst( 'PersonRoleID' )
                    ->validator( Validate::required() )
                    ->options( Options::inst()
                        ->table( 'PersonInfo' )
                        ->value( 'PersonRoleID' )
                        ->label( 'FullName' )
                        ->where( function ($r) {
                            $r->where( 'RoleID', '1', '=' );
                        })
                    ),
                Field::inst( 'PersonID' ),
                Field::inst( 'FullName' ),
                Field::inst( 'RoleID' )
                    )
        )
 ->Join(
        Mjoin::inst( 'PersonInfo' )
                ->where( function ($q) {
                            $q->where( 'RoleID', '2', '=' );
                        })
            ->name( 'PersonInfoSubmittingManagerTemplates' )
            ->link( 'Templates.TemplateID', 'SubmittingManagerTemplates.TemplateID' )
            ->link( 'PersonInfo.PersonRoleID', 'SubmittingManagerTemplates.PersonRoleID' )
            ->order( 'PersonID asc' )
            ->fields(
                Field::inst( 'PersonRoleID' )
                    ->validator( Validate::required() )
                    ->options( Options::inst()
                        ->table( 'PersonInfo' )
                        ->value( 'PersonRoleID' )
                        ->label( 'FullName' )
                        ->where( function ($r) {
                            $r->where( 'RoleID', '2', '=' );
                        })
                    ),
                Field::inst( 'PersonID' ),
                Field::inst( 'FullName' ),
                Field::inst( 'RoleID')
                    )
        )
 ->Join(
        Mjoin::inst( 'PersonInfo' )
                ->where( function ($q) {
                            $q->where( 'RoleID', '3', '=' );
                        })
            ->name( 'PersonInfoApprovingManagerTemplates' )
            ->link( 'Templates.TemplateID', 'ApprovingManagerTemplates.TemplateID' )
            ->link( 'PersonInfo.PersonRoleID', 'ApprovingManagerTemplates.PersonRoleID' )
            ->order( 'PersonID asc' )
            ->fields(
                Field::inst( 'PersonRoleID' )
                    ->validator( Validate::required() )
                    ->options( Options::inst()
                        ->table( 'PersonInfo' )
                        ->value( 'PersonRoleID' )
                        ->label( 'FullName' )
                        ->where( function ($r) {
                            $r->where( 'RoleID', '3', '=' );
                        })
                    ),
                Field::inst( 'PersonID' ),
                Field::inst( 'FullName' ),
                Field::inst( 'RoleID')
                    )
        )
 ->Join(
        Mjoin::inst( 'PersonInfo' )
                ->where( function ($q) {
                            $q->where( 'RoleID', '5', '=' );
                        })
            ->name( 'PersonInfoResponsibleStaffTemplates' )
            ->link( 'Templates.TemplateID', 'ResponsibleStaffTemplates.TemplateID' )
            ->link( 'PersonInfo.PersonRoleID', 'ResponsibleStaffTemplates.PersonRoleID' )
            ->order( 'PersonID asc' )
            ->fields(
                Field::inst( 'PersonRoleID' )
                    ->validator( Validate::required() )
                    ->options( Options::inst()
                        ->table( 'PersonInfo' )
                        ->value( 'PersonRoleID' )
                        ->label( 'FullName' )
                        ->where( function ($r) {
                            $r->where( 'RoleID', '5', '=' );
                        })
                    ),
                Field::inst( 'PersonID' ),
                Field::inst( 'FullName' ),
                Field::inst( 'RoleID')
                    )
        )
 ->Join(
        Mjoin::inst( 'DataSources' )
            ->link( 'Templates.TemplateID', 'TemplateDataSources.TemplateID' )
            ->link( 'DataSources.DataSourceID', 'TemplateDataSources.DataSourceID' )
            ->order( 'DataSourceID asc' )
            ->fields(
                Field::inst( 'DataSourceID' )
                    ->validator( Validate::required() )
                    ->options( Options::inst()
                        ->table( 'DataSources' )
                        ->value( 'DataSourceID' )
                        ->label( 'DataSource' )
                        ->where( function ($r) {
                            $r->where( 'Enabled', '1', '=' );
                        })
                        ->order( 'DataSource' )
                    ),
                Field::inst( 'DataSource' ),
                Field::inst( 'Enabled' )
                    )
        )
    ->process($_POST)
    ->json();

allan

It would go just before the ->process() method normally. It could go anywhere in the Editor chain before that method, but that's typically where I've put it myself.

If you do:

->validator( function ( $editor, $action, $data ) {
    if ( $action === Editor::ACTION_CREATE || $action === Editor::ACTION_EDIT ) {
        foreach ( $data['data'] as $pkey => $values ) {
            print_r( $values );
        }
    }
} )

You'll be able to see the data structure for your configuration. It will cause an invalid JSON error (system error in Editor), but using the network inspector you'll be able to see the data and use that to form the validator needed.

If you need a hand with that, let me know - if you could show the printed debug statement that would be useful.

Allan

rlangille2

Thanks Allan,

I am struggling with using the validator function. I just want the validation rule to not allow the form to be submitted if no DataSource is selected (you can find the field at the very bottom of the output below.)

Array
(
    [Templates] => Array
        (
            [ReportName] => TEST5RYAN
            [ReportTitle] => 
            [ReportTypeID] => 3
            [ReportDesc] => 
            [ReportPath] => 
            [FrequencyID] => 8
            [DailyOption] => Every weekday
            [DO_Day] => 0
            [WO_Week] => 0
            [MonthlyOption] => 
            [MO_Day] => 0
            [MO_Day_Month] => 0
            [MO_The_Week] => first
            [MO_The_Day] => Sunday
            [MO_The_Month] => 0
            [MO_LastDay] => 0
            [MO_BusinessDay] => 0
            [MO_BusinessDay_Month] => 0
            [YO_Year] => 0
            [YearlyOption] => 
            [YO_On_Month] => January
            [YO_On_Day] => 0
            [YO_OnThe_Day] => first
            [YO_OnThe_Weekday] => Sunday
            [YO_OnThe_Month] => January
            [StartDateInbound] => 2018-05-23
            [StartDateOutbound] => 2018-05-23
            [EndOption] => No end date
            [EndAfterThisManyInstances] => 0
            [EndByThisDate] => 2018-05-23
            [RecipientID] => 23
            [SendingInstructions] => 
            [Enabled] => 1
        )

    [PersonInfoAnalystTemplates] => Array
        (
            [0] => Array
                (
                    [PersonRoleID] => 19
                )

        )

    [PersonInfoAnalystTemplates-many-count] => 1
    [PersonInfoSubmittingManagerTemplates] => Array
        (
            [0] => Array
                (
                    [PersonRoleID] => 24
                )

        )

    [PersonInfoSubmittingManagerTemplates-many-count] => 1
    [PersonInfoApprovingManagerTemplates] => Array
        (
            [0] => Array
                (
                    [PersonRoleID] => 21
                )

        )

    [PersonInfoApprovingManagerTemplates-many-count] => 1
    [PersonInfoResponsibleStaffTemplates] => Array
        (
            [0] => Array
                (
                    [PersonRoleID] => 26
                )

        )

    [PersonInfoResponsibleStaffTemplates-many-count] => 1
    [DataSources] => Array
        (
            [0] => Array
                (
                    [DataSourceID] => 1
                )

        )

    [DataSources-many-count] => 1
)

allan

This should do it I believe:

->validator( function ( $editor, $action, $data ) {
    if ( $action === Editor::ACTION_CREATE || $action === Editor::ACTION_EDIT ) {
        foreach ( $data['data'] as $pkey => $values ) {
            if ( isset( $values['DataSources-many-count'] ) && $values['DataSources-many-count'] == 0 ) {
              return 'Must select at least one data source';
            }
        }
    }
} )

Allan

rlangille2

Hi Allan,

Yes that worked. Here is the complete code so others can benefit if they have a similar question.

Thank you!

<?php

include( "../DataTables/Editor-PHP/DataTables.php" );

use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions;

Editor::inst( $db, 'Templates', 'TemplateID' )
    ->field(
        Field::inst( 'Templates.ReportName' ),
        Field::inst( 'Templates.ReportTitle' ),
        Field::inst( 'Templates.ReportTypeID' )
            ->options( Options::inst()
                ->table ( 'ReportTypes' )
                ->value ( 'ReportTypeID' )
                ->label ( 'ReportType' )
              ),
        Field::inst ( 'ReportTypes.ReportType' ),
        Field::inst( 'Templates.ReportDesc' ),
        Field::inst ( 'Templates.ReportPath' ),
        Field::inst( 'Templates.FrequencyID' )
        ->options( 'Frequencies', 'FrequencyID', 'Frequency', function ($q) {
        $q->where( 'Enabled', '1', '=' );
        } ),
        Field::inst( 'Frequencies.Frequency' ),
        Field::inst( 'Templates.DailyOption' ),
        Field::inst( 'Templates.DO_Day' ),
        Field::inst( 'Templates.WO_Week' ),
        Field::inst( 'Templates.MonthlyOption' ),
        Field::inst( 'Templates.MO_Day' ),
        Field::inst( 'Templates.MO_Day_Month' ),
        Field::inst( 'Templates.MO_The_Week' ),
        Field::inst( 'Templates.MO_The_Day' ),
        Field::inst( 'Templates.MO_The_Month' ),
        Field::inst( 'Templates.MO_LastDay' ),
        Field::inst( 'Templates.MO_BusinessDay' ),
        Field::inst( 'Templates.MO_BusinessDay_Month' ),
        Field::inst( 'Templates.YO_Year' ),
        Field::inst( 'Templates.YearlyOption' ),
        Field::inst( 'Templates.YO_On_Month' ),
        Field::inst( 'Templates.YO_On_Day' ),
        Field::inst( 'Templates.YO_OnThe_Day' ),
        Field::inst( 'Templates.YO_OnThe_Weekday' ),
        Field::inst( 'Templates.YO_OnThe_Month' ),
        Field::inst( 'Templates.StartDateInbound' ),
        Field::inst( 'Templates.StartDateOutbound' ),
        Field::inst( 'Templates.EndOption' ),
        Field::inst( 'Templates.EndAfterThisManyInstances' ),
        Field::inst( 'Templates.EndByThisDate' ),
        Field::inst( 'Templates.RecipientID' )
        ->options( 'PersonInfo', 'PersonRoleID', 'FullName', function ($q) {
        $q->where( 'RoleID', '4', '=' );
        } ),
        Field::inst( 'PersonInfo.FullName' ),
        Field::inst ( 'Templates.SendingInstructions' ),
        Field::inst ( 'Templates.Enabled' )
        )
->leftJoin( 'ReportTypes', 'ReportTypes.ReportTypeID', '=', 'Templates.ReportTypeID' )
->leftJoin( 'Frequencies', 'Frequencies.FrequencyID', '=', 'Templates.FrequencyID' )
->leftJoin( 'PersonInfo', 'PersonInfo.PersonRoleID', '=', 'Templates.RecipientID' )
 ->Join(
        Mjoin::inst( 'PersonInfo' )
        ->where( function ($q) {
                            $q->where( 'RoleID', '1', '=' );
                        })
            ->name( 'PersonInfoAnalystTemplates' )
            ->link( 'Templates.TemplateID', 'AnalystTemplates.TemplateID' )
            ->link( 'PersonInfo.PersonRoleID', 'AnalystTemplates.PersonRoleID' )
            ->order( 'PersonID asc' )
            ->fields(
                Field::inst( 'PersonRoleID' )
                    ->validator( Validate::required() )
                    ->options( Options::inst()
                        ->table( 'PersonInfo' )
                        ->value( 'PersonRoleID' )
                        ->label( 'FullName' )
                        ->where( function ($r) {
                            $r->where( 'RoleID', '1', '=' );
                        })
                    ),
                Field::inst( 'PersonID' ),
                Field::inst( 'FullName' ),
                Field::inst( 'RoleID' )
                    )
        )
 ->Join(
        Mjoin::inst( 'PersonInfo' )
                ->where( function ($q) {
                            $q->where( 'RoleID', '2', '=' );
                        })
            ->name( 'PersonInfoSubmittingManagerTemplates' )
            ->link( 'Templates.TemplateID', 'SubmittingManagerTemplates.TemplateID' )
            ->link( 'PersonInfo.PersonRoleID', 'SubmittingManagerTemplates.PersonRoleID' )
            ->order( 'PersonID asc' )
            ->fields(
                Field::inst( 'PersonRoleID' )
                    ->validator( Validate::required() )
                    ->options( Options::inst()
                        ->table( 'PersonInfo' )
                        ->value( 'PersonRoleID' )
                        ->label( 'FullName' )
                        ->where( function ($r) {
                            $r->where( 'RoleID', '2', '=' );
                        })
                    ),
                Field::inst( 'PersonID' ),
                Field::inst( 'FullName' ),
                Field::inst( 'RoleID')
                    )
        )
 ->Join(
        Mjoin::inst( 'PersonInfo' )
                ->where( function ($q) {
                            $q->where( 'RoleID', '3', '=' );
                        })
            ->name( 'PersonInfoApprovingManagerTemplates' )
            ->link( 'Templates.TemplateID', 'ApprovingManagerTemplates.TemplateID' )
            ->link( 'PersonInfo.PersonRoleID', 'ApprovingManagerTemplates.PersonRoleID' )
            ->order( 'PersonID asc' )
            ->fields(
                Field::inst( 'PersonRoleID' )
                    ->validator( Validate::required() )
                    ->options( Options::inst()
                        ->table( 'PersonInfo' )
                        ->value( 'PersonRoleID' )
                        ->label( 'FullName' )
                        ->where( function ($r) {
                            $r->where( 'RoleID', '3', '=' );
                        })
                    ),
                Field::inst( 'PersonID' ),
                Field::inst( 'FullName' ),
                Field::inst( 'RoleID')
                    )
        )
 ->Join(
        Mjoin::inst( 'PersonInfo' )
                ->where( function ($q) {
                            $q->where( 'RoleID', '5', '=' );
                        })
            ->name( 'PersonInfoResponsibleStaffTemplates' )
            ->link( 'Templates.TemplateID', 'ResponsibleStaffTemplates.TemplateID' )
            ->link( 'PersonInfo.PersonRoleID', 'ResponsibleStaffTemplates.PersonRoleID' )
            ->order( 'PersonID asc' )
            ->fields(
                Field::inst( 'PersonRoleID' )
                    ->validator( Validate::required() )
                    ->options( Options::inst()
                        ->table( 'PersonInfo' )
                        ->value( 'PersonRoleID' )
                        ->label( 'FullName' )
                        ->where( function ($r) {
                            $r->where( 'RoleID', '5', '=' );
                        })
                    ),
                Field::inst( 'PersonID' ),
                Field::inst( 'FullName' ),
                Field::inst( 'RoleID')
                    )
        )
 ->Join(
        Mjoin::inst( 'DataSources' )
            ->link( 'Templates.TemplateID', 'TemplateDataSources.TemplateID' )
            ->link( 'DataSources.DataSourceID', 'TemplateDataSources.DataSourceID' )
            ->order( 'DataSourceID asc' )
            ->fields(
                Field::inst( 'DataSourceID' )
                    ->validator( Validate::required() )
                    ->options( Options::inst()
                        ->table( 'DataSources' )
                        ->value( 'DataSourceID' )
                        ->label( 'DataSource' )
                        ->where( function ($r) {
                            $r->where( 'Enabled', '1', '=' );
                        })
                        ->order( 'DataSource' )
                    ),
                Field::inst( 'DataSource' ),
                Field::inst( 'Enabled' )
                    )
        )
        ->validator( function ( $editor, $action, $data ) {
    if ( $action === Editor::ACTION_CREATE || $action === Editor::ACTION_EDIT ) {
        foreach ( $data['data'] as $pkey => $values ) {
            if ( isset( $values['DataSources-many-count'] ) && $values['DataSources-many-count'] == 0 ) {
              return 'Must select at least one data source';
            }
        }
    }
} )
    ->process($_POST)
    ->json();