Editor in .NET One to Many Join getting error

hafidzfairiz

Hei Allan, nice to know your brilliant plugin..

I'm new in .net
But i have some errors with .net plugin which using MJoin (same problem when i use LeftJoin)..
I have table with foreign keys between dbtester and dbtotestergender with iGenders as the key of dbtotestergender..
As look like this:

table: dbtester
| id | name | iGender |
| 1 | Johnny |1 |
| 2 | Elisa | 2 |

table: dbtotestergender
| iGender | sGender |
| 1 | Man |
| 2 | Woman |

Foreign keys at dbtogender:

But i get this error:

{
    "draw": null,
    "data": [],
    "recordsTotal": null,
    "recordsFiltered": null,
    "error": "Unknown column \u0027dbtotestergender\u0027 in \u0027field list\u0027",
    "fieldErrors": [],
    "id": null,
    "meta": {},
    "options": {},
    "files": {},
    "upload": {
        "id": null
    }
}

Unknown column 'dbtotestergender' in 'field list' ""

This is the controller:

[AcceptVerbs(HttpVerbs.Get | HttpVerbs.Post)]
        public ActionResult GetEmp()
        {
            var settings = Properties.Settings.Default;
            var formData = HttpContext.Request.Form;

            using (var db = new DataTables.Database(settings.DbType, settings.DbConnection))
            {
                var response = new Editor(db, "dbtester", "id")
                    .Model<dbtester>()
                    .Field(new Field("dbtester.id")
                        .Validator(Validation.NotEmpty())
                    )
                    .Field(new Field("dbtester.name")
                        .SetFormatter(Format.IfEmpty(null))
                    )
                    .MJoin(new MJoin("dbtotestergender as gender")
                        .Link("dbtester.iGender", "gender.iGenders")
                        .Model<dbtotestergender>()
                        .Order("gender.iGenders")
                        .Field(new Field("gender.iGenders")
                            .Options("gender", "gender.iGenders", "gender.sGender")
                            )
                        )
                    .Process(formData)
                    .Data();

                return Json(response, JsonRequestBehavior.AllowGet);
                        
            }
        }

Am i wrong in syntax or else? Thanks Allan

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

allan

Could you try it without the as gender alias for the MJoin please? Also, what is in the dbtotestergender model?

Thanks,
Allan

hafidzfairiz

I change my controllers to:

            using (var db = new DataTables.Database(settings.DbType, settings.DbConnection))
            {
                var response = new Editor(db, "dbtester", "id")
                    .Model<dbtester>()
                    .Field(new Field("dbtester.id")
                        .Validator(Validation.NotEmpty())
                    )
                    .Field(new Field("dbtester.name")
                        .SetFormatter(Format.IfEmpty(null))
                    )
                    .MJoin(new MJoin("dbtotestergender")
                        .Link("dbtester.iGender", "dbtotestergender.iGenders")
                        .Model<dbtotestergender>()
                        .Order("dbtotestergender.iGenders")
                        .Field(new Field("dbtotestergender.iGenders")
                            .Options("dbtotestergender", "iGenders", "sGender")
                            )
                        )
                    .Process(formData)
                    .Data();

                return Json(response, JsonRequestBehavior.AllowGet);
                        
            }

This is dbototestergender table:

And this is dbtotestergender Models (Im using ADO.NET with entity framework for mysql, and this is autogenerated models in my Data Access Layer):

 public partial class dbtotestergender
    {
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
        public dbtotestergender()
        {
            this.dbtesters = new HashSet<dbtester>();
        }
    
        public byte iGenders { get; set; }
        public string sGender { get; set; }
    
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
        public virtual ICollection<dbtester> dbtesters { get; set; }
    }

This is my dbtester models:

public partial class dbtester
    {
        public decimal id { get; set; }
        public string name { get; set; }
        public byte iGender { get; set; }
    
        public virtual dbtotestergender dbtotestergender { get; set; }
    }

And still getting same error

hafidzfairiz

I try this:

using (var db = new DataTables.Database(settings.DbType, settings.DbConnection))
            {
                var response = new Editor(db, "dbtester", "id")
                    .Model<dbtester>()
                    .Field(new Field("dbtester.id")
                        .Validator(Validation.NotEmpty())
                    )
                    .Field(new Field("dbtester.name")
                        .SetFormatter(Format.IfEmpty(null))
                    )
                    // Code below is making dropdown in editor right?
                    .Field(new Field("gender.iGenders")
                    .Options("gender", "iGenders", "sGender")
                    )

                    .LeftJoin("dbtotestergender as gender", "gender.iGenders", "=", "dbtester.iGender")
                    //.MJoin(
                    //new MJoin("dbtotestergender")
                    //    .Model<dbtotestergender>()
                    //    .Order("dbtotestergender.iGenders")
                    //    //.Link("dbtotestergender.iGenders", "dbtester.iGender")
                    //    .Field(new Field("dbtotestergender.iGenders")
                    //        .Options("dbtotestergender", "iGenders", "sGender")
                    //        )
                    //)
                    .Process(formData)
                    .Data();

                return Json(response, JsonRequestBehavior.AllowGet);

And still no luck and same errors appeared..

allan

I think public dbtotestergender() in the model is the issue. Editor will use reflection to iterate over all of the properties of the model and tears each of them as a field. Could you try simplify the model so it just matches the database for the dbtotestergender table please? I think that should solve the issue.

Regards,
Allan

hafidzfairiz

I comment public dbtotestergender() like this:

 public partial class dbtotestergender
    {
        //[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
        //public dbtotestergender()
        //{
        //    this.dbtesters = new HashSet<dbtester>();
        //}
    
        public byte iGenders { get; set; }
        public string sGender { get; set; }
    
        //[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
        public virtual ICollection<dbtester> dbtesters { get; set; }
    }

And still no luck, so sorry Allan..

allan

Looking over the data again, it does look like a left join would be more approriate than an MJoin here. I'm assuming that each person can have only one gender?

In which case:

            using (var db = new DataTables.Database(settings.DbType, settings.DbConnection))
            {
                var response = new Editor(db, "dbtester", "id")
                    .Model<dbtester>()
                    .Field(new Field("dbtester.id")
                        .Validator(Validation.NotEmpty())
                    )
                    .Field(new Field("dbtester.name")
                        .SetFormatter(Format.IfEmpty(null))
                    )
                    // Code below is making dropdown in editor right? - AJJ - YES!
                    .Field(new Field("gender.iGenders")
                        .Options("gender", "iGenders", "sGender")
                    )
                    .LeftJoin("dbtotestergender", "dbtotestergender.iGenders", "=", "dbtester.iGender")
                    .Process(formData)
                    .Data();
 
                return Json(response, JsonRequestBehavior.AllowGet);

What does your dbtester model contain?

Allan

hafidzfairiz

I'm assuming that each person can have only one gender?

Yes you're right Allan..

dbtester model (its contained above):

public partial class dbtester
    {
        public decimal id { get; set; }
        public string name { get; set; }
        public byte iGender { get; set; }
     
        public virtual dbtotestergender dbtotestergender { get; set; }
    }

Now, im used LeftJoin again but no help:

[AcceptVerbs(HttpVerbs.Get | HttpVerbs.Post)]
        public ActionResult GetEmp()
        {
            var settings = Properties.Settings.Default;
            var formData = HttpContext.Request.Form;

            using (var db = new DataTables.Database(settings.DbType, settings.DbConnection))
            {
                var response = new Editor(db, "dbtester", "id")
                    .Model<dbtester>()
                    .Field(new Field("dbtester.id")
                        .Validator(Validation.NotEmpty())
                    )
                    .Field(new Field("dbtester.name")
                        .SetFormatter(Format.IfEmpty(null))
                    )
                    .Field(new Field("gender.iGenders")
                            .Options("gender", "iGenders", "sGender")
                            )
                    .LeftJoin("dbtotestergender as gender", "gender.iGenders", "=", "dbtester.iGender")
                    .Process(formData)
                    .Data();

                return Json(response, JsonRequestBehavior.AllowGet);
                        
            }
        }

Thanks for your response until now Allan, hope this prob will clear ASAP..

allan

Thanks - sorry this is taking a while to sort out.

You need to use a nested class as shown here for Editor's .NET libraries when doing a left join. That allows it to tell which field(s) belong to which tables.

Regards,
Allan

hafidzfairiz

Oh Allan, thanks for response and this great answer.. Sorry i didnt read your doc's particularly

I create nested class like this:

    public class JoinModel
    {
        public class dbtester
        {
            public decimal id { get; set; }
            public string name { get; set; }
            public byte iGender { get; set; }
        }

        public class dbtotestergender
        {            
            public string sGender { get; set; }
        }
    }

Change controller like this:

using (var db = new DataTables.Database(settings.DbType, settings.DbConnection))
            {
                var response = new Editor(db, "dbtester", "id")
                    .Model<JoinModel>()
                    .Field(new Field("dbtester.id")
                        .Validator(Validation.NotEmpty())
                    )
                    .Field(new Field("dbtester.name")
                        .SetFormatter(Format.IfEmpty(null))
                    )
                    .Field(new Field("dbtester.iGender")
                            .Options("dbtotestergender", "iGenders", "sGender")
                            )
                    .LeftJoin("dbtotestergender", "dbtotestergender.iGenders", "=", "dbtester.iGender")
                    .Process(formData)
                    .Data();

                return Json(response, JsonRequestBehavior.AllowGet);
                        
            }

And edit javascript in view like this:

 var editor = new $.fn.dataTable.Editor({
            ajax: '/Employee/GetEmp',
            table: '#tblEmployee',
            idSrc: 'dbtester.id',
            fields: [

                {
                    label: "Gender",
                    name: "dbtester.iGender",
                    type: "select"
                }

            ],
        });

        $(document).ready(function() {
            $('#tblEmployee').DataTable({
                "sAjaxSource": '/Employee/GetEmp',
                responsive: true,
                dom: 'Bfrtip',
                select: true,

                "aoColumns": [
                     { "mDataProp": "dbtester.id" },
                     { "mDataProp": "dbtester.name" },

                     { "mDataProp": "dbtotestergender.sGender" },

                ],
                buttons: [
                    { extend: 'create', editor: editor },
                    { extend: 'edit', editor: editor },
                    { extend: 'remove', editor: editor }
                ],
                language: {
                    searchPlaceholder: "Search"
                }
            });
        });

It works Allan.. Thank you very much
And now i will try cascading dropdown with this dependent

allan

Fantastic - great to hear it is working for you now!

Allan