Error whilst using a database table alias with the EDITOR in an MVC environment.

plangan

I am using the DataTable Editor in an MVC environment (C#, Visual Studio). I have hit a wall whilst trying to use table aliases.

I have a table 'tbl_persondetails' which contain two integer columns 'cv_received_id' and 'gcp_received_id' which have a left join to another table 'tbl_receivedstatus' which contain two columns 'id','descr'. When my model/controller/view references just one of the fields it works fine and the dropdown list as expected. However if I reference both the fields I get a duplicate table error. I cannot have two joins to the same table.

Hence I have to use table aliases. When I use a table aliases (in the code below the alias name is cvstatus) I get an error 'cannot find tbl_receivedstatus.descr' error. (I have removed reference to the second enumerated field 'gcp_recieved_id').

Controller code is:

public class IndividualController : ApiController
{
    [System.Web.Http.Route("api/Individual")]
    [System.Web.Http.HttpGet]
    [System.Web.Http.HttpPost]
    public IHttpActionResult Individual()
    {
        var request = HttpContext.Current.Request;
        var settings = Properties.Settings.Default;

        using (var db = new Database(settings.DbType, settings.DbConnection))
        {
            var response = new Editor(db, "tbl_persondetails")
                .Model<IndividualModel>()
                .Field(new Field("tbl_persondetails.id")
                    .Validator(Validation.Numeric())
                    .SetFormatter(Format.IfEmpty(null))
                )
                .Field(new Field("tbl_persondetails.title_id")
                    .Options(new Options()
                        .Table("tbl_title")
                        .Value("id")
                        .Label("descr")
                    )
                    .Options("tbl_title", "id", "descr")
                )
                .Field(new Field("tbl_persondetails.given_name")
                    .Validator(Validation.NotEmpty())
                )
                .Field(new Field("tbl_persondetails.middle_name"))
                .Field(new Field("tbl_persondetails.family_name"))
                .Field(new Field("tbl_persondetails.preferred_name"))
                .Field(new Field("tbl_persondetails.previous_name"))
                .Field(new Field("tbl_persondetails.tel_no"))
                .Field(new Field("tbl_persondetails.cv_received_id")
                    .Options(new Options()
                        .Table("cvstatus")
                        .Value("id")
                        .Label("descr")
                    )
                    .Options("cvstatus", "id", "descr")
                )
                .Field(new Field("tbl_persondetails.active").Validator(Validation.Boolean()))

                .LeftJoin("tbl_title", "tbl_title.id", "=", "tbl_persondetails.title_id")
                .LeftJoin("tbl_receivedstatus as cvstatus", "cvstatus.id", "=", "tbl_persondetails.cv_received_id")

                .Process(request)
                .Data();

            return Json(response);
        }
    }
}

My model is

public class IndividualModel
{
    public class tbl_persondetails
    {
        public int id { get; set; }
        public int title_id { get; set; }
        public string given_name { get; set; }
        public string middle_name { get; set; }
        public string family_name { get; set; }
        public string preferred_name { get; set; }
        public string previous_name { get; set; }
        public string tel_no { get; set; }
        public int cv_received_id { get; set; }
        public bool active { get; set; }

    }
    public class tbl_title
    {
        public string descr { get; set; }
    }
    public class tbl_receivedstatus
    {
        public string descr { get; set; }
    }
}

My View (cshtml) is:

var editor; 

$(document).ready(function () {
    editor = new $.fn.dataTable.Editor({
        ajax: "/api/Individual",
        table: "#userTbl",
        fields: [{
            label: "ID:",
            name: "tbl_persondetails.id"
        }, {
            label: "Title:",
            name: "tbl_persondetails.title_id",
            type: "select"
        }, {
            label: "Forename:",
            name: "tbl_persondetails.given_name"
        }, {
            label: "Middle name:",
            name: "tbl_persondetails.middle_name"
        }, {
            label: "Surname:",
            name: "tbl_persondetails.family_name"
        }, {
            label: "Preferred name:",
            name: "tbl_persondetails.preferred_name"
        }, {
            label: "Previous name:",
            name: "tbl_persondetails.previous_name"
        }, {
            label: "Contact No.:",
            name: "tbl_persondetails.tel_no"
        }, {
            label: "CV Received:",
            name: "tbl_persondetails.cv_received_id",
            type: "select"
        }, {
            label: "State:",
            name: "tbl_persondetails.active",
            type: "radio",
            options: [
                { label: "No", value: 0 },
                { label: "Yes", value: 1 }
            ],
            def: 0
        }
        ]
    });

    $('#userTbl').DataTable({
        dom: "Bfrtip",
        ajax: "/api/Individual",
        columns: [
    { data: "tbl_persondetails.id"},
    { data: "tbl_title.descr" },
    { data: "tbl_persondetails.given_name" },
    { data: "tbl_persondetails.middle_name" },
    { data: "tbl_persondetails.family_name" },
    { data: "tbl_persondetails.preferred_name" },
    { data: "tbl_persondetails.previous_name" },
    { data: "tbl_persondetails.tel_no" },
    { data: "cvstatus.descr" },
    {
        data: "tbl_persondetails.active",
        class: "center",
      render: function (val, type, row) {
            return val == 0 ? "No" : "Yes";
        }
    }

    ],
    select: true,
    buttons: [
    { extend: "create", editor: editor },
    { extend: "edit", editor: editor },
    { extend: "remove", editor: editor },
           {
               extend: 'collection',
               text: 'Export',
               buttons: [
                    'copy',
                    'excel',
                    'csv',
                    'pdf'
               ]
           }
        ]
    });
});

I have tried a few things but am at a lost on what i need to do. I have looked at the Datatable/Editor documentation, which implies what I have done is correct. However there are no MVC examples that I can fine. Can anyone point me in the right direction?

allan

I think all you need to do is update the model to reflect the alias name:

    public class cvstatus
    {
        public string descr { get; set; }
    }

You'd need to update the client-side code to reflect that field name as well.

One thing that might just confuse matters a little though is that the alias is not applied to the options list - you'd need to use:

                    .Options(new Options()
                        .Table("tbl_receivedstatus")
                        .Value("id")
                        .Label("descr")

Allan

plangan

Thanks for your suggestion Allan. The solution was much simplier. My mistake was in the ApiController where in the field definition I referenced the alias table:

            .Field(new Field("tbl_persondetails.cv_received_id")
                .Options(new Options()
                    .Table("cvstatus")
                    .Value("id")
                    .Label("descr")
                )
                .Options("cvstatus", "id", "descr")
            )

I should have referenced the original table tbl_receivestatus. The LeftJoin statement was correct.
The web page now works as expected. The issue was due to my misunderstanding of table alias's.