MJoin tables

nuggetap3

Hello Alan.
I need your help to connect two tables together using the same generator Id.
For example, one employee has certain generator Id and when he/she starts to create a new row of the table, this row will have the same generator id connect this employee and his rows.
When I want to open this form it should show me only his/her rows that connect to his/her form. The Database will store all rows from employees. I try to figured it for couple weeks already and I am stuck on it. Please give me idea what do I need to use here to resolve this issue.
I tried to use in Controller table "Where" statement but it didn't work.
There are my code for Table js:

(function($){

$(document).ready(function() {
var editor = new $.fn.dataTable.Editor( {
ajax: '/api/Table',
table: '#Table',
fields: [
{
"label": "GeneratorID:",
"name": "generatorid",
"className": "formidne"

        },
        {
            "label": "MATExample#:",
            "name": "mate_example"
        },
        {
            "label": "Description:",
            "name": "description",
            "type": "textarea"
        },
        {
            "label": "Example2",
            "name": "example2"
        },
        {
            "label": "Example3:",
            "name": "example3"
        },
        {
            "label": "Example4",
            "name": "example4"
        }
    ]

});

editor.on('open', function () {

    var id1 = $("#thisid").val();
    var id2 = $("#thisid2").val();
    if (id1 != "") {
        var id = $("#thisid").val();


    }
    else {
        var id = $("#thisid2").val();

    }

    $("#DTE_Field_generatorid").val(id);
    $(".formidne").hide();



});

var table = $('#Table').DataTable({
    columnDefs:[
       {visible:false,targets:0}
    ],
    dom: 'Bfrtip',
    ajax: {
        url: '/api/Table',
        type:"post",
        data: {"generatorid": formId}
         data:function(data){
           data.generatorid = $("#thisid2").val();
            var formId = data.generatorid;


         }

},   

    columns: [
        {
            "data": "generatorid"

        },
        {
            "data": "mate_example"
        },
        {
            "data": "description"
        },
        {
            "data": "example2"
        },
        {
            "data": "example3"
        },
        {
            "data": "example4"
        }
    ],

    select: true,
    responsive:true,

    buttons: [
        { extend: 'create', editor: editor },
        { extend: 'edit',   editor: editor },
        { extend: 'remove', editor: editor }
    ]
});

} );

}(jQuery));

Code for Controller:

public class AdjustmentTableController : ApiController
{
private readonly string dbCon = " Example";
[Route("api/Table")]
[HttpGet]
[HttpPost]
public IHttpActionResult Table()
{

        var request = HttpContext.Current.Request;

        using (var db = new Database("sqlserver", dbCon))
        {

            var response = new Editor(db, "Table","generatorid").Where(q => q.Where("generatorid", " ", "="))
                .Model<TableModel>()
                .MJoin(new MJoin("Form").Model<Model>()
                 .Link("Table.generatorid", "Form.generatorid"))
                .Process(request)
                .Data();

            return Json(response);
        }
    }
}

}

Using Mjoin should give me results from both tables but don't working.

Also, have cshtml and model class that they are easy.

Please give me ideas what do I need to use, maybe "LeftJoin " statement I can use. Let me know if you need anythin else.

Thank you very much!!

allan

Could you show me the JSON being returned from the server please? It looks like it it would be returning only rows where generatorid is a string with a single space value, which is probably not what you want?

Do you want a single large table showing all entries from Table, or do you want it restricted to a single generatorid (in which case a Where statement with a suitable value would be appropriate).

Allan

nuggetap3

Thank you for respond, Allan!
I was working yesterday all day and 'where' clause is not working here how do I expect to receive results. Yes, you are right generatorid returns only one row that it is in database but I need to returns all rows that has the same generatorid #. Query should pull out only generatorid that equally to another generator id from second table.
I am not sure if we can pass string generatorid through Table() method? I want a single large table showing all entries from Table where show all rows with certain generatorid! Hope, this more make sense for you.
I changed a little bit code:

public IHttpActionResult Table()
{

        var request = HttpContext.Current.Request;

        using (var db = new Database("sqlserver", dbCon))
        {

            var response = new Editor(db, "Table","id")
                .Model<TableModel>()

                .LeftJoin("Form", "Form.generatorid", "=", "Table.generatorid")
                 .Where("generatorid", "generatorid")

                .Process(request)
                .Data();

            return Json(response);
        }
    }
}

}

But this code is not working yet, it is not returned anything from Database and not allow me to create a new row in DataTable.
Any ideas how I can rewrite this code?
Thank you very much. Allan!

nuggetap3

When I am using .LeftJoin- it is not allowed me to create a new row for Table but when I use .MJoin and .Link it is works fine with creating new rows and save in db. I am not sure what is different!

allan

A left join is a 1:1 relationship - i.e. the parent row (Table in your above code) can reference a single record in the linked table. That's good for things such as a location or single entity reference.

An mJoin is a 1:many relationship - i.e. the parent row can reference multiple records in the child table. Example: access roles for a single employee - they might have multiple.

Which is the relationship you are using?

Allan

nuggetap3

I think mJoin relationship. I am using generatorid from main table-form to connect it to datatable-Table with all rows that have the same generatorid!

nuggetap3

Can I use ajax.data to pass this generatorid instead of using server site? Thank you!

nuggetap3

Allan,
We are trying to set the "where" condition in the api controller based on a field that is located on the form. When the form loads, we want to grab that value and pass it to the api controller where that can say "where forminstanseid = formid". I attached the code we are using from our .js file and the api controller. What are we missing??

//This is from the API Controller

public class AdjustmentTableController : ApiController
{

    [Route("api/AdjustmentTable")]
    [HttpGet]
    [HttpPost]
    public IHttpActionResult AdjustmentTable**(string formid**)
    {

        var request = HttpContext.Current.Request;

        using (var db = new Database("sqlserver", dbCon))
        {

            var response = new Editor(db, "AdjustmentTable","id").Where(q => q.Where("forminstanseid", **formid**, "="))
                .Model<AdjustmentTableModel>()
                .MJoin(new MJoin("MaterialAdjustmentForm").Model<MaterialAdjustmentModel>()
                  .Link("AdjustmentTable.forminstanseid", "MaterialAdjustmentForm.FormInstanceId"))      



                .Process(request)
                .Data();

            return Json(response);
        }
    }
}

// This is from the .js file

var formid = $("#thisid2").val();
var table = $('#AdjustmentTable').DataTable({
columnDefs:[
{visible:false,targets:0}
],
dom: 'Bfrtip',
ajax: {
url: '/api/AdjustmentTable',
type: "post",

      **  data:function(data){
             data.forminstanseid = formid                
             alert(data.forminstanseid);
             return data
         }**

},   

    columns: [
        {
            "data": "forminstanseid"

        },
        {
            "data": "mat"
        },
        {
            "data": "description"
        },
        {
            "data": "plus_or_minus_qty"
        },
        {
            "data": "unit_cost"
        },
        {
            "data": "extended_cost"
        }
    ],

    select: true,
    responsive:true,
    //lengthChange: false,
    buttons: [
        { extend: 'create', editor: editor },
        { extend: 'edit',   editor: editor },
        { extend: 'remove', editor: editor }
    ]
});

allan

Hi,

Using ajax.data is the correct way to send additional information to the server when DataTables makes the request. If you have a look at the Ajax request in your browser's network inspector, you should be able to see that it has a forminstanseid property (unless the value is undefined!).

So the key question is then, how to get that value on the server-side - you can use request.Form["forminstanseid"] for that.

Regards,
Allan