Validating the selected values from a joined table

georgemcfly

I have an MJoin that joins a User table to a UserRoles table to a Roles table.
I want to perform some validation logic based on the selected Roles. For example, a new user can only be assigned the Admin role if the current user is an Admin.
I see examples of how to iterate through the DtRequest.Data to find individual field values in a server-side Validation method.
How do I iterate through the list of selected Roles values?

rf1234

You would need a global validator for that:
https://editor.datatables.net/manual/net/validation#Global-validators

Here is a simple example from PHP which should work very similarly in .NET. Editor sends "many-count" fields to the server for Mjoins. Just check your browser's payload. I use that to validate whether the user selected anything if it is mandatory to select something.

But you can also loop through the data in a much more complex way.

->validator( function ( $editor, $editorAction, $data ) use ( $msg ) {
    if ( $editorAction === Editor::ACTION_CREATE || 
         $editorAction === Editor::ACTION_EDIT       ) {
        foreach ( $data['data'] as $pkey => $values ) {
            if ( isset($values['gov-many-count']) ) {
                if ( $values['gov-many-count'] <= 0 ) {
                    return $msg[1];
                }
            }
        }
        return null;
    }
} )
->join(
Mjoin::inst( 'gov' )
    ->link( 'ctr_installation.id', 'ctr_installation_has_gov.ctr_installation_id' )
    ->link( 'gov.id', 'ctr_installation_has_gov.gov_id' )
    ->order( 'gov.name asc' )
    ->fields(
        Field::inst( 'id' )->set( false )                
            ->options( Options::inst()
                ->table('gov')
                ->value('id')
                ->label( array('name', 'regional_12') )
                ->render( function ( $row ) {   
                    return $row['name'] . ' ('.$row['regional_12'].')';
                } )
                ->order( 'name asc' )
            ),
        Field::inst( 'name' )->set( false ),
        Field::inst( 'regional_12' )->set( false )
    )
)

This global validator really loops through the Mjoin values. You can see the nested foreach loop that performs that job. It takes all the "govdept" values from the Mjoin and checks against database values.

->validator( function ( $editor, $editorAction, $data ) use ( $msg ) {
    if ( $editorAction === Editor::ACTION_CREATE || 
         $editorAction === Editor::ACTION_EDIT       ) {
        foreach ( $data['data'] as $pkey => $values ) {
            if ( $editorAction === Editor::ACTION_CREATE ) {
                $infomaId = 0;
            } else {
                $infomaId = (int)substr($pkey, 4);
            }                
            if ( $values['govdept-many-count'] <= 0 ) {
                return $msg[4];
            } else {
                foreach ( $values['govdept'] as $val ) {
                    $result = $editor->db()->raw()
                    ->bind( ':govdept_id',  $val["id"] ) 
                    ->bind( ':infoma_id',   $infomaId )
                    ->exec( 'SELECT COUNT(*) AS govdeptIsAssigned 
                               FROM govdept_has_infoma 
                              WHERE govdept_id  = :govdept_id
                                AND infoma_id  <> :infoma_id' );
                    $res = $result->fetch(PDO::FETCH_ASSOC);
                    if ( (bool)$res["govdeptIsAssigned"] ) {
                        return $msg[5];
                    }
                }
            }
        }
        return null;
    }
} )

georgemcfly

@rf1234 : Thanks for your quick response. I followed your lead and looped through the Roles dictionary looking for a specific role in the list.

                foreach (var row in dtRequest.Data)
                {
                    var fields = row.Value as Dictionary<string, object>;
                    var roles = fields["Roles"] as Dictionary<string, object>;
                    foreach (var role in roles)
                    {
                        var selectedRole = role.Value as Dictionary<string, object>;
                        string roleId = selectedRole?.Values.FirstOrDefault().ToString();
                        if (roleId == adminRole.ToString())
                        {
                            containsAdminRole = true;
                        }

                        if (roleId == ocAdminRole.ToString())
                        {
                            containsOcAdminRole = true;
                        }
                    }
                }

I was hoping it would be as simple as foreach(var role in fields["Roles"]) but alas...