Search for a string instead of '1' in a Boolean Column

redman85

Hi,
Like the title already says. Is there a way, that you make a column (which has boolean values in it) searchable for certain strings (like "something") instead of 1 or true?
I have a DataTable and one of the columns shall be searchable aswell, but not by it's bool value, but by a string that i determine?

Thanks in advance

colin

Hi @redman85 ,

You can use columns.render for that - look at the filter property.

Cheers,

Colin

redman85

Thx @Colin,
that might be the option i was looking for, but it does not work yet.
Here is how i tried to implement it.

$(document).ready( function () {
    $('#datatable').DataTable({
        autoWidth: false,
        processing: true,
        serverSide: true,
        ajax: '{!! route('activities.staff',$activity->id) !!}',
        headers: {'X-CSRF-TOKEN': '{{ csrf_token() }}'},
        columns: [
            { data: 'lastname', name: 'lastname' },
            { data: 'firstname', name: 'firstname' },
            { data: 'id', name: 'id'},
            { data: 'grabungsleiter', name: 'grabungsleiter',
              render:{
                filter: 'grabungsleiter', // <--- this should be the string which users shall search for
              }},             
        ]       
    });
});

Maybe you can see what i have done wrong here?

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

allan

That code will go looking for nested a property called grabungsleiter.grabungsleiter which I doubt you have in your data object.

You probably want something like:

{
  data: 'grabungsleiter',
  render: function (data, type, row) {
    if (type === 'display' || type === 'filter' ) {
      return data ? 'Yes' : 'No';
    }
    return data;
  }
}

Allan

redman85

Hi and thanks @allan,
i don't know if you got me right. In my DataTable there are some columns receiving a bool value. In the end, instead of showing 1, 0 or null, i want to display the specific name of that column, like in this case 'grabungsleiter' if the bool is true and ' ' (empty string) if the bool is false.
I already found ways to do that, but my problem is, that i want to search e.g. for 'grabungsleiter' in the search-bar,so that ajax searches for all records in the database, where 'grabungsleiter' is true.
I don't know if that is even possible.
I hope you can understand what i am looking for.

colin

As we said, columns.render is the place for this. The filter string that's returned in Allan's example above is what will be used to search for that field.

redman85

I don't know how to use columns.render properly. I've tried diverse things now, but nothing worked. This is how i try to combine both of your answers.

$(document).ready( function () {
$('#datatable').DataTable({
        autoWidth: false,
        processing: true,
        serverSide: true,
        ajax: '{!! route('activities.staff',$activity->id) !!}',
        headers: {'X-CSRF-TOKEN': '{{ csrf_token() }}'},
        columns: [
            { data: 'lastname', name: 'lastname' },
            { data: 'firstname', name: 'firstname' },
            { data: 'grabungsleiter', name: 'grabungsleiter',
              render: function (data, type, row) {
                    if (type === 'display' || type === 'filter' ) {
                      return data ? 'Grabungsleiter' : '';
                    }
                    return data;
                  },
            filter: 'Grabungsleiter'}
        ]       
    });
});

tangerine

if (type === 'display' || type === 'filter' )

That's not what you want.
You have to adapt the render function "if" condition to suit your purpose. You need to be testing data for whatever is being supplied - yes/no or true/false or 1/0, whichever you are using.

kthorngren

Looks like you code works here:
http://live.datatables.net/ravafeke/1/edit

Without seeing your specific data its hard to say what the problem is. Start by using console.log statements to debug your render function. Between lines 13 and 14 of your above code snippet start with console.log( data );, for example:

              render: function (data, type, row) {
                    if (type === 'display' || type === 'filter' ) {
                      console.log( data );
                      return data ? 'Grabungsleiter' : '';
                    }
                    return data;
                  },

You might need to update the if portion of the return statement: return data ? 'Grabungsleiter' : '';.

If this doesn't help then please provide a link to your page or update my test case to replicate the issue.

Kevin

redman85

Hi and thx,
somehow the code really works , but only if i take hardcoded data like in your link. But there should not be a difference between the hardcoded and the data coming from my database. There are only three values returned from the database:
1. a string for lastname
2. a string for firstname
3. 1/0 or null for grabungsleiter

Or does it make a difference if the data is coming from a database?

kthorngren

I'm using Javascript data so it should be the same. It could be that the 1 and 0 are strings not numbers. This would cause the if statement to not behave as you want. You would need to change it to something like this: return data == 1 ? 'Grabungsleiter' : '';.

Kevin

redman85

It now does work, when i change the ajax serverSide: true to serverSide: false.
So maybe it does not wor,k when i want server side processing or else teach me better
So the whole data must be loaded at once first in order to make the searching work, if i got that right.

I thought ajax would convert the search string 'grabungsleiter' (or similar parts of it) from string back to a boolean value and then requests something like
"SELECT * FROM users WHERE grabungsleiter=1", but that is probably not the case.

Anyways thx to all helpers,
tell me if i am wrong and serverside processing should also work please.

Regards

Olav