DataTables Editor Issues Setting Up MJoin between two tables

MHCCAIRTeam

I am using data tables editor as part of a C# request system. I have setup the data tables editor following the examples to allow inline editing of the request table. Data tables makes a call from the client side to my Web API. Everything is working great - pretty sweet.

However, I then wanted to join another table and used LeftJoin. After creating the additional model and setting all table identifiers I was able to get the table joined and the data displayed in data tables correctly. I then edit a field and the request is sent off but no update in the DB occurs. I did some more investigating and found left joins only support 1 to 1 relationship and to use MJoin.

So now I am trying to use MJoin but haven't had any luck. Server side looks like this:

//web api controller 
     var request = HttpContext.Current.Request;
     var settings = Properties.Settings.Default;

                    using (var db = new Database(settings.DbType, settings.DbConnection))
                    {
                        var response = new Editor(db, "dbo.Requests", "RequestId")
                            .Model<RequestsModel>()
                            .MJoin(new MJoin("dbo.RequestActions")
                            .Link("Requests.RequestId", "RequestActions.RequestId")
                            .Model<ActionModel>()
                            .Order("Requests.DueDate"))
                            .Process(request)
                            .Data();

                    return Json(response);
                    }

//Model classes
        public class ActionModel
             {
                public string requestActionId { get; set; }
                public string requestId { get; set; }
                public string ownerId { get; set; }
             }

        public class RequestsModel
        {
            public string requestId { get; set; }
            public string statusId { get; set; }
            public string serviceId { get; set; }
            public string priorityId { get; set; }
            public string requesterId { get; set; }
            public string ownerId { get; set; }
        }

I get back a response with the error:

DataTables warning: table id=actionsTable - An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name. An expression of non-boolean type specified in a context where a condition is expected, near 'JOIN'

I tried stepping through code but wasn't able to track down what the raw SQL looked like. If I understood the documentation correctly I am attempting a direct link. The "Requests" table contains a primary key(RequestId) and the "RequestsActions" table has Requests.RequestId as a foreign key - 1 to many. There are no other connections between these two tables. The requests table does have other foreign key constraints (for example, priority id is a primary key in the priorities table).

The end goal is to have actions as editable inside of the data table with some information as to which request the action belongs too or the ability to add a new action to the request via the editor interface.

allan

Hi,

To see the SQL that Editor's C# libraries are generating, add .Debug(true) immediately before the .Process(...) method call. That will cause the returned JSON to include the SQL statements.

Am I right in saying you want to edit the Mjoined table inside this Editor? That's going to be tricky (bordering on not possible at the moment I think) as the Mjoin functionality is primarily geared towards managing the links between tables. Creating, editing and deleting links between the two tables is no problem, but directly editing the Mjoined table is difficult - doubly so if you have other data in the table (e.g. when you create a new row in it, what values should the other columns take)?

Typically I would say to have another page / pane / tab / whatever to manage the second table.

When you post back with the SQL statements, could you also show me your Javascript Editor initialisation please?

Thanks,
Allan

MHCCAIRTeam

Hi Allan,
Thanks for the reply! You are mostly right in saying the I'd like to edit the Mjoined table inside editor. The requests table has a 1 to many relationship with the actions table. Any request can have X number of actions. The data tables editor on the page is meant for editing the actions of a request, adding a new action, etc. So the editor pretty much only deals with the actions table but would have additional information from the requests table. The left join was actually pretty much everything I needed I think except that the data wasn't updated after making a change (request was submitted but if(!set.Any()) return null was executed //if nothing to do, then do nothing).

I don't think editing anything in the requests table is desired. This particular project has been frustrating as the requirements keep changing and common practices are being ignored. I was hoping to get something going quick and dirty rather than write additional functionality on top of the editor or something custom.

I added the .Debug(true) before the .Process(...) and am getting this SQL in the response:

{
    "debug": [{
        "Query": "SELECT  [RequestId] as 'RequestId', [requestId] as 'requestId', [statusId] as 'statusId', [serviceId] as 'serviceId', [priorityId] as 'priorityId', [requesterId] as 'requesterId', [ownerId] as 'ownerId', [parentId] as 'parentId', [createdDate] as 'createdDate', [completedDate] as 'completedDate', [subject] as 'subject', [requestBody] as 'requestBody', [sourceUsed] as 'sourceUsed', [requestedCompletionDate] as 'requestedCompletionDate', [estimatedStartDate] as 'estimatedStartDate', [estimatedCompletionDate] as 'estimatedCompletionDate' FROM  [airservices].[Requests] ",
        "Bindings": []
    }, {
        "Query": "SELECT DISTINCT  [] as 'dteditor_pkey', [airservices].[RequestActions].[requestActionId] as 'requestActionId', [airservices].[RequestActions].[requestId] as 'requestId', [airservices].[RequestActions].[ownerId] as 'ownerId', [airservices].[RequestActions].[description] as 'description', [airservices].[RequestActions].[dueDate] as 'dueDate', [airservices].[RequestActions].[createdDate] as 'createdDate', [airservices].[RequestActions].[hours] as 'hours', [airservices].[RequestActions].[isComplete] as 'isComplete' FROM  [airservices].[Requests]  JOIN [RequestActions] ON [RequestActions].[RequestId]   JOIN [airservices].[RequestActions] ON  =   ORDER BY [Requests].[DueDate] ",
        "Bindings": []
    }],
    "cancelled": []
}

So it looks like column for dteditor_pkey is missing and the join is missing its conditions. I must be going about using the MJoin wrong. I tried making the 'primary' table the Requests table and the RequestsAction table when initializing the Editor but didn't have any luck either. My first post had Requests as the base table with RequestActions as the joined table when actually the editor will be dealing with CRUD on the RequestActions.

    var response = new Editor(db, "dbo.Requests", "RequestId")

The client side code looks something like: 

    editor = new $.fn.dataTable.Editor({
                    ajax: "@Url.RouteUrl("DefaultApi", new { httproute = "", controller = "Actions" })",
                    table: "#actionsTable",
                    fields: [
                    {
                        label: "Description:",
                        name: "RequestActions.Description"
                    },
                    {
                        label: "Due Date:",
                        name: "RequestActions.DueDate",
                        type: 'date',
                        def: function () { return new Date(); },
                        dateFormat: "mm-dd-yy"
                    },
                    {
                        label: "Owner:",
                        name: "RequestActions.OwnerId",
                        type: "select"
                    },
                    {
                        label: "Hours:",
                        name: "RequestActions.Hours"
                    },
                    {
                        label: "Completed",
                        name: "RequestActions.isComplete",
                        type: "checkbox",
                        separator: "",
                        options: [
                            { label: "", value: 1 }
                        ]
                    }
                    ]
    
                });
    
                // Activate an inline edit on click of a table cell
                $('#actionsTable').on('click', 'tbody td:not(:last-child), tbody span.dtr-data', function (e) {
    
                    // Ignore the Responsive control and checkbox columns
                    if ($(this).hasClass('control') || $(this).hasClass('select-checkbox')) {
                        return;
                    }
                    editor.inline(this, {
                        onBlur: 'submit'
                    });
    
                });
    
                $('#actionsTable').DataTable({
    
                    dom: "<'actionsToolbar'>frtip",
                    ajax: "@Url.RouteUrl("DefaultApi", new { httproute = "", controller = "Actions" })",
                    columns: [
                        {   // Responsive control column
                            data: null,
                            defaultContent: '',
                            className: 'control',
                            orderable: false
                        },
                        { data: "RequestActions.Description" },
                        { data: "RequestActions.DueDate" },
                        { data: "RequestActions.OwnerId" },
                        { data: "RequestActions.Hours" },
                        {
                            data: "RequestActions.isComplete",
                            editField: "isComplete",
                            render: function (data, type, row) {
                                if (type === 'display') {
                                    if (data == 1) {
                                        return '<i class="fas fa-check"></i>';
                                    }
                                    else {
                                        return "";
                                    }
                                }
                                return data;
                            }
                        },
                        {
                            data: "RequestActions.RequestId",
                            render: function (data, type, row) {
                                return "<a href='@Url.Action("Details","Home")/"+data+"' target='_blank'>View</a>";
                            }
                        }
                    ],
                    order: [2, 'asc'],
                    select: false,
                    buttons: []
                });

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

allan

Let's step back for a moment, so I can a bit more of an overview if that's okay? I'm not quite clear if you need 1:1 or 1:many. In your first post you said 1:1 (leftJoin) was working well, but not updating. We should be able to make it update the joined table.

So what I'm interested to know is, what is the main source for the rows in the table? To you want one row per entry in the Requests table, or one row per entry in the RequestActions table?

When it is then presented, what do you want to be shown in each column in the table, and which parts should be editable?

If you need to edit the Mjoined information you'd need to do parent / child editing, as Editor does not currently support modifying data in an Mjoined table (at least not in a complex fashion with extra data and without a link table). Left join it does support editing though.

Allan

MHCCAIRTeam

Hey Allen,
Sure, no problem! Sorry for the confusing explanation There would be one row per record in RequestActions displayed in the DataTable. So for example, there is say two requests in the Requests table Request1 & Request2.

The RequestActions table would have:
Request1_Action1
Request1_Action2
Request2_Action1
Request2_Action2
Request3_Action1
etc.

So I think I am confusing some things and/or making things confusing. The Requests table and RequestsActions table has a 1:many relationship in my database.

The Datatable will be showing all records in the RequestActions table but would have some information from the Requests table. Furthermore, if a request doesn't have any RequestActions then nothing would need to be in the datatable for that request. The information contained in the Requests table doesn't need to be edited. The datatables columns would be something like:

[Requests.Subject], [RequestsActions.Description], [RequestActions.isComplete]

Only 'Description' and 'isComplete' would need to be editable. I had set up a left join something like this:

            var response = new Editor(db, "dbo.RequestActions", "RequestActionId")
           .Model<ActionModel>("RequestActions")
           .Model<RequestsModel>("Requests")
           .Field(new Field("RequestActions.RequestActionId")
               .Validator(Validation.NotEmpty())
           )
           .Field(new Field("RequestActions.RequestId"))
           .Field(new Field("RequestActions.OwnerId"))
           .Field(new Field("RequestActions.DueDate").Validator(Validation.DateFormat(
                   Format.DATE_USA,
                   new ValidationOpts { Message = "Please enter a date in the format MM-dd-yyyy" }
               ))
               .GetFormatter(Format.DateSqlToFormat(Format.DATE_USA))
               .SetFormatter(Format.DateFormatToSql(Format.DATE_USA))
           )
           .Field(new Field("RequestActions.Description"))
           .Field(new Field("RequestActions.Hours"))
           .Field(new Field("RequestActions.isComplete"))
           .Field(new Field("Requests.Subject"))
           .LeftJoin("dbo.Requests", "Requests.RequestId", "=", "RequestActions.RequestId")
           .Process(request)
           .Data();

Using the LeftJoin I get the tables connected and the information was available as I expected. The problem I had was I would edit something in the table and no changes were reflected in the database and then the information the editor brings back is the original values. I couldn't find a reason why it wasn't updating but read about some limitations with LeftJoin and assumed it was my problem. Stepping through code it reaches the _InsertOrUpdateTable method of Editor.cs but when it gets to this line it returns null:

        // If nothing to do, then do nothing!
        if (!set.Any())
        {
            return null;
        }

The request sent to my webapi has the values but no update happens and the response back to datatables contains the original values.

allan

Super - thanks! I would suggest going back to the left join code in that case. In fact from your description you don't need to update the joined table at all, just the information in the RequestActions table.

So if you go back to that, could you then show me Javascript you are using for the Editor?

Thanks,
Allan

MHCCAIRTeam

Sure, no problem! Exactly the join was just for some additional information in the DataTable and not meant for editing or the key/value's of a column. So I have something like this for my javascript. Everything seems to be working perfectly like I can get the values from the joined table, events fire, request/response is successful, etc. It's just that any of the fields from the RequestActions table don't update when I change them after adding in the LeftJoin.

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

            ajax: "@Url.RouteUrl("DefaultApi", new { httproute = "", controller = "Actions" })",


            table: "#actionsTable",

            fields: [
                {
                    label: "RequestId",
                    name: "RequestActions.RequestId"
                },
                {

                    label: "Description:",
                    name: "RequestActions.Description"

                },
                {

                    label: "Start Date:",

                    name: "RequestActions.StartDate",
                    type: 'date',
                    def: function () { return new Date(); },
                    dateFormat: "mm-dd-yy"

                },
                {

                    label: "Due Date:",
                    name: "RequestActions.DueDate",
                    type: 'date',
                    def: function () { return new Date(); },
                    dateFormat: "mm-dd-yy"

                },
                {

                    label: "Owner:",
                    name: "RequestActions.OwnerId"

                },
                {

                    label: "Hours:",
                    name: "RequestActions.Hours"

                },
                {
                    label: "Completed",
                    name: "RequestActions.isComplete",
                    type: "checkbox",
                    separator: "",
                    options: [
                        { label: "", value: 1 }
                    ]
                }
            ]

        });


        // Activate an inline edit on click of a table cell
        $('#actionsTable').on('click', 'tbody td:not(:last-child), tbody span.dtr-data', function (e) {

            // Ignore the Responsive control and checkbox columns
            if ($(this).hasClass('control') || $(this).hasClass('select-checkbox')) {
                return;
            }
            editor.inline(this, {
                onBlur: 'submit'
            });

        });

        dTable = $('#actionsTable').DataTable({

            dom: "<'actionsToolbar'>fBrtip",

            ajax: "@Url.RouteUrl("DefaultApi", new { httproute = "", controller = "Actions" })",

            columns: [
                {
                    targets: 0,
                    className: 'control',
                    data: null,
                    defaultContent: ''
                },
                {
                    targets: 1,
                    className: 'select-checkbox',
                    data: null,
                    defaultContent: ''
                },
                { data: "Requests.Subject" },
                { data: "RequestActions.Description" },
                { data: "RequestActions.StartDate" },
                { data: "RequestActions.DueDate" },
                { data: "RequestActions.OwnerId" },
                { data: "RequestActions.Hours" },
                {
                    data: "RequestActions.isComplete",
                    editField: "isComplete",
                    render: function (data, type, row) {
                        if (type === 'display') {
                            if (data == 1) {
                                return '<i class="fas fa-check"></i>';
                            }
                            else {
                                return "";
                            }
                        }
                        return data;
                    }
                },
                {
                    data: "RequestActions.RequestId",
                    render: function (data, type, row) {
                        return "<a href='@Url.Action("Details","Home")/" + data + "' target='_blank'>View</a>";
                    }
                }
            ],
            order: [3, 'asc'],
            select: {
                style: 'multi',
                selector: '.select-checkbox',
                items: 'row',
            },


            buttons: []
        });

allan

Everything there looks like it should work. The on thing that does however stand out to me is the dbo. prefix used for the two table names. Assuming that is still the default for the user you are using, can you try dropping that and see if that helps? I'm wondering if the matching methods Editor is using on the server-side are tripping over that.

Allan

MHCCAIRTeam

Hey Allan,
That looks to be the problem! I'm not sure how to fix it though. I am in fact using a different schema than the default dbo one. I tried setting the default schema for the user and set them to the schema owner for the schema I am using. Still no luck.
I copied the two tables to the default schema dbo and omitted any schema references and then boom everything works perfectly.

So using the following everything seems to be working fine except for update/inserts:

var response = new Editor(db, "customschema.RequestActions", "RequestActionId")
.Model<ActionModel>("RequestActions")
.Model<RequestsModel>("Requests")
.Field(new Field("RequestActions.RequestActionId")
    .Validator(Validation.NotEmpty())
)
.Field(new Field("RequestActions.RequestId"))
.Field(new Field("RequestActions.OwnerId"))
.Field(new Field("RequestActions.DueDate").Validator(Validation.DateFormat(
        Format.DATE_USA,
        new ValidationOpts { Message = "Please enter a date in the format MM-dd-yyyy" }
    ))
    .GetFormatter(Format.DateSqlToFormat(Format.DATE_USA))
    .SetFormatter(Format.DateFormatToSql(Format.DATE_USA))
)
.Field(new Field("RequestActions.Description"))
.Field(new Field("RequestActions.Hours"))
.Field(new Field("RequestActions.isComplete"))
.Field(new Field("Requests.Subject"))
.LeftJoin("customschema.Requests", "Requests.RequestId", "=", "RequestActions.RequestId")
.Process(request)
.Data();

Again, if the two tables belong to the dbo schema and I omit references then update/insert works as well. Is there any way to use a schema other than the default dbo one in sql server? I searched around and found some posts about using a schema other than dbo but didn't have any luck.

https://datatables.net/forums/discussion/36844/support-for-non-dbo-schemas
https://datatables.net//forums/discussion/comment/83867/#Comment_83867)
https://datatables.net/forums/discussion/36905

MHCCAIRTeam

With no left join and a custom schema updates work but the insert fails unless I hardcode the tablename in DataBaseUtil/Sqlserver/Query.cs _Prepare method

allan

Non-default schema's for the SQL Server integration in Editor is certainly a weak point at the moment I'm afraid. The plan is to introduce a new Schema() method at some point which will resolve these issues - hopefully that will be part of 1.8.

Until then, try adding the required schema name to all fields and references - i.e. if it specifies the tableName.fieldName it should become schemaName.tableName.fieldName. That will allow the string based matching to find the links. The Javascript would also need to be updated for this nesting.

Allan

MHCCAIRTeam

Thanks Allan! Adding the schema to all fields and references fixed the update issue I was missing the schema in the left join which was causing the problem. I wasn't able to insert still unless I hardcode the table name in the DataBaseUtil/Sqlserver/Query.cs _Prepare method. So I replaced in Query.cs
param.Value = _table[0] with:

string[] splitTableNameFromSchema = _table[0].Split('.');
param.Value = splitTableNameFromSchema[splitTableNameFromSchema.Count() - 1];

and everything seems to be working fine now. Thanks for all the help!! Datatables and Editor are both amazing. Beautiful code and easy implementation. Looking forward to any future releases!