Searching a column no longer works

ButchNZ

I say no longer as it was once. I changed the data in the filter from a string label to the int id and now I can't get it to work.

I also had column.cache('search').unique();. So firstly, is there a cache I can clear? I have deleted/clear site data in Chrome.

If not, a slimmed down table config:

    ajax: route,
    columns: [
        { data: 'status' },   // 7
    ],
    columnDefs: [
        {
            targets: 7,
            className: 'text-center',
            render: function (data, type, row)
            {
                if (type == 'filter')
                {
                    console.log(data.filter);
                    return data.filter;
                }
                else if (type == 'sort')
                {
                    return data.sort;
                }

                return data.display;
            }
        }
    },
    initComplete: function ()
    {
       this.api()
      .columns(7)
      .every(function ()
      {
           var column = this;

          $('#StatusFilter').on('change', function ()
          {
             var val = $.fn.dataTable.util.escapeRegex($(this).val());
            
            console.log('onchange, filter id is: '+val);

            column.search(val, true, false).draw();
        });
    });

    }

The console.log(data.filter) there is outputting the id numbers I'm hoping to search on.

If I change this line:
column.search(val, true, false).draw();
to
column.search(4, true, false).draw();

It still shows no records even though there are a lot of "4".

An example output:

I can't easily provide a test case but can provide a link behind a login.

kthorngren

Searching a numeric column with a numeric value works in this test case:
https://live.datatables.net/pucorome/1/edit

Changing the test case to search the column using a string also works:
https://live.datatables.net/gehiyuka/1/edit

Just to show orthogonal data works I built this test case:
https://live.datatables.net/dodobuge/72/edit

There is nothing obvious in your code above ot indicate an error. Please provide a link to your page or a test case replicating the issue so we can help debug.
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

Kevin

ButchNZ

That was quick Kevin! Have sent you a message with a link

ButchNZ

This seems to have something to do with types. I'm not understanding it.

To test I changed the columns render function to

render: function (data, type, row)
{
    if (type == 'filter')
    {
        console.log(typeof data.filter); // number

        return (data.filter ? data.filter.toString() : '');
    }
    else if (type == 'sort')
    {
        return data.sort;
    }

    return data.display;
}

And that works. I don't know why it's needed and it's not for sort.

Just to check I wasn't somehow sending it garbage I also forced it to an JS number with:

 return (data.filter ? Number(data.filter) : '');

Doesn't find results.

allan

Could you PM me the link that shows it not working as well please?

Allan

ButchNZ

No sweat - have done @allan

allan

Interesting on this one, it took a fair bit of digging. It is because the column is being detected as an HTML data type, but the filtering data is actually a "Number". The result is that it trips over this condition, resulting in the returned data for the search being an empty string.

Three ways to work around this:

1. Update to DataTables 2 which shouldn't have this issue
2. Change your number data type to be a string
3. Change the rendering function to return the sort property for type === 'type' (just add it to line 9 in your rendering function above as an OR condition).

Option 3 is probably the easiest to implement!

Allan

ButchNZ

Interesting - I really appreciate you looking into that thank you!

You'll see in a comment above I had fixed it locally with option 2. Forcing the data to a js string with .toString().

I wasn't aware 2.0 was out - I'll check that out.

Are the values for type in the render function listed somewhere? More to the point is there a different type for a column search vs a text search in the input box?

The column searches are using the data.filter value for the column data which is the id value = great.
That does break the search input box as it seems to also search on the same data-filter value which is the id not the readable column content.

There isn't say a type = global-search where I can tell it to use data.stringSearchVal?

If that makes sense I should probably make a new question.

allan

Are the values for type in the render function listed somewhere?

Yes here. It is actually possible to extend that list though, e.g. for exporting data you can specify an orthogonal option which can be set to export or whatever. Generally it is best to handle explicit the types you know will be passed in, and then return the raw data or display data, for anything unknown.

There isn't say a type = global-search where I can tell it to use data.stringSearchVal?

There isn't I'm afraid. The global search is literally just a concatenation of the column search strings. DataTables 2 does have support for a function as a search term though, which can increase flexibility. I've still to write a release blog post for DT2... This feature will be one that is detailed there.

Allan

ButchNZ

Cool thanks!

I can't figure out why DT is detecting the column as html. The setting of filter and sort is the same.

The server side code for preparing a row is:

return [
    'id' => $row->id,
    'uuid' => $row->uuid,
    'name' => $row->name,
    'property_name' => [
        'display' => $row->property?->name ?? '',
        'filter' => $row->property?->id  
    ],
    'area_name' => $row->property_area->name ?? '',
    'contractor_name' => $row->contractor->company_name ?? '',
    'priority' => [
        'display' => Format::priority($row->priority) ?? '',
        'filter' => $row->priority?->id,
        'sort' => $row->priority?->order
    ],
    'status' => [
        'display' => Format::status($row->status) ?? '',
        'filter' => $row->status?->id,
        'sort' => $row->status?->order
    ],
    'date_added' => [
        'display' => $row->date_added?->format('d/m/Y'),
        'sort' => $row->date_added?->format('Ymd')
    ]
];

Ah well, I changed to option 3, setting type to sort and that works. Must be less for the client to do than setting toString() every row.

I wonder if I did myself more harm than good migrating from the original strings I had to the id's.
I assumed id's would be more performant and save some hassle like when a value is won't with the apostrophe.

I'll keep an eye out for the blog post. Ideally there'd be a way to either tell DT to use another data operation for string searches (filterString, say) or take over the text/type search box. The latter would seem hard to maintain.

Thanks again for your help

allan

It was seeing it as an html type due to return data.display; which contained HTML data. With the return of a number when type === 'type' then it should see it as numeric.

Ideally there'd be a way to either tell DT to use another data operation for string searches (filterString, say) or take over the text/type search box

You can, with DT2, fairly easily create a feature plug-in with your own input element and then use a custom search function for search() to provide complete control over the search functionality.

Allan