DataTables with Django

EN99

First of all, let me share what I hacked together tonight for the use of DataTables with Django in order to create tables of data. I used it together with Django Ninja, an API plugin for Django which is very helpfull is this case. It's a bit messy still; I had to maintain two dictionaries because of the naming conventions of my Model instances.

I basically have three parts:

The Ninja API:

        import importlib
        from masterdata.models import crud_dict as crud_dict
        from frontend.views import capitalizeFirstChar

        @api.get("crud/{model_src}", tags=["CRUD"])
        def crud_material(request, model_src):
           length = int(request.GET.get("length"))
           start = int(request.GET.get("start"))
           limit = start + length

           search_string = request.GET.get('search[value]')
           try:
              order_col = int(request.GET.get('order[0][column]'))
           except:
              order_col = 0

           order_dir = request.GET.get('order[0][dir]')
           draw = int(request.GET.get("draw"))
           columns = crud_dict[model_src]
           mod_ref = crud_class_dict[model_src]
           filter_q = Q()
           if len(search_string)>0:
              val = search_string
              for x in columns:
                 filter_q |= Q(**{f'{x}__icontains': val})  #### Add all filters 

           order = columns[order_col]
           if order_dir == 'desc':
              order = '-' + order

           module = importlib.import_module('masterdata.models')
           mod_class = getattr(module, mod_ref)

           data = mod_class.objects.filter(filter_q).order_by(order).values_list(*columns)[start:limit]

           item_count = mod_class.objects.filter(filter_q).count()
           json_data = list(data)

           crud_data = {}
           crud_data['draw'] = draw
           crud_data['recordsTotal'] = item_count
           crud_data['recordsFiltered'] = item_count
           crud_data['data'] = json_data

           return crud_data

Then second the view in the app itself:

        from masterdata.models import crud_dict as crud_dict
        def crud_page(request, model_src):

            if model_src.lower() in crud_dict:
                print("Model exists")

            else:
                print("Model does not exist.. Escape, do something.")
                raise PermissionDenied

            columns = crud_dict[model_src]
            context = {
                'page_title': "Overview of model " + capitalizeFirstChar(model_src),
                'columns': columns,
                'model': model_src.lower(),
            }
            return render(request, "crud/crud_2.html", context)

And finally the template:

    <table id="data_table" class="display" style="width:100%">
            <thead>
                <tr>
                  {% for i in columns %}
                  <th>{{ i }}</th>
                  {% endfor %}
                </tr>
            </thead>
            <tfoot>
                <tr>
                  {% for i in columns %}
                  <th>{{ i }}</th>
                  {% endfor %}
                </tr>
            </tfoot>
      </table>

    <script>
      DataTable.type('num', 'detect', () => false);
      DataTable.type('num-fmt', 'detect', () => false);
      DataTable.type('html-num', 'detect', () => false);
      DataTable.type('html-num-fmt', 'detect', () => false);
      new DataTable('#data_table', {
        ajax: '../api/crud/{{model}}',
        processing: true,
        serverSide: true,

    });

    </script>

**In the model I have two dictionaries; **
Unfortunately had to make the second to because I'm not always consistent with naming my models; sometimes there are Uppercases half way.

> > crud_dict = {
> >     'material': ['id', 'code', 'name'],
> >     'properties': ['id', 'code', 'name'],
> >     }
> > 
> > crud_class_dict = {
> >     'material': 'Material',
> >     'properties': 'Properties',
> > }

The funny thing is, you can relatively easy expand it, by adding values to the dictionary; by just adding the model name and respective columns it automatically is properly taking into account in the template and the API response. It creates the filter dynamically on the list of columns. Thought I'd share it, for whoever might encounter the same situation with Django.

I got one question though; What is the easiest way to add a standardized hyperlink to the column ID (if exists)? Would like to trigger a Modal with a form to update the values.

kthorngren

Nice job using Django.

What is the easiest way to add a standardized hyperlink to the column ID (if exists)?

Use columns.render. See the forth example in the docs. Also see this running example.

Kevin

EN99

Sorry if this is stupid, but shouldn't below work? For some reason if I add the columns it breaks the datatable. I'm unfortunately not that familiar with JavaScript.

       <script>
          DataTable.type('num', 'detect', () => false);
          DataTable.type('num-fmt', 'detect', () => false);
          DataTable.type('html-num', 'detect', () => false);
          DataTable.type('html-num-fmt', 'detect', () => false);
          new DataTable('#data_table', {
            ajax: '../api/crud/{{model}}',
            processing: true,
            serverSide: true,
            pageLength: 50,

            columns: [
              {
                data: 'id',
                render: function (data) {
                            link = 'https://editor.datatables.net';
                            return '<a href="' + link + '">' + data + '</a>';
                        }
                        return data;
                }
            ]
        });

        </script>

kthorngren

For some reason if I add the columns it breaks the datatable.

What error(s) do you get? Might need to look at the browser's console.

Here are some guesses as to the issues:

When columns is defined it is expected that all the columns in the table are defined. ITs not clear from the code snippet how many columns you have. See the docs for details.

Does the Ajax response contain objects or arrays for the row data? If array data then using columns.data won't work. If objects make sure there is an object with the key id. See the Data source types doc for details.

return '<a href="' + link + '">' + data + '</a>';

where is link defined? Maybe the error you are getting is that link is undefined. What exactly do you want the URL to look like?

Kevin

EN99

Just thinking; could it be perhaps I'm missing the "select" plugin?
Adds row, column and cell selection abilities to a table.v2.0.3

Edit:
Just notice your reply. I'm not sure; I can't seem to get to find the error in the console strangely enough. But I'm using nested listst in the ajax response, so I guess that might be a reason? I'm currently not specifying all columns; just the one I want to change. I will add a loop to include all and try it like that.

kthorngren

Just thinking; could it be perhaps I'm missing the "select" plugin?

Are you referring to trying to add the link to the cell?

Please provide more details of what exactly isn't working and any errors you are getting.

Kevin

EN99

I think the problem is how I parse the data server-side, as a list of lists:
"data": [[1, "5037082", "XYZ"],

Is it neccesary to convert them to dictionaries so it includes the column names in each record?

(unfortunately for some reason, even after reset, my Chrome Inspection window is doing strange and not allowing me to inspect errors, and I can't reach this machine from my other laptop)

kthorngren

You can have a list of lists or a list of dictionaries. In Javascript terms list = array and dictionary = object. Objects are easier to use as they aren't order dependent. If the array order changes you need to update the code to use the proper index but with objects no code changes are needed.

Use columnDefs to define options for one column. You above code would look something like this:

     columnDefs: [
       {
         targets: 0,  // change 0 to the column index
         render: function (data) {
                     link = 'https://editor.datatables.net';
                     return '<a href="' + link + '">' + data + '</a>';
                 }
                 return data;
         }
     ]

Kevin

EN99

Sorry for the confusion, but I finally understood your references .

I changed the output of the API, to get dictionaries:
data = mod_class.objects.filter(filter_q).order_by(order).values(*columns)[start:limit]

Then in the template I currently have it as follows (will change it with an if on ID, but just for info for whoever might encure the same issue):

        <script>
          DataTable.type('num', 'detect', () => false);
          DataTable.type('num-fmt', 'detect', () => false);
          DataTable.type('html-num', 'detect', () => false);
          DataTable.type('html-num-fmt', 'detect', () => false);
          new DataTable('#data_table', {
            ajax: '../api/crud/{{model}}',
            processing: true,
            serverSide: true,
            pageLength: 50,
        
            columns: [
                {% for i in columns %}
                {
                    data: '{{ i }}',
                    render: function (data, type, row, meta) {
                             link = row['id'];
                             return '<a href="' + link + '">' + data + '</a>';
                         }       
                },
                {% endfor %}
            ],
        
        
        });
        </script>

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

EN99

Thanks Kevin. From your activity on this website I assume you're the person behind this library? Really impressive library/functionality and fun to tinker with!

One final question; is it possible to change the position of a column to last?

kthorngren

From your activity on this website I assume you're the person behind this library?

No, I've used it for years. @allan is the developer.

is it possible to change the position of a column to last?

Yes but I'm not sure of the context around your question. If using objects you can arrange the columns.data in any order you wish. You can also use columns.title to create the header. But you are using a template to create the columns so you would need to set the Python columns variable in the order you wish.

There is the ColReorder extension that will allow users to change the column order. The order can also be changed via API calls.

Kevin

allan

I might be the developer, but it is safe to say that the DataTables community wouldn't be the warm welcoming place that it is without Kevin! He has helped countless people over the years!

Allan