Searchable collumns

[daviddutch]

Hi,

I'm pretty new to django so hopefully everything I say will be correct.

If I add the primary key has a column text search will crash the app because pk__icontains is not a legal operation.

The simplest way I found to get around the problem is making the column not searchable. But this feature doesn't seem to be supported in eztables.

I've modified the global_search method the following way:

def global_search(self, queryset):
    '''Filter a queryset with global search'''
    search = self.dt_data['sSearch']
    if search:
        if self.dt_data['bRegex']:
            criterions = [Q(**{'%s__iregex' % field: search}) for field in self.get_db_fields() if
                          self.can_regex(field)]
            if len(criterions) > 0:
                search = reduce(or_, criterions)
                queryset = queryset.filter(search)
        else:
            search_fields = []
            fields = self.fields.values() if isinstance(self.fields, dict) else self.fields
            i = 0
            for field in fields:
                if self.dt_data['bSearchable_' + str(i)]:
                    if RE_FORMATTED.match(field):
                        search_fields.extend(RE_FORMATTED.findall(field))
                    else:
                        search_fields.append(field)
                i += 1

            for term in search.split():
                criterions = (Q(**{'%s__icontains' % field: term}) for field in search_fields)
                search = reduce(or_, criterions)
                queryset = queryset.filter(search)
    return queryset

Or is there another way of stoping the app from crashing with having the primary key (pk column) in the data?

Thank you

[noirbizarre]

I will need some more details to implement the test case and the fix.

How to you declare both the client-side (javascript initialization) and server-side (DatatablesView inherited class) views ? Do you a have a stacktrace ? Which Django version are you using (the behavior seems to differ between 1.4 and 1.5)

[daviddutch]

I use Django 1.5. To stop a column from being searchable from the client side you need to use the aoColumnDefs property like this:

    $('#browser-table').dataTable({
        "bPaginate": true,
        "sPaginationType": "bootstrap",
        "bProcessing": true,
        "bServerSide": true,
        "sAjaxSource": "{% url "contacts:data_table" %}",
        "aoColumnDefs": [ {
            "sClass": "nowrap",
            "bSearchable": false,
            "bSortable": false,
            "aTargets": [ -1 ]
        }]
    });

On the server side I don’t do anything special:

    class ContactsTableView(DatatablesView):
        model = Person
        fields = (
            'first_name',
            'last_name',
            'email',
            'pk',
        )

The problem is having the primary key in the column. I need it to generate the buttons links on the client side. Another nice fix would be to make it possible to search on the primary key as well without it crashing. So when I don’t have my fix the error when searching is:

TypeError at /contacts/data_table/
Related Field has invalid lookup: icontains

C:\Python27\lib\site-packages\eztables\views.py in get
        return self.process_dt_response(request.GET) ...

C:\Python27\lib\site-packages\eztables\views.py in process_dt_response
            self.object_list = self.get_queryset().values(*self.get_db_fields()) ...

C:\Python27\lib\site-packages\eztables\views.py in get_queryset
        qs = self.global_search(qs) ...

C:\Python27\lib\site-packages\eztables\views.py in global_search
                    queryset = queryset.filter(search) ...

You should find a way to use a different operator than icontains on primary keys.

[daviddutch]

Maybe as an example you can have a look at the php code here: http://datatables.net/development/server-side/php_mysql

They search only on the columns that are searchable.

[thepapermen]

The issue seems legitimate and is reproducible on Django 1.6, but not in a way that original poster supposed.

Take a look at this model:

    class FirstModel(models.Model):
        related_item = models.ForeignKey(SecondModel)

Let's say we've got two tables, both have "aoColumns": { "mData": 'related_item' } in their respective dataTable declaration:

    class BrokenFirstModelTableView(DatatablesView):
        """
        self.global_search(qs) will fail with
        TypeError: Related Field got invalid lookup: icontains
        """
        model = FirstModel
        fields = {
            'related_item': 'related_item',      
        }

    class WorkingFirstModelTableView(DatatablesView):
        """
        And this table will do just fine.
        """
        model = FirstModel
        fields = {
            'related_item': 'related_item__pk',      
        }

It looks like eztables.views.DatatablesView.global_search sets criterions criterions = (Q(**{'%s__icontains' % field: term}) for field in self.get_db_fields()) which are not compatible with related fields.

Whether this behavior is to be considered a documentation issue or a genuine bug, is a good question.

As about OP, my wild guess is that he's got a foreign key field somewhere in his Person model which is included in DatatablesView. The issue has nothing to do with having pk whatsoever because pk is not a Related Field, as in his stacktrace.

[noirbizarre]

I'm sorry, I didn't had time to look into it.

I will fix this issue this week-end.

[thepapermen]

Never mind, this issue doesn't heavily affect me since there is an easy workaround. Thanks for your awesome projects! I use eztables and django.js each and every day. These are incredibly robust, handy and well-made tools.

With best wishes and admiration, Ivan

[noirbizarre]

You're very welcome !!!