MJoin and LeftJoin

dynasoft

Hi

I'm trying to come up with code that reads GlobalSubsPacks.Name & GlobalPacksVoiceParam.CustomerType from the following table schema:

Table GlobalSubsPacks with fields:
id
Name

Table GlobalSubsPacksRelations with fields:
id
SubPackID
ParamID

Table GlobalPacksVoiceParam with fields:
id
CustomerType

where GlobalSubsPacksRelations.SubPackID stores GlobalSubsPacks.id in a one-to-many relationship and,
GlobalSubsPacksRelations.ParamID stores GlobalPacksVoiceParam.id in a one-to-one relationship.

Here's the code I came up with:

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)
);
editor.Field(new Field("GlobalSubsPacks.Name")

);
editor.MJoin(new MJoin("CustomerSubsPacksRelations")
    .Model<SubsPacksDBModel.GlobalSubsPacksRelations>()
    .Name("GlobalSubsPacksRelations")
    .Link("GlobalSubsPacks.id", "GlobalSubsPacksRelations.SubPackID")
    .Order("GlobalSubsPacksRelations.id DESC")
    .Field(new Field("id"))
);
editor.Field(new Field("GlobalPacksVoiceParam.CustomerType")

);

editor.LeftJoin("GlobalPacksVoiceParam", "GlobalSubsPacksRelations.ParamID", "=", "GlobalPacksVoiceParam.ID");

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

}
`

I think the code should work above but the returned data is empty and no sql gets executed under debug in a browser's network tab. Model class holds the fields needed. I followed the indications given on page https://editor.datatables.net/manual/net/mjoin for the building of the MJoin. The one-to-many relationship was implemented with an MJoin and one-to-one with a LeftJoin. CustomerSubsPacksRelations is a link table

Kindly advise.

rf1234

Just wrote two SQL statements:

   SELECT a.Name, c.CustomerType
     FROM GlobalSubsPacks a
LEFT JOIN GlobalSubsPacksRelations b ON a.id = b.SubPackID
LEFT JOIN GlobalPacksVoiceParam c    ON b.ParamID = c.id
    WHERE c.id IS NOT NULL;
    
    
   SELECT a.Name, GROUP_CONCAT(c.CustomerType)
     FROM GlobalSubsPacks a
LEFT JOIN GlobalSubsPacksRelations b ON a.id = b.SubPackID
LEFT JOIN GlobalPacksVoiceParam c    ON b.ParamID = c.id
    WHERE c.id IS NOT NULL
 GROUP BY a.Name;

The first one would require two left joins in Editor and it would return:
Name1 Type1
Name1 Type2
Name1 Type3
Name2 Type1
Name3 Type3
and so on.

The second one is the Mjoin scenario:
Name1 Type1, Type2, Type3
Name2 Type1
Name3 Type 3

If you want the second scenario you would need one Mjoin and no left join in Editor at all. The problem is that your link table "GlobalSubsPacksRelations" isn't a classical link table because it doesn't contain just two foreign keys but has its own auto-increment id as well. This means: You can't use the Mjoin for inserts or updates. It won't work. But you can use it to read the information you require without a left join.

This is your Mjoin in PHP. You go figure out what it would be in .NET ...

->join(
    Mjoin::inst( 'GlobalPacksVoiceParam' )
        ->link( 'GlobalSubsPacks.id', 'GlobalSubsPacksRelations.SubPackID' )
        ->link( 'GlobalPacksVoiceParam.id', 'GlobalSubsPacksRelations.ParamID' )
        ->order( 'GlobalSubsPacksRelations.id desc' )
        ->fields(
            Field::inst( 'GlobalPacksVoiceParam.CustomerType' )->set( false ),
            Field::inst( 'GlobalSubsPacksRelations.id' )->set( false ) 
        )
    )

I think you need to retrieve the GlobalSubsPacksRelations.id as well to make the order by work, but you might drop it as well.

dynasoft

Many thanks. Will remove the id primary key and test.

rf1234

Well, you don't have to remove it if you don't care about updating / inserting through the Mjoin. The way I wrote it in PHP makes sure it doesn't do updates / inserts.

Just like your "fake" link table I have a couple of them as well. This table is the link between user and government department and it holds the user's role for that department. Hence it isn't a "regular" link table because that would have no attributes just the two foreign keys.

And the Mjoin for this table. Showing all the user's departments and roles for each of it.

->join(
    Mjoin::inst( 'govdept' )
        ->link( 'user.id', 'govdept_has_user.user_id' )
        ->link( 'govdept.id', 'govdept_has_user.govdept_id' )
        ->order( 'govdept.name asc' )
        ->fields(
            Field::inst( 'govdept.name AS deptName' )->set( false ),
            Field::inst( 'govdept_has_user.role AS userRole' )->set( false )
        )
    )

And this is what it looks like in the data table:

And finally the Javascript to render it accordingly. The Javascript is one of the first things I ever wrote in that language ... not very elegant actually but it works

//            user financing roles
{   data: null,
    render: function ( data, type, row ) {
        var ix=0;
        var returnString = '';
        while (row.govdept[ix]) {
            if (ix !== 0) {
                returnString = returnString.concat('<br>');
            }
            returnString = returnString.concat
                (row.govdept[ix].deptName + ': '
                        + renderRole(row.govdept[ix].userRole));
            ix++;       
        }
        return returnString;
    }
},

dynasoft

Thanks.- Upadting the data via the MJoin is something I need. Thanks

dynasoft

Hi, I have modified my code but haven't been able to get it to work yet. Iit seems I had a redundant field in a class in my Model and couldn't understand why no data was showing. Only saw this now. But no data is showing yet. If I use LeftJoins as per your 1st suggestion, data shows:

editor.LeftJoin("GlobalSubsPacksRelations", "GlobalSubsPacks.id", "=", "GlobalSubsPacksRelations.SubPackID");
editor.LeftJoin("GlobalPacksVoiceParam", "GlobalSubsPacksRelations.ParamID", "=", "GlobalPacksVoiceParam.id");

A word to the people developing Editor: it would be good to have better error handling and a message thrown if a field exists in Models that don't exist in the db.

Here's my current code:

Controller:

Editor editor = null;
{
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)
);
editor.Field(new Field("GlobalSubsPacks.Name")

);

editor.MJoin(new MJoin("GlobalPacksVoiceParam") //*TODO* - use MJoins to get fields of linked tables everywhere
    .Model<SubsPacksDBModel.GlobalPacksVoiceParam>()
    .Name("GlobalPacksVoiceParam")
    .Link("GlobalSubsPacks.id", "GlobalSubsPacksRelations.SubPackID")
    .Link("GlobalPacksVoiceParam.id", "GlobalSubsPacksRelations.ParamID")
    .Where(q =>
        q.Where("GlobalSubsPacksRelations.ParamType", 0, "=")
    )
    .Order("GlobalSubsPacksRelations.SubPackID ASC")
    .Field(new Field("GlobalPacksDataParam.CustomerType")
        .SetFormatter((val, data) => CommonUtilities.IsNullOrEmpty(val) == true ? null : val)
    )
    .Field(new Field("GlobalPacksDataParam.NumberOfMinutesOrCalls")
        .GetFormatter((val, data) => CommonUtilities.IsNullOrEmpty(val) == true ? 0 : val)
        .Validator(Validation.Numeric())
        .SetFormatter((val, data) => CommonUtilities.IsNumeric(val) == false ? 0 : val)
    )
    .Field(new Field("GlobalPacksDataParam.HaveMinutesOrCalls")
        .GetFormatter((val, data) => CommonUtilities.IsNullOrEmpty(val) == true ? string.Empty : val)
        .SetFormatter((val, data) => CommonUtilities.IsNullOrEmpty(val) == true ? null : val)
    )
);

editor.Where(q =>
   q.Where("GlobalSubsPacks.id", "(SELECT SubPackID FROM GlobalSubsPacksRelations WHERE SubPackType = 0)", "IN", false)
);

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

}

Model:

public class SubsPacksDBModel
{
public class GlobalSubsPacks
{
public long id { get; set; }
public string Name { get; set; }
}

public class GlobalSubsPacksRelations
{
    public long id { get; set; }
    public int SubPackType { get; set; }
    public int ParamType { get; set; }
    public long SubPackID { get; set; }
    public long ParamID { get; set; }
}

public class GlobalPacksVoiceParam
{
    public long id { get; set; }
    public string CustomerType { get; set; }
    public double NumberOfMinutesOrCalls { get; set; }
    public string HaveMinutesOrCalls { get; set; }
}

}

Many thanks.

rf1234

In case you just need the Mjoin to display data, not to create or edit them you can also emulate it by using a getFormatter. In that getFormatter you just execute an SQL statement and return an associative resultset array. Done. Again: No left join needed.
This is particularly useful if the values you would like to display require joining multiple tables because that won't work with the Mjoin.

Here is a simple example:

Field::inst( 'ctr.id AS ctr_category' )->set( false )   //return same format as an MJoin             
    ->getFormatter( function($val, $data, $opts) {
        return getFormatterCategoryArray($val);
    }),

By aliasing the id field of the respective table I can easily pass in the id field and select the required associative array for the Mjoin values in the getFormatter function.

rf1234

Thanks.- Upadting the data via the MJoin is something I need. Thanks

Then you would need a more complex Mjoin that includes an options instance.

Here is an example for this in PHP. You'll find some in .NET I am sure.
https://editor.datatables.net/examples/advanced/joinArray.html

This allows the Mjoined "Permissions" to be edited etc.

dynasoft

Thanks but GlobalPacksVoiceParam does not hold options just the actual values (of voice parameters) for each row in GlobalSubsPacks so I don't think I need to show options, just the value stored.

dynasoft

Thinking about it, the relationship between GlobalSubsPacks and GlobalSubsPacksRelations in terms of the data stored in GlobalPacksVoiceParam is 1-1 rather than 1-to-many because GlobalSubsPacksRelations and GlobalPacksVoiceParam have a 1-1 relationship. Will proabbly end up using leftjoins (which worked as I found out). Not sure how GlobalPacksVoiceParam will get updated tho.

dynasoft

GlobalSubsPacksRelations holds 1-1 relationships with GlobalPacksVoiceParam but 1-to-any with 3 other tables. How can one combine this? I'm a bit stumped. Ideally, I would need ability to add, edit and delete too. Thanks.

rf1234

Don't know why you can't get the Mjoin running. For the Mjoin it is not important if it is 1:1 or 1:N as long as you have a link table.

I think you need to get the Mjoin running. Don't know anything about .NET - so I can't help you with that.

dynasoft

Yes, thanks. Trying to get the Mjoin working. I got many others but this one is the trickiest of all

dynasoft

Hi rf1234,
Thanks for the pointers. Got it to work in the end. I had to remove the Name property '//.Name("GlobalPacksVoiceParam")' and use fields inside the MJoin and it worked. Thanks again.

rf1234

you are welcome!

dynasoft

Hi
Trying to get it to update. It works displaying but not updating, adding or editing. Here's my code:

• Server:

editor = new Editor(db, "GlobalSubsPacks", "GlobalSubsPacks.id").Model<SubsPacksDBModel.GlobalSubsPacks>("GlobalSubsPacks");
editor.Field(new Field("GlobalSubsPacks.id")
    .Set(false)
);
editor.Field(new Field("GlobalSubsPacks.Name")
    .Xss(false)
    .Validator(Validation.NotEmpty(new ValidationOpts
    {
        Message = lblo.lblEnsureValueIdentified
    }))
);

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.CustomerType")
        .SetFormatter((val, data) => CommonUtilities.IsNullOrEmpty(val) == true ? null : val)
    )
);

editor.Where(q =>
   q.Where("GlobalSubsPacks.id", "(SELECT SubPackID FROM GlobalSubsPacksRelations WHERE SubPackType = 0)", "IN", false)
);

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

• JS:

            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.lblName)*:',
                        name: 'GlobalSubsPacks.Name'
                    }, {
                        label: '@(lblo.lblCustomerType)**:',
                        name: 'GlobalPacksVoiceParam[].CustomerType',
                        type: 'select',
                        options: [
                            @if (Model.CustomerTypesListViewModel != null)
                            {
                                ...
                            }
                            else
                            {
                                ...
                            }
                            @Html.Raw(strTp)
                        ],
                        data: function (row, type, val) {

                            if (row.GlobalPacksVoiceParam.length > 0) {
                                if (row.GlobalPacksVoiceParam[0].CustomerType == null)
                                {
                                    return '';
                                }
                                else
                                {
                                    return row.GlobalPacksVoiceParam[0].CustomerType;
                                }
                            }
                        },
                        //def: '-1'
                        def: 'default'
                    }
                ],
                i18n: {
                    create: {
                        button: '@(lblo.lblAdd)',
                        title: '<img src="/assets/images/Add32.png"> <b>@lblo.lblProductNameLong' + ' - ' + '@(lblo.lblAddNewEntry)</b>',
                        submit: '@(lblo.lblSave)'
                    },
                    edit: {
                        button: '@(lblo.lblModify)',
                        title: '<img src="/assets/images/Modify32.png"> <b>@lblo.lblProductNameLong' + ' - ' + '@(lblo.lblModifyEntry)</b>',
                        submit: '@(lblo.lblSave)'
                    },
                    remove: {
                        button: '@(lblo.lblDelete)',
                        title: '<img src="/assets/images/Delete32.png"> <b>@lblo.lblProductNameLong' + ' - ' + '@(lblo.lblDeleteEntry)</b>',
                        submit: '@(lblo.lblDelete)',
                        confirm: {
                            _: '@(lblo.lblConfirmPermanentAction)',
                            1: '@(lblo.lblConfirmPermanentAction)'
                        }
                    },
                    error: {
                        system: '@(lblo.lblError5)'
                    }
                }
            });

I removed the primary status on field ID in GlobalSubsPacksRelations. What else do I need to look out for? All objects and variables in code in the Model is correct and code for JS is given above. Many thanks.

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

rf1234

What I don't see in your code is an options instance in which you retrieve the options for your field. Take a look at this please: https://editor.datatables.net/manual/net/mjoin#Link-table

DtResponse response = new Editor(WebApiApplication.Db, "users")
    .Model<UserModel>()
    .MJoin(new MJoin("permission")
        .Link("users.id", "user_permission.user_id")
        .Link("permission.id", "user_permission.access_id")
        .Model<AccessModel>()
        .Order("permission.name")
        .Field(new Field("id")
            .Options("permission", "id", "name")
        )
    )
    .Process(formData)
    .Data();

In your code you don't have an options instance but a setFormatter. I mean you need to have a select field or something similar at the front end to select from options and these options need to be retrieved by an options instance. A setFormatter isn't required with an options instance because the options are simple label - value pairs. The label to be displayed to the user at the front end. The value being the key to be inserted / updated. No setFormatting required.

.Field(new Field("GlobalPacksDataParam.CustomerType")
        .SetFormatter((val, data) => CommonUtilities.IsNullOrEmpty(val) == true ? null : val)
    )

You should also find these in the examples. The example that I quoted above (which I think is PHP only).

Here is an example from my own coding: More complex than the rather simple example above. What does it do? It assigns underlying contracts to a derivative contract in order to avoid the derivatives become "naked" which Warren Buffet called "weapons of mass destruction".
The example has lots of rendering in it and the Mjoin has numerous fields for that purpose.

//single derivative contracts need to have underlying contracts assigned to them
//without an assignment of an underlying they may not become approved.
//the array of the underlyings is displayed in the data table
->join(
Mjoin::inst( 'underlying' )
    ->link( 'contract.id', 'derivative_has_underlying.derivative_contract_id' )
    ->link( 'underlying.id', 'derivative_has_underlying.underlying_contract_id' )
    ->order( 'serial, instrument, type asc' )
    ->fields(
        Field::inst( 'id' )->set( false )
            ->options( Options::inst()
                ->table( 'underlying' )
                ->value( 'id' )
                ->label( array('serial', 'instrument', 'type', 'number') )
            //render serial, instrument, type, number
                ->render( function ( $row ) {               
                    return '# '.$row['serial']
                            .' / '.renderInstrument($row['instrument'])
                            .' / '.renderTypeDerivative($row['type'])
                            .' ('.$row['number'].')';
                } )
                ->order( 'serial, instrument, type asc' )
                //where clause MUST be a closure function in Options!!!
                ->where( function($q) {
                    $q ->where( function($r) {
                        $r ->where('govdept_id', $_SESSION['govdept_id'] );
//                                $r ->where('instrument', 'X', '<' );
    //currently we only allow loans to be assigned as underlyings to derivatives
                        $r ->where('instrument', 'W', '<' );
                    });
                } )
            ),
        Field::inst( 'serial' )->set( false ),
        Field::inst( 'instrument' )->set( false ),    
        Field::inst( 'type' )->set( false ),
        Field::inst( 'number' )->set( false )
    )
)

and that is the same field in Javascript. I use selectize but you can also do this with the built-in select field type

}, {
    label: lang === 'de' ? 
            'Wählen Sie einen oder mehrere Verträge über Basisgeschäfte:' : 
            'Select one or more underlying contracts:',
    name:  "underlying[].id", //render serial, instrument, type, number
    type: "selectize", 
    opts: {
        create: false,
        maxItems: null,
        openOnFocus: true,
        allowEmptyOption: true,
        placeholder: lang === 'de' ? 
            'Bitte Grundgeschäft auswählen' : 'Please select underlying'
        }
}, {

allan

If you are using a link table with Mjoin them I’m afraid it is not possible for it to update the child table. It will only update the link table.

If you want to modify the child table, then you’d need to use an approach like in this blog post.

Mjoin should be thought of as changing the links between the tables only - adding and removing the links as required.

Allan

dynasoft

Thanks Allan.

The options are displayed via the razor view (js in code above) and this code and the choices offered aren't taken from a table but from a .net list:

            @if (Model.CustomerTypesListViewModel != null)
            {
                ...
            }
            else
            {
                ...
            }

You're right in that Editor isn't selecting the value stored in GlobalPacksVoiceParam[].CustomerType

Allan, is this enough for the right selection to be made by Editor or do I need to list the options in server code too? If so what is the syntax in .net when working from a .net list rather than a tyable? Many thanks.

dynasoft

from my tests, the code I use for displaying and selecting the CustomerType's is fine.

dynasoft

"You're right in that Editor isn't selecting the value stored in GlobalPacksVoiceParam[].CustomerType"

Not an issue anymore.