Datatables Editor return one row at a time with where clause

Datatables Editor return one row at a time with where clause

tomslloydtomslloyd Posts: 12Questions: 3Answers: 0
edited May 21 in Free community support

Im using the Datatables editior in C# and i am unable to filter down the records that have a specific id

C# Code in the controller

int id = 13;
var formData = HttpContext.Request.Form;

            using (var db = new Database("mysql", _dbFactory.CreateConnection(ConnectionDatabase.CustomerSpecific)))
            {
                var editor = new Editor(db, "pmt_migration_nodes_view", "p_id")
                    .Field(new Field("p_id").Set(false))
                    .Field(new Field("n_id").Set(false))
                    .Field(new Field("col1").Set(false))
                    .Field(new Field("col2").Set(false))
                    .Where("p_id", id, "=");

                var response = editor.Transaction(false).Debug(true).Process(formData).Data();

                return Json(response, JsonRequestBehavior.AllowGet);

            }

It knows it has 1024 records but only shows one record a page but still says showing 1 to 10 but only as one row on display in the table

if i remove the Where clause this returns all records and paginates correctly and show 10 records per page.

using DataTables-Editor-Server.2.0.5

please help

Answers

  • kthorngrenkthorngren Posts: 21,330Questions: 26Answers: 4,951

    Based on your description it sounds like you have server side processing enabled. Is this correct?

    Please post the JSON response using the browser's network inspector tool. Please post your Datatables initialization code.

    Assuming you have server side processing enabled it sounds like your server script isn't calculating the recordsFiltered value correctly. See the Server Side Processing protocol docs for details.

    Kevin

  • tomslloydtomslloyd Posts: 12Questions: 3Answers: 0
    edited May 21

    Hey i do indeed have this option on the JS side, i get the following json return

    {
        "draw": 1,
        "data": [
            {
                ... record data ...
            }
        ],
        "recordsTotal": 1024,
        "recordsFiltered": 1024,
        "error": null,
        "fieldErrors": [],
        "id": null,
        "meta": {},
        "options": {},
        "searchPanes": null,
        "files": {},
        "upload": {
            "id": null
        },
        "debug": [
            {
                "Query": "SELECT  COUNT( p_id ) as \u0027cnt\u0027 FROM  `pmt_migration_nodes_view` WHERE `p_id` = @where_0 AND(1=1)",
                "Bindings": [
                    {
                        "Name": "@where_0",
                        "Value": 13,
                        "Type": null
                    }
                ]
            },
            {
                "Query": "SELECT  COUNT( p_id ) as \u0027cnt\u0027 FROM  `pmt_migration_nodes_view` WHERE `p_id` = @where_0 ",
                "Bindings": [
                    {
                        "Name": "@where_0",
                        "Value": 13,
                        "Type": null
                    }
                ]
            },
            {
                "Query": "SELECT  [columns here] FROM  `pmt_migration_nodes_view` WHERE `p_id` = @where_0 AND(1=1) ORDER BY `n_id`  asc  LIMIT 10 OFFSET 0",
                "Bindings": [
                    {
                        "Name": "@where_0",
                        "Value": 13,
                        "Type": null
                    }
                ]
            }
        ],
        "cancelled": []
    }
    

    setup

    var table = $('#tblSC').DataTable({
            ajax:
                {
                    "url": "/getdata",
                    "type": "POST",
                    "data": function (d) {
                        d.__RequestVerificationToken = $('input[name=__RequestVerificationToken]').val();
                    },
                },
                serverSide: true,
                searching: true,
                orderable: true,
                processing: true,
                dom: 'lBfrtip',
                select: false,
                columns: [ ..column defs here ..],
                buttons: [
                    //{ extend: "create", editor: editor },
                ],
                lengthMenu: [[10, 25, 50, 100, -1], [10, 25, 50, 100, "All"]],
                initComplete: function () {
                    $(".dataTables_filter, .dt-buttons.btn-group").remove();
                    if (!isAdmin) {
    
                    }
                }
            });
    
  • kthorngrenkthorngren Posts: 21,330Questions: 26Answers: 4,951

    It looks like your server script isn't calculating recordsFiltered properly.
    The response has "recordsFiltered": 1024, but it should be "recordsFiltered": 1, for Datatables to display the info message properly. See the Server Side Processing protocol docs for details.

    Kevin

  • tomslloydtomslloyd Posts: 12Questions: 3Answers: 0
    edited May 21

    This is being done by the DataTables C# Server Package from the post i don't actually control that its part of the class its self. is this a bug in the version we are using.

    And only messes up when the Where clause is used in the C# Editor code

  • allanallan Posts: 63,498Questions: 1Answers: 10,471 Site admin

    is this a bug in the version we are using.

    It sounds like it, but it isn't a bug I recall seeing before. What version of the dll are you using?

    The SQL statements being output don't appear to be wrong looking at them. What do you get if you directly run the following against the db:

    SELECT  COUNT( p_id ) as cnt FROM  `pmt_migration_nodes_view` WHERE `p_id` = 13
    

    ?

    Allan

  • tomslloydtomslloyd Posts: 12Questions: 3Answers: 0

    It looks like one to me too as it works fine without the where clause on the Editor and loads everything correctly.

    All the SQL Queries work correctly from the debug output and return the correct count and the correct page amount, but for some reason when it does it, it only shows one record out of the 10.

    The version is 2.0.5.0.

  • allanallan Posts: 63,498Questions: 1Answers: 10,471 Site admin

    Can you show me the full JSON that is returned? You can PM it to me if you don't want to post it in public here.

    Thanks,
    Allan

Sign In or Register to comment.