Add row number to JSON

Using datatables.net work awesome with server side processing turned on, cant figure out how to add row number to JSON response so I can sort displayed data.
Currently added JavaScript to HTML and row numbers displayed but not sortable or searchable, if I add row count to def render_column function than its reset on every page (on page 2 still row numbers 1-10)
If I use the id from mysql column than the row numbers doesn’t represent correctly.
Any example available ?
Like to display row numbers of returned data in JSON ie: rows available 1-147, page 1 (1-10) page 2 (11-21) … and if searching for row 133 than only row 133 displays.

1 Like

Couple quick notes here:

  1. It’s datatables.net, not datatable.net. (The link is pointing to the wrong location.)

  2. This probably isn’t a Django issue - while some of us here are familiar with the datatables library and are willing to try and help, you may find more directed assistance in some other forum.

  3. You probably could add your own indexing number for the returned rows. You get the “start” and “length” parameters in the request - you could inject those values into the JSON response before returning it.

  4. There are a number of different API calls available on the client side for handling the data from the server. We use ajax dataSrc to do some “extra processing” on the data before handing it off to datatables. (We extract some statistics that get embedded in the response, and use that data to update other parts of the page.)

What do you mean by this? How are they being represented?

user A have 147 record out of 6500, list would look like row # 123,345,1456,2478,3567,… instead on 1,2,3,4,5…

I agree but still looking for a solution so I can display row numbers of matched results (sortable, searchable)

datatables forum guru suggested this need to be addressed in Django instead of in java or html side

" As you’ve got [ server-side ] enabled, searching needs to be done on the server, so the row numbers would need to come from there too."

Having that available for the sort makes sense. Take the start value supplied to the view and add it to the first JSON entry being returned. Iterate over the entries, incrementing that added value for each one.

But I’m really not seeing where generated and arbitrary row number have anything do to with a search. It’s not like they’re values stored in the database and that they’re necessarily going to remain consistent from one search to the next. (Depending upon the search and how dynamic your data is.)

On second taught search is not necessary as its not related to results.
Suggest some input where do I get the available rows# from ?

class HomeJobListJson(BaseDatatableView):
model = Job
row_number = 0
columns = ['row_number','order','customer_name',
           'address','codes', 'calculate_total', 'updated_at','']
order_columns = ['row_number','order', 'customer_name',
                 'address', 'codes', 'calculate_total', 'updated_at','']
max_display_length = 500

def render_column(self, row, column):
     if column == 'row_number':
          ret = self.row_number +=1
          return ret

When in doubt, look at the source! That’s usually a pretty good way to see how a library is performing some function or creating or using an API.

If you look at the source for BaseDatatableView, you see it inherits from DatatableMixin. Looking at DatatableMixin, you can find the paging method, which shows an example of a method retrieving the start and length values from the request.

That not helped “me” creating a working solution, still cant order row_number columns, not even the same page (1-10) now I’m getting Ajax error probable server side processing have no clue how to sort first column what’s not even in the table.
IDK somehow need to force the new row numbers to the row_number field what’s not even exist ?! some temp variable ? IDK

Go ahead and post what you’ve tried, and we’ll take a look at it. It’s going to be easier at this point to refer to actual code rather than trying to speak in abstract generalities.

Really couldn’t get much more progress than what I have posted earlier, that will number the first column but can’t sort it, meanwhile I was attempted to setup debugger in VSCode (wanted to see line by line execution, variable values like in C#) , 5hr later I have to roll back from git as the whole test env stopped working properly (that a price for following 2-3 example/tutorial)

class HomeJobListJson(BaseDatatableView):
    model = Job
    row_number = 0
    columns = ['row_number','order','customer_name',
       'address','codes', 'calculate_total', 'updated_at','']
    order_columns = ['row_number','order', 'customer_name',
             'address', 'codes', 'calculate_total', 'updated_at','']
    max_display_length = 500

    def render_column(self, row, column):
        if column == 'row_number':
            ret = self.row_number +=1
            return ret

Let me know what is importan to share with you in order to find a solution ?

Is there is a way to populate first column with a series of numbers (1-148)

columns('row_number')[0] = 1
columns('row_number')[1] = 2

not actually working

My inclination from browsing through the source code would be to override the prepare_results method. It’s already generating a list of the entries from the queryset. If your datatables is requesting an “id” number, then that list will already have a spot for the numbers you really want to return. You could iterate through that list and replace the retrieved id numbers with the numbers you calculate.

I wouldn’t touch the render_column method - you still want those values rendered appropriately.

[created this project in PHP about 6 years ago, all written by me 'no modules, snippets, libraries" and works fine but not very modern and as some of my colleague called a “spaghetti” coding. Decided to recreate with new look and chose DJango as it have many future what i never implemented previously]

there is 1300+ job at the moment in the table created by about 15 user, the tables linked with other keys like (code, user)
When Json return data it’s only for a specific user so the table id’s are all over
Not seeing how DJango generate the JSON IDK where to look for row_id
If there is a job_id list somewhere how do I get it? Is there a way to remap it to a normal sequence (ie 1-148) probably currently have (3,45,89,343,378,567,890,1023…)

I’m not talking about anything in the data or the existing code.

Datatables sends a list of column names to be retrieved from the data. Whatever the name of the column is in datatables that you want to replace with these row numbers, that’s what you want to change.

I got ID 1294 & 1295 its should be 1&2, how to change that ?

If it were me doing this, I’d be overriding the prepare_results method. I’d be seeing what that method is doing now, and what the data is being returned by it. I would then modify that data with the new numbers before returning those results.

def prepare_results(self, qs):
    data = []
    for item in qs:
        if self.is_data_list:
            data.append([self.render_column(item, column) for column in self._columns])
        else:
            row = {col_data['data']: self.render_column(item, col_data['data']) for col_data in self.columns_data}
            data.append(row)

    return data

how should i modify it ?

I wouldn’t “modify” this, I’d override it in my own class. Create a method in your class by this name, call super in your new method to get the data, then iterate over the data to make whatever changes need to be made.

Smart idea, I guess need to read a few tutorial on “override it in my own class” ::

tenor

class HomeJobListJson(BaseDatatableView):
model = Job
columns = ['id','work_order','customer_name',
           'address','tbc', 'calculate_total', 'updated_at']
order_columns = ['id','work_order', 'customer_name',
                 'address', 'tbc', 'calculate_total', 'updated_at']

max_display_length = 500
row_number = 0


def render_column(self, row, column):
    # if column == 'row_number':
    #     self.row_number += 1
    #     return self.row_number
    
    url_enabled = True
    if column == 'work_order' and url_enabled == True:            
        if row.customer_name.isupper() == True:
            is_ccc = True
            # print(is_ccc) 
        else:
            is_ccc = False
            # print(is_ccc)                
        woscnum = ''.join(filter(str.isdigit, row.work_order))
        if row.work_order.find('Wo') == -1:
            is_wo = False
        else:
            is_wo = True
        if row.work_order.find("TTT") == -1:
            tttt = 'False'
        else:
            tttt = 'True'
            is_ccc = True
        if is_ccc:
            if is_wo == True:
                return '<a href=https:///'+ woscnum \
                        +'/='+tttt+' target="_blank">'\
                        + row.work_order +'</a>'#escape('{0}'.format(row.work_order))
            else:
                return '<a href=https:///'+ woscnum \
                        +'/='+tttt+' target="_blank">'\
                        + row.work_order +'</a>'
        else:
            if is_wo == True and is_ccc == False:
                return '<a href=https:///'+ woscnum \
                        +'/='+tttt+' target="_blank">'\
                        + row.work_order +'</a>'
            elif is_ccc == False:
                return '<a href=https:///'+ woscnum \
                        +'/='+tttt+' target="_blank">'\
                        + row.work_order +'</a>'
            elif is_wo == False and is_ccc == True:
                return '<a href=https:///'+ woscnum \
                        +'/='+tttt+' target="_blank">'\
                        + row.work_order +'</a>'
            elif  is_ccc == True:
                return '<a href=https:///'+ woscnum \
                        +'/='+tttt+' target="_blank">'\
                        + row.work_order +'</a>'


    settings_time_zone = timezone(settings.TIME_ZONE)
    if column == "calculate_total" and row.calculate_total:
        return escape("$" + format(row.calculate_total(), '.2f'))
    if column == "tbc" and row.tbc():
        return escape(row.tbc())
    if column == "updated_at" and row.updated_at:
        return escape(row.updated_at.strftime("%H:%M:%S"))
    else:
        return super(HomeJobListJson, self).render_column(row, column)

def get_initial_queryset(self):
    today = datetime.datetime.today()
    begin = datetime.datetime(
        today.year, today.month, today.day, 0, 0, 0, 0)
    return self.model.objects.filter(user=self.request.user, created_at__gt=begin) #

This is the class creating the JSON data
Would this help you to help me ?