Trying to JOIN two of the same table in C# MVC

airmaster

I am completely new to datatables, and I am trying to see if it will fit my needs for editable tables. I have a table that is joined twice on different fields. One join is for a customer, and the other is for the company servicing the customer, both pull from a table of companies. My platform is C#, MVC 5. I haven't written the client side yet, just accessing the controller from postman.

Database models

public class tblWinLoss
{
   public int ID {get;set;}
   public string Title {get;set;}
   public int CustomerID {get;set;}
   public int SupplierID {get;set;}
}

public class tblCompany
{
   public int ID {get;set;}
   public string CompanyName {get;set;}
}

Template models

public class JoinWinLoss
{
   public int ID {get;set}
    public string Title { get; set; }        
}


public class JoinCompany
{
    public int ID { get; set; }
    public string CompanyName { get; set; }
}

What I want to do is join them like this, and then use the inline editor to use dropdowns (actually autocompletes, given the large number of companies, but I figure dropdowns would be easier to discuss at this point) to select the companies.

SELECT A.ID, A.Title, B.CompanyName as Customer, C.CompanyName as Supplier
FROM tblWinLoss A
JOIN tblCompany B ON B.ID = A.CustomerID
JOIN tblCompany C ON C.ID = A.SupplierID

I am struggling with how to properly represent this. I have reviewed the documentation, such as here: https://editor.datatables.net/manual/net/joins

                var Response = new Editor(db, "tblWinLoss")
                    .Model<JoinWinLoss>("tblWinLoss")
                    .Model<JoinCompany>("a")
                    .Model<JoinCompany>("b")
                    .Field(new Field("tblCompany.CompanyName")
                        .Options(new Options()
                            .Table("tblCompany")
                            .Value("ID")
                            .Label("CompanyName")
                        )
                        .Validator(Validation.DbValues(new ValidationOpts { Empty = false }))
                    )
                    .Field(new Field("tblCompany.CompanyName")
                        .Options(new Options()
                            .Table("tblCompany")
                            .Value("ID")
                            .Label("CompanyName")
                        )
                        .Validator(Validation.DbValues(new ValidationOpts { Empty = false }))
                    )

                    .LeftJoin("tblCompany as a ", "a.ID", "=", "tblWinLoss.WinningCompanyID")
                    .LeftJoin("tblCompany as b", "b.ID", "=", "tblWinLoss.CustomerID")
                    .Process(request)
                    .Data();

I tried just to see if I could get the alias working, but no luck on the example code (I renamed the tables and prefixed with datatables_)

var oop = new Editor(db, "datatables_users")
   .Field(new Field("datatables_users.main_site"))
   .Field(new Field("datatables_users.backup_site"))
   .Field(new Field("mainSite.name"))
   .Field(new Field("backupSite.name"))
   .LeftJoin("datatables_sites as mainSite", "mainSite.id", "=", "datatables_users.main_site")
   .LeftJoin("datatables_sites as backupSite", "backupSite.id", "=", "datatables_users.backup_site")
   .Process(request)
   .Data();

this gives me the error of "Invalid column name 'main_site'.\r\nInvalid column name 'backup_site'.\r\nInvalid column name 'main_site'.\r\nInvalid column name 'backup_site'."

So, could someone please help me join the tables, and give me a result that I can then pass to the view?

airmaster

After more reading and trying things I think I have figured it out. I even added a additional table of City with just two fields, ID and CityName.

                var Response = new Editor(db, "tblWinLoss")
                    .Model<JoinWinLoss>("tblWinLoss")
                    .Model<JoinCity>("tblCity")
                    .Model<JoinCompany>("Winners")
                    .Model<JoinCompany>("Customers")
                    .Field(new Field("tblCity.CityName")
                        .Options(new Options()
                            .Table("tblCity")
                            .Value("ID")
                            .Label("CityName")
                        )
                    )
                    .Field(new Field("Winners.CompanyName", "Winner")
                        .Options(new Options()
                            .Table("tblCompany")
                            .Value("ID")
                            .Label("CompanyName")
                        )
                    )
                    .Field(new Field("Customers.CompanyName", "Customer")
                        .Options(new Options()
                            .Table("tblCompany")
                            .Value("ID")
                            .Label("CompanyName")
                        )
                    )
                    .LeftJoin("tblCompany as Winners", "Winners.ID", "=", "tblWinLoss.WinningCompanyID")
                    .LeftJoin("tblCompany as Customers", "Customers.ID", "=", "tblWinLoss.CustomerID")
                    .LeftJoin("tblCity", "tblCity.ID", "=", "tblWinLoss.CityID")
                    .Process(request)
                    .Data();

allan

That looks spot on - nice one.

Allan