Datatables Editor return one row at a time with where clause

tomslloyd

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

kthorngren

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

tomslloyd

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) {

                }
            }
        });

kthorngren

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

tomslloyd

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

allan

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

tomslloyd

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.

allan

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