Editor: MJoin Error: Join was performed on the field x which was not included...

kmbonin

Hello,

I'm using the datatables editor with .Net. I'm doing an MJoin on three tables, Jobs, ProdData, and Dept. So a job (table 1) has a jobID that is a FK to ProdData, which has a DeptID, linking it to the Dept table. Jobs to ProdData is a one to many, and ProdData to Dept is a one to one. When I execute my code, I get the following error: "DataTables warning: table id=ProductionData - Join was performed on the field 'logan_jobs.jobID' which was not included in the Editor field list. The join field must be included as a regular field in the Editor instance." The issue is, I have placed logan_jobs.jobID in the Editor field list already, but it's still throwing the error. Any assistance would be much appreciated.

Controller:

public IHttpActionResult ProductionData()
        {
            var request = HttpContext.Current.Request;
            var settings = Properties.Settings.Default;

            using (var db = new Database(settings.DbType, settings.DbConnection))
            {                
                var response = new Editor(db, "logan_jobs", "jobId")
                    .Model<ProductionDataModel>()
                    .Field(new Field("logan_jobs.jobID")
                        .Set(false))
                    .Field(new Field("logan_jobs.jobNumber"))
                    .Field(new Field("logan_jobs.customer"))
                    .Field(new Field("logan_jobs.plannerID")
                        .Options("logan_user", "userID", "fullName", q => q.Where("deptID", "1", "="))
                        .Validator(Validation.DbValues()))
                    .Field(new Field("logan_jobs.description"))
                    .Field(new Field("logan_jobs.partNumber"))
                    .Field(new Field("logan_jobs.salesOrder"))
                    .Field(new Field("logan_jobs.OrderDate"))
                    .Field(new Field("logan_jobs.QuoteNumber"))
                    .Field(new Field("logan_jobs.ShipByDate"))
                    .Field(new Field("logan_jobs.NeedByDate"))
                    .Field(new Field("logan_jobs.CloseDate"))
                    .Field(new Field("logan_user.fullName"))                    
                    .MJoin(
                        new MJoin("logan_proddata") 
                        .Link("logan_jobs.jobID", "logan_prodData.jobID")
                        .Model<ProductionDEPTDataModel>()
                        .Order("displayOrder, department")                        
                        .Field(new Field("logan_deptData.department"))
                        )
                    .LeftJoin("logan_user", "logan_user.userID", "=", "logan_jobs.plannerID")
                    .Process(request)
                    .Data();

                return Json(response);
            }
        }

Models(2):

public class ProductionDataModel : EditorModel
    {
        public class logan_jobs : EditorModel
        {
            public int jobID { get; set; }
            public string jobNumber { get; set; }
            public string customer { get; set; }
            public string description { get; set; }
            public string partNumber { get; set; }
            public int plannerid { get; set; }
            public string salesOrder { get; set; }
            public string OrderDate { get; set; }
            public string QuoteNumber { get; set; }
            public string ShipByDate { get; set; }
            public string NeedByDate { get; set; }
            public string CloseDate { get; set; }
        }

        public class logan_user : EditorModel
        {
            public string fullname { get; set; }
            public int userid { get; set; }
        }       
    }
----
public class ProductionDEPTDataModel : EditorModel
    {
        public class logan_deptData : EditorModel
        {
            public int deptID { get; set; }
            public string department { get; set; }
            public int displayOrder { get; set; }
        }       
    }

Javascript:

function format(d) {
    // `d` is the original data object for the row
    return '<table cellpadding="15" cellspacing="0" border="0" style="padding-left:50px; width:75%;">' +
       
        '<tr>' +
            '<td><a href="jobs_details.aspx?jobNumber=' + d.logan_jobs.jobNumber + '"><img src="img/icons/white-zoom-icon.jpg" title="View Details" target="_blank" width="20px"/></a></td>' +
            '<td style="font-weight:bold">Part Number:</td>' +
            '<td>' + d.logan_jobs.partNumber + '</td>' +
            '<td style="font-weight:bold">Ship By:</td>' +
            '<td>' + d.logan_jobs.shipByDate + '</td>' +
        '</tr>' +
        '<tr>' +
            '<td></td>' +
            '<td style="font-weight:bold">Sales Order:</td>' +
            '<td>' + d.logan_jobs.salesOrder + '</td>' +
            '<td style="font-weight:bold">Quote Number:</td>' +
            '<td>' + d.logan_jobs.quoteNumber + '</td>' +
        '</tr>' +
    '</table>';
}

    
(function($){
$(document).ready(function() {
     editor = new $.fn.dataTable.Editor( {
        ajax: './api/ProductionData',
        table: '#ProductionData',
        fixedHeader: true,
        fields: [
             {
                 "label": "JobID:",
                 "name": "logan_jobs.jobID",
                 "type": "readonly"
             },
            {
                "label": "Job:",
                "name": "logan_jobs.jobNumber",
                "type": "readonly"
            },
            {
                "label": "Customer:",
                "name": "logan_jobs.customer",
                "type": "readonly"
            },
            {
                "label": "Planner:",
                "name": "logan_jobs.plannerID",
                "type": "select"
            },
            {
                "label": "Description:",
                "name": "logan_jobs.description",
                "type": "textarea"
            },
            {
                "label": "PM Date:",
                "name": "logan_prodData[].PMEnd"
            }
        ]
    });


    var table = $('#ProductionData').DataTable( {
        ajax: './api/ProductionData',
        order: [ 1, 'asc' ],
        fixedHeader: true,
        columns: [
            {
                "class": "details-control",
                "orderable": false,               
                "data": null,
                "defaultContent": "<img src='img/icons/blue-plus-icon.png' title='View Details' width='16px' />"
            },
            {
                "data": "logan_jobs.jobID"
            },
            {
                "data": "logan_jobs.jobNumber"
            },
            {
                "data": "logan_jobs.customer"
            },
            {
                "data": "logan_user.fullName"
            },
            {
                "data": "logan_jobs.description"
            } 
        ],
        select: true,
        lengthChange: false
    } );

    new $.fn.dataTable.Buttons(table, [
        { extend: "edit", 
          editor: editor,
            formButtons: [
                'Save',
                { label: 'Cancel', fn: function () { this.close(); } }
            ]
        },
        {
            extend: "remove",
            editor: editor,
            formMessage: function (e, dt) {
                return 'Are you sure you want to delete this record?';
            },
            formButtons: [
                'Delete',
                { label: 'Cancel', fn: function () { this.close(); } }
            ]
        },
        {
            extend: 'collection',
            text: 'Export',
            buttons: [
                'copy',
                'excel',
                'csv',
                'pdf',
                'print'
            ]
        }
    ] );

    table.buttons().container()
        .prependTo($('div.fg-toolbar:eq(0)', table.table().container()));

    // Add event listener for opening and closing details
    $('#ProductionData tbody').on('click', 'td.details-control', function () {
        var tr = $(this).closest('tr');
        var row = table.row(tr);

        if (row.child.isShown()) {
            // This row is already open - close it
            row.child.hide();
            tr.removeClass('shown');
        }
        else {
            // Open this row
            row.child(format(row.data())).show();
            tr.addClass('shown');
        }
    });
} );

}(jQuery));

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

kmbonin

My apologies for the ugliness of this post.

allan

Could you remove:

                    .Field(new Field("logan_jobs.jobID")
                        .Set(false))

And also the jobID from the logan_jobs class please? Editor will include them automatically and I think that might be confusing it.

Thanks,
Allan

kmbonin

That resolved the existing error, but now I get a new error: "Not unique table/alias: 'logan_proddata'"

Controller is:

     public IHttpActionResult ProductionData()
          {
              var request = HttpContext.Current.Request;
              var settings = Properties.Settings.Default;

            using (var db = new Database(settings.DbType, settings.DbConnection))
            {                
                var response = new Editor(db, "logan_jobs", "jobID")
                    .Model<ProductionDataModel>()                   
                    .Field(new Field("logan_jobs.jobNumber"))
                    .Field(new Field("logan_jobs.customer"))
                    .Field(new Field("logan_jobs.plannerID")
                        .Options("logan_user", "userID", "fullName", q => q.Where("deptID", "1", "="))
                        .Validator(Validation.DbValues()))
                    .Field(new Field("logan_jobs.description"))
                    .Field(new Field("logan_jobs.partNumber"))
                    .Field(new Field("logan_jobs.salesOrder"))
                    .Field(new Field("logan_jobs.OrderDate"))
                    .Field(new Field("logan_jobs.QuoteNumber"))
                    .Field(new Field("logan_jobs.ShipByDate"))
                    .Field(new Field("logan_jobs.NeedByDate"))
                    .Field(new Field("logan_jobs.CloseDate"))
                    .Field(new Field("logan_user.fullName"))                    
                    .MJoin(
                        new MJoin("logan_proddata") 
                        .Link("logan_jobs.jobID", "logan_prodData.jobID")
                        .Model<ProductionDEPTDataModel>()
                        .Order("displayOrder, department")                        
                        .Field(new Field("logan_deptData.department"))
                        )
                    .LeftJoin("logan_user", "logan_user.userID", "=", "logan_jobs.plannerID")
                    .Process(request)
                    .Data();

                return Json(response);
            }
        }

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

allan

That's odd! Can you show me the code for your two models please?

Thanks,
Allan

kmbonin

"Not unique table/alias: 'logan_proddata'"

Controller:

using (var db = new Database(settings.DbType, settings.DbConnection))
            {                
                var response = new Editor(db, "logan_jobs", "jobID")
                    .Model<ProductionDataModel>()                   
                    .Field(new Field("logan_jobs.jobNumber"))
                    .Field(new Field("logan_jobs.customer"))
                    .Field(new Field("logan_jobs.plannerID")
                        .Options("logan_user", "userID", "fullName", q => q.Where("deptID", "1", "="))
                        .Validator(Validation.DbValues()))
                    .Field(new Field("logan_jobs.description"))
                    .Field(new Field("logan_jobs.partNumber"))
                    .Field(new Field("logan_jobs.salesOrder"))
                    .Field(new Field("logan_jobs.OrderDate"))
                    .Field(new Field("logan_jobs.QuoteNumber"))
                    .Field(new Field("logan_jobs.ShipByDate"))
                    .Field(new Field("logan_jobs.NeedByDate"))
                    .Field(new Field("logan_jobs.CloseDate"))
                    .Field(new Field("logan_user.fullName"))                    
                    .MJoin(
                        new MJoin("logan_proddata") 
                        .Link("logan_jobs.jobID", "logan_prodData.jobID")
                        .Field(new Field("logan_prodData.PComp"))
                        .Model<ProductionDEPTDataModel>()
                        .Order("displayOrder, department")                        
                        .Field(new Field("logan_deptData.department"))
                        )
                    .LeftJoin("logan_user", "logan_user.userID", "=", "logan_jobs.plannerID")
                    .Process(request)
                    .Data();

                return Json(response);
            }
        }

Models:

public class ProductionDataModel : EditorModel
    {
        public class logan_jobs : EditorModel
        {
            public string jobNumber { get; set; }
            public string customer { get; set; }
            public string description { get; set; }
            public string partNumber { get; set; }
            public int plannerid { get; set; }
            public string salesOrder { get; set; }
            public string OrderDate { get; set; }
            public string QuoteNumber { get; set; }
            public string ShipByDate { get; set; }
            public string NeedByDate { get; set; }
            public string CloseDate { get; set; }
        }

        public class logan_user : EditorModel
        {
            public string fullname { get; set; }
            public int userid { get; set; }
        }
       
    }

 public class ProductionDEPTDataModel : EditorModel
    {
        public class logan_deptData : EditorModel
        {
            public int deptID { get; set; }
            public string department { get; set; }
            public int displayOrder { get; set; }
        }       
    }

allan

Could you try changing the code a little:

1) Remove the table prefix from the MJoin elements:

using (var db = new Database(settings.DbType, settings.DbConnection))
            {               
                var response = new Editor(db, "logan_jobs", "jobID")
                    .Model<ProductionDataModel>()                  
                    .Field(new Field("logan_jobs.jobNumber"))
                    .Field(new Field("logan_jobs.customer"))
                    .Field(new Field("logan_jobs.plannerID")
                        .Options("logan_user", "userID", "fullName", q => q.Where("deptID", "1", "="))
                        .Validator(Validation.DbValues()))
                    .Field(new Field("logan_jobs.description"))
                    .Field(new Field("logan_jobs.partNumber"))
                    .Field(new Field("logan_jobs.salesOrder"))
                    .Field(new Field("logan_jobs.OrderDate"))
                    .Field(new Field("logan_jobs.QuoteNumber"))
                    .Field(new Field("logan_jobs.ShipByDate"))
                    .Field(new Field("logan_jobs.NeedByDate"))
                    .Field(new Field("logan_jobs.CloseDate"))
                    .Field(new Field("logan_user.fullName"))                   
                    .MJoin(
                        new MJoin("logan_proddata")
                        .Link("logan_jobs.jobID", "logan_prodData.jobID")
                        .Field(new Field("PComp"))
                        .Field(new Field("department"))
                        .Model<ProductionDEPTDataModel>()
                        .Order("displayOrder, department")                       
                        )
                    .LeftJoin("logan_user", "logan_user.userID", "=", "logan_jobs.plannerID")
                    .Process(request)
                    .Data();
 
                return Json(response);
            }
        }

2) Remove the nested class from the MJoin model. Although nested classes are used for the left join, they (possibly confusingly) aren't for MJoin:

public class ProductionDataModel : EditorModel
    {
        public class logan_jobs : EditorModel
        {
            public string jobNumber { get; set; }
            public string customer { get; set; }
            public string description { get; set; }
            public string partNumber { get; set; }
            public int plannerid { get; set; }
            public string salesOrder { get; set; }
            public string OrderDate { get; set; }
            public string QuoteNumber { get; set; }
            public string ShipByDate { get; set; }
            public string NeedByDate { get; set; }
            public string CloseDate { get; set; }
        }
 
        public class logan_user : EditorModel
        {
            public string fullname { get; set; }
            public int userid { get; set; }
        }
        
    }
 
 public class ProductionDEPTDataModel : EditorModel
    {
            public int deptID { get; set; }
            public string department { get; set; }
            public int displayOrder { get; set; }
    }

Thanks,
Allan

kmbonin

Okay, made those changes. Now I am back to the original error: Join was performed on the field 'logan_jobs.jobID' which was not included in the Editor field list. The join field must be included as a regular field in the Editor instance.

Any ideas?

allan

I'm afraid I think there is a bug in Editor here. When it is checking to see if the joined field is the primary key, it should use the table name as well.

Could you try changing:

var response = new Editor(db, "logan_jobs", "jobID")

To be:

var response = new Editor(db, "logan_jobs", "logan_jobs.jobID")

That should workaround it until it is fixed properly.

Thanks,
Allan

kmbonin

Still getting the same error. Here's my whole response variable, as it stands now:

var response = new Editor(db, "logan_jobs", "logan_jobs.jobId")
                    .Model<ProductionDataModel>()
                    .Field(new Field("logan_jobs.jobNumber"))
                    .Field(new Field("logan_jobs.customer"))
                    .Field(new Field("logan_jobs.plannerID")
                        .Options("logan_user", "userID", "fullName", q => q.Where("deptID", "1", "="))
                        .Validator(Validation.DbValues()))
                    .Field(new Field("logan_jobs.description"))
                    .Field(new Field("logan_jobs.partNumber"))
                    .Field(new Field("logan_jobs.salesOrder"))
                    .Field(new Field("logan_jobs.OrderDate"))
                    .Field(new Field("logan_jobs.QuoteNumber"))
                    .Field(new Field("logan_jobs.ShipByDate"))
                    .Field(new Field("logan_jobs.NeedByDate"))
                    .Field(new Field("logan_jobs.CloseDate"))
                    .Field(new Field("logan_user.fullName"))
                    .MJoin(
                        new MJoin("logan_proddata")
                        .Link("logan_jobs.jobID", "logan_prodData.jobID")
                        .Field(new Field("PComp"))
                        .Field(new Field("department"))
                        .Model<ProductionDEPTDataModel>()
                        .Order("displayOrder, department")
                        )
                    .LeftJoin("logan_user", "logan_user.userID", "=", "logan_jobs.plannerID")
                    .Process(request)
                    .Data();

kmbonin

Is there any other work arounds you can think of to get this going? If not, when will the bug fix be released?

allan

Can I send an e-mail with an updated dll to the e-mail address you used to register your account? The latest changes should address what you are seeing.

Regards,
Allan

kmbonin

Yes, please do. Thank you so much.

kmbonin

Okay, new problem (maybe??). I have changed the controller quite a bit, but am still doing the MJoin.

I have logan_prodData as the main table here, doing an MJoin to logan_jobComments (one production data record can have multiple comments). The link between the tables is the JobID.

The error I now get, using the new dll is:
DataTables warning: table id=ProductionData - Column 'jobID' in field list is ambiguous

Controller Code:

using (var db = new Database(settings.DbType, settings.DbConnection))
            {                
                var response = new Editor(db, "logan_prodData", "recordID")
                    .Model<DepartmentDataModel>()
                    .Field(new Field("logan_prodData.jobID"))
                    .Field(new Field("logan_prodData.PComp")
                        .Validator(Validation.Numeric()))
                    .Field(new Field("logan_prodData.CurEstEndDate")
                        .GetFormatter(Format.DateSqlToFormat("MM/d/yyyy"))
                        .SetFormatter(Format.DateFormatToSql("MM/d/yyyy")))
                    .Field(new Field("logan_prodData.PMEnd")
                        .GetFormatter(Format.DateSqlToFormat("MM/d/yyyy"))
                        .SetFormatter(Format.DateFormatToSql("MM/d/yyyy")))
                    .Field(new Field("logan_prodData.deptID")
                        .Options("logan_dept", "deptID", "department")
                        .Validator(Validation.DbValues()))
                    .Field(new Field("logan_jobs.jobNumber"))
                    .Field(new Field("logan_jobs.customer"))
                    .Field(new Field("logan_jobs.plannerID")
                        .Options("logan_user", "userID", "fullName", q => q.Where("deptID", "1", "="))
                        .Validator(Validation.DbValues()))
                    .Field(new Field("logan_jobs.description"))
                    .Field(new Field("logan_jobs.partNumber"))
                    .Field(new Field("logan_user.fullName"))
                    .Field(new Field("logan_status.status"))
                    .Field(new Field("logan_status.displayColor"))
                    .Field(new Field("logan_dept.department"))
                    .MJoin(
                        new MJoin("logan_jobComments")
                        .Link("logan_prodData.jobID", "logan_jobComments.jobID")
                        .Field(new Field("logan_jobComments.comment"))
                        .Order("logan_jobComments.AddedDate desc")                        
                        )
                    .LeftJoin("logan_jobs", "logan_jobs.jobId", "=", "logan_prodData.jobId")
                    .LeftJoin("logan_dept", "logan_dept.deptID", "=", "logan_prodData.deptID")
                    .LeftJoin("logan_status", "logan_status.statusID", "=", "logan_prodData.statusID")
                    .LeftJoin("logan_user", "logan_user.userID", "=", "logan_jobs.plannerID")
                    .Process(request)
                    .Data();

                return Json(response);
            }

allan

Are you using the dll I sent by e-mail? If so, can you add .Debug( true ) in between lines 3 and 4 in the above code (i.e. immediately after the constructor) and then show me the JSON that the server is returning please?

Thanks,
Allan

kmbonin

Added, this is what I get from the API:

{"draw":null,"data":[],"recordsTotal":null,"recordsFiltered":null,"error":
"Column 'jobID' in field list is ambiguous",
"fieldErrors":[],"id":null,"meta":{},"options":{},"files":{},
"upload":{"id":null},"debugSql":null}

kmbonin

This is the model I am using, and the ambiguous error is on the last line. If I remove it or rename it to an alias, I get the original error that it was not included in the field list.

public class DepartmentDataModel : EditorModel
    {
        public int PComp { get; set; }
        public string CurEstEndDate { get; set; }
        public string PMEnd { get; set; }
        public int jobID { get; set; }    
    }  

allan

Sorry this is dragging on somewhat! I'll update the dll shortly and ping it through to you again - it looks like it isn't collecting the SQL if an error occurs, which isn't very useful!

Allan

kmbonin

Okay, thanks. Appreciate the assistance!

kmbonin

Alright, here it is...looks like jobID is being included in the select list (last column) but it does not have a table qualifier on it, thereby making it ambiguous. Note that it's not a field column I am adding, as my addition of jobID is the second column, logan_prodData.jobID.

{"draw":null,"data":[],"recordsTotal":null,"recordsFiltered":null,"error":"Ambiguous column name 'jobID'.","fieldErrors":[],"id":null,"meta":{},"options":{},"files":{},"upload":{"id":null},"debugSql":[{"Query":"SELECT  [logan_prodData].[recordID] as 'logan_prodData.recordID', [logan_prodData].[jobID] as 'logan_prodData.jobID', [logan_prodData].[PComp] as 'logan_prodData.PComp', [logan_prodData].[CurEstEndDate] as 'logan_prodData.CurEstEndDate', [logan_prodData].[PMEnd] as 'logan_prodData.PMEnd', [logan_prodData].[deptID] as 'logan_prodData.deptID', [logan_jobs].[jobNumber] as 'logan_jobs.jobNumber', [logan_jobs].[customer] as 'logan_jobs.customer', [logan_jobs].[plannerID] as 'logan_jobs.plannerID', [logan_jobs].[description] as 'logan_jobs.description', [logan_jobs].[partNumber] as 'logan_jobs.partNumber', [logan_user].[fullName] as 'logan_user.fullName', [logan_status].[status] as 'logan_status.status', [logan_status].[displayColor] as 'logan_status.displayColor', [logan_dept].[department] as 'logan_dept.department', [PComp] as 'PComp', [CurEstEndDate] as 'CurEstEndDate', [PMEnd] as 'PMEnd', [jobID] as 'jobID' FROM  logan_prodData LEFT JOIN [logan_jobs] ON [logan_jobs].[jobId] = [logan_prodData].[jobId]  LEFT JOIN [logan_dept] ON [logan_dept].[deptID] = [logan_prodData].[deptID]  LEFT JOIN [logan_status] ON [logan_status].[statusID] = [logan_prodData].[statusID]  LEFT JOIN [logan_user] ON [logan_user].[userID] = [logan_jobs].[plannerID]  LEFT JOIN [logan_jobComments] ON [logan_jobComments].[jobID] = [logan_prodData].[jobId] ","Bindings":[]}]}

allan

Okay - got it. Thanks!

Try this for your DepartmentDataModel:

public class DepartmentDataModel : EditorModel
    {
        public class logan_prodData : EditorModel {
            public int PComp { get; set; }
            public string CurEstEndDate { get; set; }
            public string PMEnd { get; set; }
            public int jobID { get; set; }   
        }
    } 

The problem is that without that inner class, Editor doesn't "know" that the fields are really join fields - so it is adding them as defined in the model.

Allan

kmbonin

Okay, that fixed that particular problem, now I get:

DataTables warning: table id=ProductionData - Join was performed on the field 'logan_prodData.jobID' which was not included in the Editor field list. The join field must be included as a regular field in the Editor instance.

But the field is in the list.

Here's the debug:

{"draw":null,"data":[],"recordsTotal":null,"recordsFiltered":null,"error":"Join was performed on the field 'logan_prodData.jobID' which was not included in the Editor field list. The join field must be included as a regular field in the Editor instance.","fieldErrors":[],"id":null,"meta":{},"options":{},"files":{},"upload":{"id":null},"debugSql":[{"Query":"SELECT  [logan_prodData].[recordID] as 'logan_prodData.recordID', [logan_prodData].[jobID] as 'logan_prodData.jobID', [logan_prodData].[PComp] as 'logan_prodData.PComp', [logan_prodData].[CurEstEndDate] as 'logan_prodData.CurEstEndDate', [logan_prodData].[PMEnd] as 'logan_prodData.PMEnd', [logan_prodData].[deptID] as 'logan_prodData.deptID', [logan_jobs].[jobNumber] as 'logan_jobs.jobNumber', [logan_jobs].[customer] as 'logan_jobs.customer', [logan_jobs].[plannerID] as 'logan_jobs.plannerID', [logan_jobs].[description] as 'logan_jobs.description', [logan_jobs].[partNumber] as 'logan_jobs.partNumber', [logan_user].[fullName] as 'logan_user.fullName', [logan_status].[status] as 'logan_status.status', [logan_status].[displayColor] as 'logan_status.displayColor', [logan_dept].[department] as 'logan_dept.department' FROM  logan_prodData LEFT JOIN [logan_jobs] ON [logan_jobs].[jobId] = [logan_prodData].[jobId]  LEFT JOIN [logan_dept] ON [logan_dept].[deptID] = [logan_prodData].[deptID]  LEFT JOIN [logan_status] ON [logan_status].[statusID] = [logan_prodData].[statusID]  LEFT JOIN [logan_user] ON [logan_user].[userID] = [logan_jobs].[plannerID]  LEFT JOIN [logan_jobComments] ON [logan_jobComments].[jobID] = [logan_prodData].[jobId] ","Bindings":[]},{"Query":"SELECT DISTINCT  [department] as 'department', [deptID] as 'deptID' FROM  logan_dept ","Bindings":[]},{"Query":"SELECT DISTINCT  [fullName] as 'fullName', [userID] as 'userID' FROM  logan_user WHERE ([deptID] = @where_1 )","Bindings":[{"Name":"@where_1","Value":"1","Type":null}]}]}

allan

I think its because of the case difference:

new MJoin("logan_proddata")

v

.Link("...", "logan_prodData.jobID")

A direct string compare (which Editor does) is going to fail because `"logan_proddata" != "logan_prodData"

If you make it consistent - logan_prodData for example - I think it should work.

Allan

kmbonin

So the MJoin is on logan_jobComments (see the post from Oct 11):

.MJoin(
                        new MJoin("logan_jobComments")
                        .Link("logan_prodData.jobID", "logan_jobComments.jobID")
                        .Field(new Field("logan_jobComments.comment"))
                        .Order("logan_jobComments.AddedDate desc")                       
                        )

Also verified that all cases of logan_prodData were the same, just to be sure that wasn't it. And from the same post on Oct 11, logan_prodData.jobID is the first field that is listed, so I am not sure why it would be saying it's not in the editor list.

The whole controller request variable:

var response = new Editor(db, "logan_prodData", "recordID")
                    .Debug(true)
                    .Model<DepartmentDataModel>()                    
                    .Field(new Field("logan_prodData.jobID"))
                    .Field(new Field("logan_prodData.PComp")
                        .Validator(Validation.Numeric()))
                    .Field(new Field("logan_prodData.CurEstEndDate")
                        .GetFormatter(Format.DateSqlToFormat("MM/d/yyyy"))
                        .SetFormatter(Format.DateFormatToSql("MM/d/yyyy")))
                    .Field(new Field("logan_prodData.PMEnd")
                        .GetFormatter(Format.DateSqlToFormat("MM/d/yyyy"))
                        .SetFormatter(Format.DateFormatToSql("MM/d/yyyy")))
                    .Field(new Field("logan_prodData.deptID")
                        .Options("logan_dept", "deptID", "department")
                        .Validator(Validation.DbValues()))
                    .Field(new Field("logan_jobs.jobNumber"))
                    .Field(new Field("logan_jobs.customer"))
                    .Field(new Field("logan_jobs.plannerID")
                        .Options("logan_user", "userID", "fullName", q => q.Where("deptID", "1", "="))
                        .Validator(Validation.DbValues()))
                    .Field(new Field("logan_jobs.description"))
                    .Field(new Field("logan_jobs.partNumber"))
                    .Field(new Field("logan_user.fullName"))
                    .Field(new Field("logan_status.status"))
                    .Field(new Field("logan_status.displayColor"))
                    .Field(new Field("logan_dept.department"))
                    
                    .MJoin(
                        new MJoin("logan_jobComments")
                        .Link("logan_prodData.jobID", "logan_jobComments.jobID")
                        .Field(new Field("logan_jobComments.comment"))
                        .Order("logan_jobComments.AddedDate desc")                        
                        )
                    
                    .LeftJoin("logan_jobs", "logan_jobs.jobID", "=", "logan_prodData.jobID")
                    .LeftJoin("logan_dept", "logan_dept.deptID", "=", "logan_prodData.deptID")
                    .LeftJoin("logan_status", "logan_status.statusID", "=", "logan_prodData.statusID")
                    .LeftJoin("logan_user", "logan_user.userID", "=", "logan_jobs.plannerID")
                    .Process(request)
                    .Data();

                return Json(response);

allan

Sorry - I got that from the Oct 7th post.

I think this is a bug in the Editor library that is being triggered by the fact that .Field(new Field("logan_prodData.jobID")) has the table name on it as well as the column name. If you were to use .Field(new Field("jobID")) I suspect it would work.

Let me dig into this a bit more. I'll create a local example and try to replicate the issue.

Regards,
Allan

kmbonin

No, it apparently has to have the table name in the field. New error when I remove the table name from the field definition:
Table part of the field 'jobID' was not found. In Editor instance that use a join, all the fields must have the database table set explicity.

No debugSQL was generated this time.

allan

Okay - thanks for letting me know. Let me look into it, while I'll do as quickly as possible, and get back to you. I can see the issue in the code, I'm just not immediately certain what the best way to fix it is.

Allan

kmbonin

So I worked around the insert by creating a field in my main table, logan_prodData for a single comment, then created a trigger on that table in the database to move the comment from the main table to the "real" table that it's needed in. The only downside is that the users still cannot view the comments in the datatable, they have to view them on a custom .net C# page.

Eisenkiefer

Hi,

just ran into the same problem.
Is there a solution yet?

Eisenkiefer

allan

I've not had a chance to work out what is going on here yet I'm afraid (plan to soon though!). I think that it is an error in the libraries where the joining field is not the primary key. Would that match your case Eisenkiefer? Could you post your code so I can take a look for similarities?

Thanks,
Allan

Eisenkiefer

Yeah that would match my case, the joined is performed on a foreign key in the main table.
Code looks pretty much similiar to the above, i will post the exact code later.

Thanks for your support
Eisenkiefer