deferRender doesn't work?

Darojuvius

I'm using Django for my page, i want to use server-side without any pips, and i discovered that if JSON has the attributes draw, recordstotal, recordsfiltered, and data, DataTables can handle the JSON and use deferRender, i tried it and it works. But i tried it on a bigger project and I't doesn't work properly, it has 2 ways of work: It does the deferRender but instead of charging only 10-25-50-100 rows, it loads the JSON all the time when i change of page, and the other one is simply he downloads the JSON and it works, but I want deferRender because it slows my page loading...
This is my JSON:

{"draw": 2, "recordsTotal": 4100, "recordsFiltered": 4100, "data": [{"x": "x"},

Imagine for data shown...
My JS:

$(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 views.py that generates the json:

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 = []  
 
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,
}
 
 

return JsonResponse(x) 

What could be the problem?

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

kthorngren

Please read my last comment in your other thread.

It doesn't look like you are using LIMIT and OFFEST in your SQL query to limit the records returned to those for the page. So you are returning more records than the pageLength: 100, specifies. Datatables only sends a request for the page data. Its up to the server script to read the server side parameters and use the start and length to determine the records to return.

deferRender tells Datatables to only render the HTML elements for the page being displayed. This applies when using client side processing as all the rows are at the client. deferRender doesn't apply with server side processing as the only rows at the client are the rows on the page.

As I said in the other thread you might want to look at a third party Django library that supports server side processing so you don't have to write the code.

Kevin

Darojuvius

Well, DeferRender worked on my testing page this are my codes:

def data_to_json(request):
    start = int(request.GET.get('start', 0))
    length = int(request.GET.get('length', 100))

    cur = connections['default'].cursor()
    cur.execute("SELECT id, nombre, seccion, precio FROM prueba.baseDatos_articulos LIMIT 10000")
    llistaArticulos = cur.fetchall()

    total_records = len(llistaArticulos)

    # Filtra los registros para la página actual
    data = llistaArticulos[start:start + length]

    # Convierte los registros en un formato adecuado
    data_dict = []
    for row in data:
        data_dict.append({
            'id': row[0],
            'nombre': row[1],
            'seccion': row[2],
            'precio': row[3]
        })

    response_data = {
        "draw": int(request.GET.get('draw', 2)),
        "recordsTotal": total_records,  # Establece el número total de registros
        "recordsFiltered": total_records,  # Puedes filtrar si es necesario
        "data": data_dict,
    }

    return JsonResponse(response_data)

and my js

$(document).ready(function() {
    $('#mytable').DataTable({
        serverSide: true, // Asegúrate de que no sea "true"
        paging: true, // Asegúrate de que esté habilitado
        pageLength: 100, // Puedes configurar el número de registros por página aquí
        ajax: {
            url: 'http://127.0.0.1:8000/baseDatos/data_to_json/',
            dataSrc: 'data'  // Especifica la clave 'data' como origen de los datos
        },
        deferRender: true,
        columns: [
            { data: 'id' },
            {
                data: 'nombre',
                render: function(data, type, row, meta) {
                    return '<td style="color:red">' + data +  '<i class="fa-solid fa-house"></i>'  +'</td>';
                }
            },
            { data: 'seccion' },
            { data: 'precio' },
            
            // Agrega más columnas según tus necesidades
        ]
    });
});

and deferRender works perfectly

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

Darojuvius

Also, in my main page , if i put a length or an start, it does deferRender, but instead of doing it correctly it just entirely downloads the entire json again and again each time i pass a page, even if i go back, it just downlaods all the time the entire json for each page

Darojuvius

My post about deferRender working, it meant for another page, in my main page it doesn't work, even if i put the same...

kthorngren

When using the triple backticks (```) for code formatting they need to be on their own lines. Thanks for trying to format the code

deferRender is a client side function only. It doesn't perform any action to tell the server script what to display. Your server code is limiting the returned records with this:

    # Filtra los registros para la página actual
    data = llistaArticulos[start:start + length]

You can verify that deferRender does not have an affect by commenting out line 10. Use the browser's network inspector and you will still see the 100 records returned.

Instead of fetching 10000 rows with:

    cur.execute("SELECT id, nombre, seccion, precio FROM prueba.baseDatos_articulos LIMIT 10000")

Use something like this to limit the rows selected by SQL:

    cur.execute(f"SELECT id, nombre, seccion, precio FROM prueba.baseDatos_articulos LIMIT {start} OFFSET {length}")

You can then remove the data = llistaArticulos[start:start + length] as llistaArticulos should have the proper records.

Kevin

Darojuvius

Hello Kevin, I did as you said, i putted the length and offsett and now it only shows 100 registers, not the 4100 that i have in one user in specific, and i putted length=4100. Also, if you use without lentgh a offsett, whenever you are going to the next page, it entirely downloads the JSON, i didn't change any code, know an answer?

allan

Sounds like your server-side processing implementation isn't complete.

You need to make three queries to the database:

1. Get the records for the current page only (i.e. a OFFSET and LIMIT should be specified along with the WHERE condition)
2. A count for the number of records in the current data set after filtering (with a WHERE)
3. A count for the number of records in the current data set before filtering.

DataTables needs the first to display the current page, and the second two for paging. There are more details in the manual page.

To be honest though, with only 4100 rows, don't bother with server-side processing (unless the data in each is really large). Just dump the whole lot back to the client-side in a single Ajax call and disable serverSide.

Allan

Darojuvius

Hello Allan, the thing is, maybe a user has 4k rows, but if the admins needs to load all the rows of the entire database it will be very slow (sorry I didn't say that). Also, I just saw, when i do serverSide it downloads only the 10 rows, but takes like 40 seconds, takes the time that it will take to load all the 4k rows, but only downloads the first 10... why?

Darojuvius

Oh wait I think i got it! I think it was the query, but not if it was missing things like limit or offset, the query is very complex and does lots of things, and maybe that slowdowns the charge and performance of DataTables, because if I do it with a simpler query It works perfectly!

Darojuvius

Other thing I should ask, when serverSide is true, and you move on to the next page, I am assuming that he does another query for the next 10, and he takes more time? Because i tried showing 1k at the same time and takes the same time that showing only 10 entries (it doesn't have more than 10 entries at the moment)

1k is about 3MB and takes 35 seconds.
10 is about 100kb and takes 35 seconds?

My only answer to that is the sql query sintax, and I think it could be, it's very complex... And he does every time to get the other files...

Darojuvius

Sry, I am quiet new and I don't know how to use technical english neither I know how to express my self in this kind of situation haha.

kthorngren

Use the browser's network inspector (see this technote for instructions) to see the JSON response. You should see only the number of rows to be displayed on the page. For example if you have pageLength: 100, then the server script should return only 100 records in the JSON. If you see all the rows returned then your SQL query isn't using LIMIT and OFFSET to reduce the number of rows in the query result set.

Other thing I should ask, when serverSide is true, and you move on to the next page, I am assuming that he does another query for the next 10

Yes. Please look at the Server Side Processing protocol docs. Each time the table is sorted, searched or paged a request is sent to the server for the rows to be displayed. The start and length parameters sent correspond to the page number and number of rows expected to be returned from the server.

Kevin

kthorngren

Take a look at this server side processing example. Click the Ajax tab to see the JSON response. Go to different pages in the table and you will see only 10 rows of data returned for each page. This is what your server script is expected to do.

You can use the browser's network inspector to see the request parameters sent to see the start and length parameter values.

Kevin