stateLoadCallback not working from DB

matissg

I'm new to coding and Datatables and totally stuck as I cannot make my JS to load prepared JSON from DB:

{"time":"1495134965190","start":"0","length":"10","order":{"0":["0","asc"]},"search":{"search":"","smart":"true","regex":"false","caseInsensitive":"true"},"columns":{"0":{"visible":"true","search":{"search":"","smart":"true","regex":"false","caseInsensitive":"true"}},"1":{"visible":"true","search":{"search":"","smart":"true","regex":"false","caseInsensitive":"true"}},"2":{"visible":"true","search":{"search":"","smart":"true","regex":"false","caseInsensitive":"true"}}},"ColReorder":["0","1","2"]}

and this is my JS:

jQuery(document).ready(function() {
  var user_id = $("#data-table").attr('data-user-id');
  var contname = $("#data-table").attr('data-controller-name');
    $('#data-table').DataTable(
            {
                "processing": true,
                "responsive": true,
                "colReorder": true,
                "serverSide": true,
                "ajax": $('#data-table').data('source'),
                "stateSave": true,
                "stateSaveCallback": function (settings, data) {
                    $.ajax( {
                        "url": "/common/datatables_states/"+contname+".json",
                        "data": {"common_datatable_state":{"user_id": user_id, "name": contname, "state": data}} ,
                        "dataType": "json",
                        "method": "PATCH",
                        "success": function () {}
                    } );
                },
                stateLoadCallback: function (settings, callback) {
                    $.ajax( {
                         url: "/common/datatables_states/"+contname+".json",
                         async: false,
                         dataType: "json",
                         method: 'GET',
                         success: function (json) {
                             callback( json );
                         }
                    } );
                }
            }
    );
});

Link yo my Debug is this.
My stateSaveCallback is working as I see in my terminal it saves state, however I cannot get stateLoadCallback wokring I'll be happy for any help. Thank you!

allan

"start":"0","length":"10"

These should be integers rather than strings.

What happens when it is loaded - do you get any errors?

Allan

matissg

I'm using Rails and in my terminal I don't see any error. It looks like this:

Started GET "/common/datatables_states/campaigns_index.json" for 10.0.2.2 at 2017-05-19 18:44:51 +0000
Processing by Common::DatatablesStatesController#show as JSON
  Parameters: {"id"=>"campaigns_index"}
  User Load (0.6ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT $2  [["id", 2], ["LIMIT", 1]]
  Role Load (1.1ms)  SELECT "roles".* FROM "roles" WHERE "roles"."user_id" = $1  [["user_id", 2]]
  Common::DatatableState Load (3.6ms)  SELECT  "common_datatable_states"."state" FROM "common_datatable_states" WHERE "common_datatable_states"."user_id" = $1 AND "common_datatable_states"."name" = $2 ORDER BY "common_datatable_states"."id" ASC LIMIT $3  [["user_id", 2], ["name", "campaigns_index"], ["LIMIT", 1]]
Completed 200 OK in 105ms (Views: 2.7ms | ActiveRecord: 14.1ms)

My stateSaveCallback saves JSON like this:

Started PATCH "/common/datatables_states/campaigns_index.json" for 10.0.2.2 at 2017-05-19 18:31:33 +0000
Processing by Common::DatatablesStatesController#update as JSON
  Parameters: {"common_datatable_state"=>{"user_id"=>"2", "name"=>"campaigns_index", "state"=>{"time"=>"1495218692828", "start"=>"0", "length"=>"10", "order"=>{"0"=>["0", "asc"]}, "search"=>{"search"=>"", "smart"=>"true", "regex"=>"false", "caseInsensitive"=>"true"}, "columns"=>{"0"=>{"visible"=>"true", "search"=>{"search"=>"", "smart"=>"true", "regex"=>"false", "caseInsensitive"=>"true"}}, "1"=>{"visible"=>"true", "search"=>{"search"=>"", "smart"=>"true", "regex"=>"false", "caseInsensitive"=>"true"}}, "2"=>{"visible"=>"true", "search"=>{"search"=>"", "smart"=>"true", "regex"=>"false", "caseInsensitive"=>"true"}}}}}, "id"=>"campaigns_index"}
  User Load (1.2ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT $2  [["id", 2], ["LIMIT", 1]]
  Role Load (1.5ms)  SELECT "roles".* FROM "roles" WHERE "roles"."user_id" = $1  [["user_id", 2]]
  Common::DatatableState Load (8.2ms)  SELECT  "common_datatable_states".* FROM "common_datatable_states" WHERE "common_datatable_states"."user_id" = $1 AND "common_datatable_states"."name" = $2 ORDER BY "common_datatable_states"."id" ASC LIMIT $3  [["user_id", 2], ["name", "campaigns_index"], ["LIMIT", 1]]
   (1.2ms)  BEGIN
  SQL (2.1ms)  UPDATE "common_datatable_states" SET "state" = $1, "updated_at" = $2 WHERE "common_datatable_states"."id" = $3  [["state", "\"{\\\"time\\\":\\\"1495218692828\\\",\\\"start\\\":\\\"0\\\",\\\"length\\\":\\\"10\\\",\\\"order\\\":{\\\"0\\\":[\\\"0\\\",\\\"asc\\\"]},\\\"search\\\":{\\\"search\\\":\\\"\\\",\\\"smart\\\":\\\"true\\\",\\\"regex\\\":\\\"false\\\",\\\"caseInsensitive\\\":\\\"true\\\"},\\\"columns\\\":{\\\"0\\\":{\\\"visible\\\":\\\"true\\\",\\\"search\\\":{\\\"search\\\":\\\"\\\",\\\"smart\\\":\\\"true\\\",\\\"regex\\\":\\\"false\\\",\\\"caseInsensitive\\\":\\\"true\\\"}},\\\"1\\\":{\\\"visible\\\":\\\"true\\\",\\\"search\\\":{\\\"search\\\":\\\"\\\",\\\"smart\\\":\\\"true\\\",\\\"regex\\\":\\\"false\\\",\\\"caseInsensitive\\\":\\\"true\\\"}},\\\"2\\\":{\\\"visible\\\":\\\"true\\\",\\\"search\\\":{\\\"search\\\":\\\"\\\",\\\"smart\\\":\\\"true\\\",\\\"regex\\\":\\\"false\\\",\\\"caseInsensitive\\\":\\\"true\\\"}}}}\""], ["updated_at", "2017-05-19 18:31:33.082065"], ["id", 3]]
   (1.3ms)  COMMIT
Completed 200 OK in 69ms (Views: 2.0ms | ActiveRecord: 15.5ms)

allan

Could you try removing:

async: false,

from the Ajax call you are making. The latest DataTables will handle an async load okay with the callback function called.

Failing that, can you link to a test case showing the issue please?

Thanks,
Allan

matissg

HI Allan,

Disabling async: false, did not work. I even tried stateLoadCallback with stateSaveCallback commented, but it does not help as well.
For example, I search record by "big group" and my JSON looks fine to me:

{
  "time": "1498071235291",
  "start": "0",
  "length": "10",
  "order": {
    "0": [
      "1",
      "asc"
    ]
  },
  "search": {
    "search": "big group",
    "smart": "true",
    "regex": "false",
    "caseInsensitive": "true"
  },
  "columns": {
    "0": {
      "visible": "true",
      "search": {
        "search": "",
        "smart": "true",
        "regex": "false",
        "caseInsensitive": "true"
      }
    },
    "1": {
      "visible": "true",
      "search": {
        "search": "",
        "smart": "true",
        "regex": "false",
        "caseInsensitive": "true"
      }
    },
    "2": {
      "visible": "true",
      "search": {
        "search": "",
        "smart": "true",
        "regex": "false",
        "caseInsensitive": "true"
      }
    },
    "3": {
      "visible": "true",
      "search": {
        "search": "",
        "smart": "true",
        "regex": "false",
        "caseInsensitive": "true"
      }
    },
    "4": {
      "visible": "true",
      "search": {
        "search": "",
        "smart": "true",
        "regex": "false",
        "caseInsensitive": "true"
      }
    }
  }
}

As you can see my search value is there, so on page refresh I should be seeing my Datatables populating particular search as it has to loaded from DB, but it does not happen. It just renders regular table. stateSaveCallback however is working just fine - writes JSON to DB as expected.

I've been inspired by this blog post on stateSave/Load and this live demo. That's PHP version, so I'm trying to adapt this to my Rails app + there is older version of Datatables used.

Do you see anything I could be checking in addition to what I've done? By "test case" did you mean some live demo?

tangerine

By "test case" did you mean some live demo?

Yes.
https://datatables.net/forums/discussion/12899/post-test-cases-when-asking-for-help-please-read#latest

allan

I don't see anything else obvious from the above. A link to a test case probably would be needed to help resolve this.

Using the debugger might give us the information to resolve the issue, but I'm not sure.

Allan

Chase

So I just stumbled upon this issue as well. It looks to me like there is a clause in the datatable code that rejects any stateloadcallback if the 'time' field is older than 2 hours.

/* Reject old data */
var duration = settings.iStateDuration;

 if ( duration > 0 && state.time < +new Date() - (duration*1000) ) {
     return;
 }

When the user saves their settings I grab their local storage item and put the entire statesave in the database. To fix this 'reject old data issue' I just set the 'time' field coming back from the database equal to the current time like below:

    var filters;
    $.ajax({
        async: false,
        url:'/api/v1/xxxxx',
        success: function(data){
            // if filters aren't set exit function
            if (!$.trim(data)){   
                return filters = false;
            }

            //JSON parse the saved filter and set the time equal to now.

            filters = JSON.parse(data[0]['filters']);
            filters.time = +new Date();    
        }
    })

    //sets the datatables to default if the user doesn't have saved filters
    //I used the local storage item with the defaults I want if the user has no filter in 
    //the database. 

    if (filters == false){
       var time = +new Date();
           return filters = ({"time":time,"start":0,"length":10,"order":[[10,"asc"]],"search": 
                   {"search":"","smart":true,"regex":false,"caseInsensitive":true}, 
                   "columns":[{"visible":true,"search":"search":"", "smart":true,"regex":false, 
                   "caseInsensitive":true}}});  //EXAMPLE ONLY
     } else {
        return filters;
     }