Getting data from reference table using FK - MJoin

patrick008

I am trying to get the Name field from TlkUserRoleType' table which is being referenced in 'UserRole' table using 'MJoin'.

UserRole & TlkUserRolesType tables are linked using FK RoleID in UserRole table.

Here's my Json response code:

     var response = new Editor(db, "UserRole")
          .Model<UserRoleModel>()              
           .Field(new Field("UserRole.ID"))
           .Field(new Field("UserRole.UserID"))
           .Field(new Field("UserRole.RoleID"))
           .Field(new Field("UserRole.SupervisorUserID"))
           .Field(new Field("UserRole.ApprovedByUserID"))
           .Field(new Field("UserRole.ActivatedDate"))
           .Field(new Field("UserRole.IsActive"))
           .Field(new Field("UserRole.DeactivatedDate"))
          .MJoin(new MJoin("TlkUserRolesType")
                .Link("UserRole.RoleID", "TlkUserRolesType.ID")
                .Field(new Field("TlkUserRolesType.Name"))
          )
          .Process(request)
          .Data();

When the table is rendered, it does not give me the 'Name' value from TlkUserRolesType. I noticed that in Json Response, the last field is coming as a collection of objects like below:

[0]: {[DT_RowId, row_1]}
[1]: {[UserRole, System.Collections.Generic.Dictionary`2[System.String,System.Object]]}
[2]: {[id, 1]}
[3]: {[userid, 1]}
[4]: {[roleid, 1]}
[5]: {[supervisoruserid, 2]}
[6]: {[approvedbyuserid, 3]}
[7]: {[activateddate, 6/2/2017 12:00:00 AM]}
[8]: {[isactive, True]}
[9]: {[deactivateddate, ]}
[10]: {[TlkUserRolesType, System.Collections.Generic.List`1[System.Object]]}

Is there anything I should be changing in my code?

Thank you!

rf1234

that is what Mjoin does: it returns an array of objects. You seem to only want a normal left join. This example has both: an mjoin to show / select an array of permissions and a left join to show / select exactly one site.
https://editor.datatables.net/examples/advanced/joinArray.html

Editor::inst( $db, 'users' )
    ->field(
        Field::inst( 'users.first_name' ),
        Field::inst( 'users.last_name' ),
        Field::inst( 'users.site' )
            ->options( Options::inst()
                ->table( 'sites' )
                ->value( 'id' )
                ->label( 'name' )
            ),
        Field::inst( 'sites.name' )
    )
    ->leftJoin( 'sites', 'sites.id', '=', 'users.site' )
    ->join(
        Mjoin::inst( 'permission' )
            ->link( 'users.id', 'user_permission.user_id' )
            ->link( 'permission.id', 'user_permission.permission_id' )
            ->order( 'name asc' )
            ->fields(
                Field::inst( 'id' )
                    ->validator( 'Validate::required' )
                    ->options( Options::inst()
                        ->table( 'permission' )
                        ->value( 'id' )
                        ->label( 'name' )
                    ),
                Field::inst( 'name' )
            )
    )
    ->process($_POST)
    ->json();

patrick008

Thank you, rf1234! I implemented the change and got one step closer.

Now, I see the Role Name in the 'Edit' window but the RoleName column is still blank in the grid itself. (Screenshots attached)

My method in controller is this:

            var response = new Editor(db, "UserRole")
                   .Field(new Field("UserRole.ID"))
                   .Field(new Field("UserRole.UserID"))
                   .Field(new Field("UserRole.RoleID"))
                   .Field(new Field("UserRole.SupervisorUserID"))
                   .Field(new Field("UserRole.ApprovedByUserID"))
                   .Field(new Field("UserRole.ActivatedDate"))
                   .Field(new Field("UserRole.IsActive"))
                   .Field(new Field("UserRole.DeactivatedDate"))              
                   .LeftJoin("TlkUserRolesType", "UserRole.RoleID", "=", "TlkUserRolesType.ID")
                  .Field(new Field("TlkUserRolesType.Name"))
          .Process(request)
          .Data();
           return Json(response);

And my javascript code for table is:

var editor = new $.fn.dataTable.Editor( {
    ajax: '/api/UserRole',
    table: '#UserRole',
    fields: [
        {
            "label": "ID:",
            "name": "UserRole.ID"
        },
        {
            "label": "UserID:",
            "name": "UserRole.UserID"
        },
        {
            "label": "RoleID:",
            "name": "UserRole.RoleID"
        },          
        {
            "label": "User Role:",
            "name": "UserRole.SupervisorUserID"
        },
        {
            "label": "ApprovedByUserID:",
            "name": "UserRole.ApprovedByUserID"
        },
        {
            "label": "ActivatedDate:",
            "name": "UserRole.ActivatedDate"


        },
        {
            "label": "IsActive:",
            "name": "UserRole.IsActive"
        },
        {
            "label": "DeactivatedDate:",
            "name": "UserRole.DeactivatedDate"

        },
        {
            "label": "Name:",
            "name": "TlkUserRolesType.Name"
        }
    ]
} );

var table = $('#UserRole').DataTable( {
    dom: 'Bfrtip',
    ajax: '/api/UserRole',
    columns: [
        {
            "data": "UserRole.ID"
        },
        {
            "data": "UserRole.UserID"
        },
        {
            "data": "UserRole.RoleID"
        },
        {
            "data": "UserRole.SupervisorUserID"
        },
        {
            "data": "UserRole.ApprovedByUserID"
        },
        {
            "data": "UserRole.ActivatedDate"
        },
        {
            "data": "UserRole.IsActive"
        },
        {
            "data": "UserRole.DeactivatedDate"
        },
        {
            "data": "TlkUserRolesType.Name"
        }
    ],
    select: true,
    lengthChange: false,
    buttons: [
        { extend: 'create', editor: editor },
        { extend: 'edit',   editor: editor },
        { extend: 'remove', editor: editor }
    ]
} );

} );

rf1234

To me all looks fine. Did you check the database whether the update of the field was really made?