Searching on columns with "data" = null

pencerw

I have a Datatables table that's being fed Django/postgres data via Ajax, and in a number of places I need column.data to be null so that I can hyperlink the cell to a class-based detail view for that object. For example:

<div class="table-responsive">
        <table id="reportrecord_table" class="hover" data-server-side="true" data-ajax="/api/reportrecordset/?format=datatables">
            <thead>
                <tr>
                    <th>ID</th>
                    <th>Customer</th>
                    <th>Listed supplier name</th>
                    <th>Canonical supplier name</th>
                    <th>Listed supplier address</th>
                    <th>Publication year</th>
                </tr>
            </thead>
            <script>
                $(document).ready( function () {
                        $('#reportrecord_table').DataTable( {
                            "processing": true,
                            "lengthMenu": [ [25, 100, -1], [25, 100, "All"] ],
                            "searching": true,
                            "columns": [
                                { 
                                    "data": "id",
                                    "width": "5%",
                                    "render": function ( data, type, row, meta ) {
                                        return '<a href="../reportrecord/'+data+'">'+data+'</a>';
                                    }
                                },
                                {
                                    "data": "customer",
                                    "searchable": false,
                                    "render": function ( data, type, row, meta ) {
                                        return '<a href="../company/'+data.id+'">'+data.name+'</a>';
                                    }
                                },
                                {
                                    "data": "supplier_name",
                                },
                                {
                                    "data": "supplier",
                                    "searchable": false,
                                    "render": function ( data, type, row, meta ) {
                                        return '<a href="../company/'+data.id+'">'+data.name+'</a>';
                                    }
                                },
                                {
                                    "data": "supplier_address",
                                },
                                {
                                    "data": "report_publication_year",
                                    "width": "10%"
                                }
                            ]
                        });
                } );
            </script>
        </table>
    </div>

On both the object ID field and the ForeignKey fields, I'm hyperlinking the display text to point to a detail view for the relevant object. The problem is that if I don't specify "searchable": false,, and then the user inputs text into the search bar, I get:

DataTables warning: table id=reportrecord_table - Ajax error. For more information about this error, please see http://datatables.net/tn/7
...and a traceback that ends in:
11:01:44 PM web.1 | raise FieldError('Related Field got invalid lookup: {}'.format(lookup_name))
11:01:44 PM web.1 | django.core.exceptions.FieldError: Related Field got invalid lookup: icontains
I would like these fields to be searchable - is there a better way to do this?

allan

With server-side processing enabled, if you mark a column as data: null or as an object, then yes, it is not searchable since the server has no idea what column in the database it needs to search on.

Change "data": "supplier", to be "data": "supplier.name", and use row.supplier.name / row.supplier.id in the renderer to make it work.

The other option (if you have less than tens of thousands of rows) is to disable server-side processing.

Allan

pencerw

Ah, I didn't realize that I could reference the row that way. Thanks so much @allan !