Mjoin and 1-1 relationships based on field value

dynasoft

Hi

I am trying to get Editor to save selected data by user to 3 tables: GlobalSubsPacks, GlobalSubsPacksRelations, GlobalPacksDataParam

Here's my Model, how they relate and their respective fields:

GlobalSubsPacks:
ID

GlobalSubsPacksRelations (one GlobalSubsPacks to many GlobalSubsPacksRelations):
ID
SubPackType
ParamType
SubPackID stores GlobalSubsPacks.ID
ParamID stores GlobalPacksDataParam.ID

GlobalPacksDataParam (one GlobalPacksDataParam to one GlobalSubsPacks):
ID
MaximumUsageAllowed (decimal type)

GlobalSubsPacksRelations is a link table that holds a one to many relationship with other tables in my code elsewhere (GlobalSubsPacksRelations.ParamType != 1 if you like). However in the code below where GlobalSubsPacksRelations.ParamType = 1 the relationship is one to one with GlobalPacksDataParam. Can I avoid using an MJoin here since for GlobalPacksDataParam relation is 1-1. I tried using code #1 below but get error 'Unable to cast object of type 'System.Decimal' to type 'System.Collections.Generic.Dictionary`2[System.String,System.Object]'.' which is to be expected since an object is returned rather than a variable. However I get no data back from the server if I use code #2 below.

    HttpRequest formData = HttpContext.Current.Request;

    using (Database db = new Database(SetGetDbType2, SetGetDbConnection))
    {
        editor = new Editor(db, "GlobalSubsPacks", "GlobalSubsPacks.id").Model<SubsPacksDBModel.GlobalSubsPacks>("GlobalSubsPacks");
        editor.Field(new Field("GlobalSubsPacks.id")
            .Set(false)
        );

        Server code #1:
        editor.MJoin(new MJoin("GlobalPacksDataParam")
            .Model<SubsPacksDBModel.GlobalPacksDataParam>()
            .Name("GlobalPacksDataParam")
            .Link("GlobalSubsPacks.id", "GlobalSubsPacksRelations.SubPackID")
            .Link("GlobalPacksDataParam.id", "GlobalSubsPacksRelations.ParamID")
            .Where(q =>
                q.Where("GlobalSubsPacksRelations.ParamType", 1, "=")
            )
            .Order("GlobalPacksDataParam.id ASC")
            .Field(new Field("GlobalPacksDataParam.MaximumUsageAllowed")
                .GetFormatter((val, data) => CommonUtilities.IsNullOrEmpty(val) == true ? 0 : val)
                .Validator(Validation.Numeric())
                .SetFormatter((val, data) => CommonUtilities.IsNumeric(val) == false ? 0 : val)
            )
        );

        Server code #2:
        editor.Field(new Field("GlobalPacksDataParam.MaximumUsageAllowed")
            .GetFormatter((val, data) => CommonUtilities.IsNullOrEmpty(val) == true ? 0 : val)
            .Validator(Validation.Numeric())
            .SetFormatter((val, data) => CommonUtilities.IsNumeric(val) == false ? 0 : val)
        );
        editor.LeftJoin("GlobalSubsPacksRelations", "GlobalSubsPacksRelations.SubPackID", "=", "GlobalSubsPacks.id");
        editor.LeftJoin("GlobalPacksDataParam", "GlobalSubsPacksRelations.ParamID", "=", "GlobalPacksDataParam.id");            
   }

   editor.TryCatch(false);
   editor.Debug(true);
   editor.Process(formData);   

Any help would be welcome.

Edited by Allan - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

allan

which is to be expected since an object is returned rather than a variable

I don't understand this point I'm afraid. What is returning an object?

Certainly, if it is 1:1 through both tables, then you want to use a double left join as it will help performance. What does the JSON from the server return (since you have debug enabled)?

Allan

dynasoft

The Mjoin is passing an object

dynasoft

When updating the MJoin expects an object as the error msg indicates:

Unable to cast object of type 'System.Decimal' to type 'System.Collections.Generic.Dictionary`2[System.String,System.Object]'

dynasoft

I did not mention this but I also get duplicate entries for with code #2 above (double left join). I get single entries with code #1. Please advise.

Here's the sql:

SELECT [GlobalSubsPacks].[id] as 'GlobalSubsPacks.id', [GlobalPacksDataParam].[MaximumUsageAllowed] as 'GlobalPacksDataParam.MaximumUsageAllowed' FROM [GlobalSubsPacks] LEFT JOIN [GlobalSubsPacksRelations] ON [GlobalSubsPacksRelations].[SubPackID] = [GlobalSubsPacks].[id] LEFT JOIN [GlobalPacksDataParam] ON [GlobalSubsPacksRelations].[ParamID] = [GlobalPacksDataParam].[id] WHERE [GlobalSubsPacks].[id] IN (SELECT SubPackID FROM GlobalSubsPacksRelations WHERE SubPackType = 0)

dynasoft

Please advise

allan

Apologies, I'm still not getting the full picture. Could you show me the Javascript code for the DataTable and Editor please? A screenshot of the form and table would also be useful so I can try to understand what is happening and what you are trying to do.

You note at the start that you are trying to write data to three different database tables from a single form - is that correct? That isn't something that Editor is particularly good at - generally one Editor instance should be used per table, and any links to other tables should be done using foreign keys with select or similar.

Allan

dynasoft

"You note at the start that you are trying to write data to three different database tables from a single form"

Yes.

"any links to other tables should be done using foreign keys with select or similar."

I believe it is the case, as GlobalSubsPacksRelations holds foreign key ParamID that stores IDs from GlobalPacksDataParam.ID and foreign key SubPackID that stores IDs from GlobalSubsPacks.ID

This thread is a follow-up on this thread but deals with another issue, ie writing the values entered by a user to a table (GlobalPacksDataParam)) related to another table (GlobalSubsPacks) via a link table (GlobalSubsPacksRelations).

Please find below some code:

• JS:
  Editor:

            var editor = new $.fn.dataTable.Editor({

                destroy: true,
                ajax: {
                    url: '/Packages/CRUDPackages/',
                    type: 'POST',
                    async: true,
                    cache: false
                },
                table: '#tblDataTable',
                template: '#divEditorForm1',
                fields: [
                    {
                        label: '',
                        name: 'GlobalSubsPacks.id'
                    }, {
                        label: '@(lblo.lblMaximumUsageAllowed):',
                        name: 'GlobalPacksDataParam[].MaximumUsageAllowed',
                        data: function (row, type, val) {

                            if (row.GlobalPacksDataParam.length > 0) {
                                if (row.GlobalPacksDataParam[0].MaximumUsageAllowed == 0)
                                {
                                    return '';
                                }
                                else
                                {
                                    return row.GlobalPacksDataParam[0].MaximumUsageAllowed;
                                }
                            }
                        }
                    }
                ]
            });

DT:

            dataTable1 = $('#tblDataTable').DataTable( {

                dom: 'Bfrtip',
                ajax: {
                    url: '/Packages/CRUDPackages/',
                    type: 'GET',
                    dataType: 'json',
                    contentType: 'application/json; charset=utf-8',
                    async: true,
                    cache: false
                },
                columns: [
                    { data: 'GlobalSubsPacks.id' , //0
                        className: 'text-left'
                    },
                    { data: 'GlobalPacksDataParam[].MaximumUsageAllowed' , //16
                        className: 'text-left'
                    }
                ],
                select: true,
                buttons: [
                    { extend: 'create', editor: editor1 },
                    { extend: 'edit', editor: editor1 },
                    { extend: 'remove', editor: editor1 },
                    {
                        text: '@(lblo.lblRefresh)',
                        action: function (e, dt, node, config) {
                            dataTable1.ajax.reload(null, false);
                        }
                    }
                ]
            });

If I use Code #2 (ie use 2 leftjoins) I get duplicate results in my DT:

If I use Code #1, I get single values as expected:

The pictures show the names of the items in the table, but I removed the names fields in my initial code for clarity.

Thanks.

dynasoft

Please advise

allan

It is possible to have Editor write to multiple tables, but it isn't something we explicitly test for.

The way to do it, is to make sure that the data sent to the client-side includes the primary key for the joined table (or more specifically the value used for the join) and that it is submitted to the server on edit (typically as a hidden field).

Regarding the read aspect - if you are getting duplicate values, I've seen that happen when there is a left join onto a table which is not used in the field list. That might be the case with the GlobalSubsPacksRelations table here. Try adding a field from the table. Alternatively, you could use a VIEW to create a more complex join with two conditions on the join.

Regards,
Allan

dynasoft

Thanks. I will review things early next week.