DataTables doesn't detect properly JSON?

Darojuvius

I'm currently trying to show a Table with DataTable, and all the time keeps saying "No matching records found" I'm using Django, and i have my JSON on a page from the Django, that's how i get the Ajax, and with python i put the data of a local database and show it with json module...
Javascript:

$(document).ready(function() {
      $('#table).DataTable({
          serverSide: true, 
          paging: true, 
          pageLength: 100,
          ajax: {
              url: 'X',
              dataSrc: 'data' 
          },
          deferRender: true,
          columns: [
              { data: 'x'},
              { data: 'x'},
              { data: 'x'},
              { data: 'x'},
              { data: 'x'},
              { data: 'x'},
              { data: 'x'},
              { data: 'x'},
              { data: 'x'},
              { data: 'x'},
              { data: 'x'},
              { data: 'x'},
              { data: 'x'},
              { data: 'x'},
              { data: 'x'},
              { data: 'x'},
              { data: 'x'},
              { data: 'x'},
              { data: 'x'},
              { data: 'x'},
              { data: 'x'},
              { data: 'x'},
              { data: 'x'},
              { data: 'x'},
              { data: 'x'},
              { data: 'x'},
          ]
      });
  });  

And my Json:
"{\"draw\": 2, \"recordsTotal\": 4, \"recordsFiltered\": 4, \"data\": [{\"x\": \"x\", \"bastidor\": \"x\", \"x\": \"x\", \"x\": \"x\", \"x\": \"x\", \"x\": \"x\", \"x\": x, \"x\": \"x\", \"x\": x, \"x\": null, \"x\": \"x\", \"x\": \"x\", \"x\": x, \"x\": x, \"x\": x, \"x\": \"x\", \"x\": \"x\", \"x\": \"x\", \"x\": \"x\", \"x\": \"x\", \"x\": \"x\", \"x\": \"None\", \"x\": \"x\", \"x\": \"x\", \"x\": null, \"x\": \"\"}]}
The ones without "x" are integers or floats and the one in the end doesnt have anything

What do you think? I use special characters and i think that could be the problem, because JSON doesn't detect it well, but i validate it and it says it doesn't have a problem i mean like i dont only use string as you can see...

Edited by Kevin: Syntax highlighting. Details on how to highlight code using markdown can be found in this guide

kthorngren

The JSON string has escaped double quotes (\"). This suggests you are JSON encapsulating the data twice in your Django App. Once this is fixed you can remove the ajax.dataSrc option as data is the default. It won't hurt to leave it though.

Kevin

Darojuvius

My Django code:
```def mostrar_JSON(request):
start = int(request.GET.get('start', 0))
length = int(request.GET.get('length', 100))

username = request.user.username
org = False
x = func_x(username, org)

extraQuery = ""

curPer = connections['x'].cursor()
cur = connections['default'].cursor()
cur.execute(
    "SELECT x, x, x, x,x, x, x, x, x, x, x, x, x, x, x,x, x, x, x, x, x, x, x, x,x, x\
    FROM x.x as x\
    LEFT JOIN x.x as x ON x.x  = x.x\
    LEFT JOIN (SELECT xt, MIN(x) as ix FROM x.x GROUP BY x) AS x ON x =x.x\
    LEFT JOIN (SELECT ix, x( x) as x\
    FROM x GROUP BYx) as xON x.x = x.x\
    Left JOIN (SELECT x, x as x\
    FROM x.x) as x ON x.x = x.x\
    Left JOIN (SELECT x, x\
    FROM x.x) as x ON x.x = x.x\
    where x in " + str(x) +
    " AND x in ('x', 'x') ORDER BY x DESC")

x = cur.fetchall()

x = x[start:start + length]

 x = []  

for row in llistatComandes:
    arrayLlista = {
        'x': row[0].replace('\\', ''),
        'x': row[1].replace('\\', ''),
        'x': row[2].replace('\\', ''),
        'x': row[3].replace('\\', ''),
        'x': str(row[4]),
        'x': str(row[5]),
        'x': float(row[6]),
        'x': row[7].replace('\\', ''),
        'x': float(row[8]) if row[8] is not None else None,
        'x': row[9],
        'x': row[10],
        'x': row[11].replace('\\', ''),
        'x': row[12],
        'x': float(row[13]),
        'x': row[14],
        'xt': row[15].replace('\\', ''),
        'x': row[16].replace('\\', ''),
        'x': row[17],
        'x': row[18],
        'x': row[19].replace('\\', ''),
        'x: row[20].replace('\\', ''),
        'x': str(row[21]),
        'x': row[22].replace('\\', ''),
        'x': row[23],
        'x': row[24],
        'x': row[25].replace('\\', '')
    }
    x.append(x)  

response_data = {
    "draw": int(request.GET.get('draw', 2)),
    "recordsTotal": len(arrayLlistatComandes),
    "recordsFiltered": len(arrayLlistatComandes),
    "data": arrayLlistatComandes,
}


json_data = json.dumps(response_data, default=decimal_serializer, ensure_ascii=True)
json_data = json_data.translate(str.maketrans('', '', '\\'))
print(json_data)
return JsonResponse(json_data, safe=False) ```

I don't know how to fix it, maybe the error is in the DataBase?

kthorngren

In line 68 you are serializing response_data into JSON. Then in line 71 you are serializing the data again into a JSON string. I think you can remove 68-70 and just use the JsonResponse method to serialize response_data.

Kevin

kthorngren

Your Python code is a bit confusing. I guess you are changing the variables and other data with just x to post on the forum.

The idea with server side processing is to create a SQL query that only gets the records to be displayed on the page. It looks like you are fetching all the records from the DB then using x = x[start:start + length] to just get the records for the page. To keep from fetching all the rows use SQL LIMIT and OFFSET to fetch only the rows from the page.

Maybe you don't need server side processing enabled. Do you have many thousands of rows that cause performance issues? If not you can disable server side processing, to return all rows, and let sorting, searching and paging happen at the client. You won't need to calculate and return the draw, recordsTotal and recordsFiltered values.

It doesn't look like you are calculating the recordsTotal and recordsFiltered values correctly. recordsTotal should be the total number of records in the DB. recordsFiltered should be the total number of records after filtering. The above code is setting these to the number of records returned. This will result in the Datatables thinking you have only one page even if you have more than 100 records (pageLength: 100,). See the (server side processing protocol docs](https://datatables.net/manual/server-side) for details.

It looks like your SQL query doesn't support the sorting and searching capabilities from the client side Datatable. See the server side processing protocol docs for details. There are third party Django libraries that support server side processing. I haven't used any but, if you need server sid eprocessing, you might want to take a look to see what's available. For example:
https://pypi.org/project/django-serverside-datatable/

Kevin