Select inputs on a individual column with joined data

cha59

I'm using the code from the example "Individual column searching (select inputs)" on a column showing joined data. But the drop down list shows only [object Object], [object Object], [object Object] when 3 different data is joined. I want it to sort out those lines where data1 is represented no matter if it's joined with data2 or data3 or alone. Is that possible?
Best regards Claus

initComplete: function () {
            this.api().columns(4).every( function () {
                var column = this;
                var select = $('<select><option value=""></option></select>')
                    .appendTo( $(column.header()).empty() )
                    .on( 'change', function () {
                        var val = $.fn.dataTable.util.escapeRegex(
                            $(this).val()
                        );
 
                        column
                            .search( val ? '^'+val+'$' : '', true, false )
                            .draw();
                    } );
 
                column.data().unique().sort().each( function ( d, j ) {
                    select.append( '<option value="'+d+'">'+d+'</option>' )
                } );
            } );
        },

kthorngren

Without seeing your data and how column(4) is configured its hard to say. The place I would start is to use a console.log statement between lines 16 and 17 to see what d is, for example:

                column.data().unique().sort().each( function ( d, j ) {
                    console.log( d );
                    select.append( '<option value="'+d+'">'+d+'</option>' )
                } );

This way you can see what d is and how you need to change the next link to set the option value and display what you want.

Kevin

cha59

Hi Kevin
Thank you for your response, but I'm sad to say, it didn't change anything. I'm still getting [object Object] in the drop down list.The data comes from MySQL that joins to tabels, so that one or several persons can have the same class.

{ data: "laerer", render: "[, ].init" },

kthorngren

What change did you make? What does the output look like?

My guess is learer is an object with one of the keys being init. init is an array. So you may want to do something like d.init.join().

Maybe you can put together a simple test case with an example of your data so it will be easier to help.
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

Kevin

cha59

Thanks for your help. I might have to set up a test case, but I've got another idea:

I think search might work instead of select, but I need the search only on two columns out of 55 columns. How can I change this code, so it will only set a header search in on column 2 and 3?

$(document).ready(function() {
    // Setup - add a text input to each header cell
    $('#example thead th').each( function () {
        var title = $(this).text();
        $(this).html( '<input type="text" placeholder="Search '+title+'" />' );
    } );
 
    // DataTable
    var table = $('#example').DataTable();
 
    // Apply the search
    table.columns().every( function () {
        var that = this;
 
        $( 'input', this.header() ).on( 'keyup change clear', function () {
            if ( that.search() !== this.value ) {
                that
                    .search( this.value )
                    .draw();
            }
        } );
    } );
} );

Thanks again for your answers.

Claus

kthorngren

You can use a class. See if this example helps:
http://live.datatables.net/vuruhosa/1/edit

Kevin

cha59

Hi Kevin
Thanks a lot for your proposal. It works fine on my datatables with one to one relations in the columns. However, it doesn't work on my datatable with many to one relations in two columns. When I refresh the datatable, I can see a glimps of my search felt, but it disappears again, when the datatable is fully loaded. I assume, that it's the column of many to one relations that hinders the search felt?
The many to one relations is loaded by ajax from a MySQL database. I'll try to make a test case, but it will take some time.
This is how one of the many to one relation data is created in the php file.

        ->join(
            Mjoin::inst('laerer')
                ->link('budget.id', 'budget_laerer.id_budget')
                ->link('laerer.id', 'budget_laerer.id_laerer')
                ->order('init asc')
                ->fields(
                    Field::inst('id')
                        ->options ( Options::inst()
                            ->table('laerer')
                            ->value('id')
                            ->label('init')
                        ),
                    Field::inst('init')
                )//lukker field laerer
        )//lukker join

cha59

Hi
To be more precise. Search of only one column works on my datatable with one to one relations. It doesn't work on my more complex datatable with many to one relations, fixed columns, inline editing, footer callback (tvice) and checkbox column. So the question is: Which one of these things hinders search of a specifik column on the more complex datatable?

Any ideas?

The code of my more complex datatable is 733 lines, so I can't show it here.

kthorngren

Which one of these things hinders search of a specifik column on the more complex datatable?

many to one relations

Are you using server side processing?

fixed columns

Its possible if the search input is within the fixed columns.

inline editing

Probably not.

footer callback (tvice)

Are your search inputs in the header or footer?

checkbox column

Are you trying to search in this column?

Which one of these things hinders search of a specifik column on the more complex datatable?

Its hard to say without more details. What happens when searching? Do you get any alert messages or console errors?

Can you post a link to your page so we can see what you have? If not please provide a simple test case with an example of your data. Maybe building up the test case will show you where the problem is.
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

Kevin

cha59

Okay, here is the link: https://plearn.it/tb/Editor/examples/cha_html/budget.html
The site contains 4 datatables.
"Budget" is the complex one with inline editing and the first 3 columns are fixed. On "Budget" a Search should appear on the column "Timer", which is not fixed. There is many to one relations and footer callback on most of the columns and another footer callback to sum these columns to the column "Lektioner". The column "Faktor" calculates the relation between column "Lektioner" and column "Timer".
On the datatable "Undervisere" there are dropdowns on each column. Works perfectly.
On the datatable "Faggrupper" there is search on only one column. Works perfectly.
Data comes from and are saved to a MySQL database. Honestly I don't know if it's serverside og clintside, but nowhere in my code does it say serverside.
What I want is on the datatable "Budget" to have a search felt on the column "Lærer" and the column "Hold". Both in the header, but it won't work for me, and as I have stated, I suspect many to one relations to be the hinder?

kthorngren

I may be missing it but I don't see the code you have to create the search input for the Budget table. I see the buildSelect() function for the Undervisere table and the text input code for the Faggrupper table but I'm not finding similar code for the Budget table. Please point us to the code.

Kevin

cha59

Hi Kevin
Thank you for your respons. This is the code for the search on datatable Budget. It's similart to the one on "Faggrupper":

$(document).ready(function() {
    // Setup - add a text input to each header cell
    $('#example thead .search-sort').each( function () {
        var title = $(this).text();
        $(this).html( '<input type="text" placeholder="Search '+title+'" />' );
    } );
 
    // DataTable
    var table = $('#example').DataTable();
 
    // Apply the search
    table.columns('.search-sort').every( function () {
        var that = this;
        $( 'input', this.header() ).on( 'keyup change', function () {
          if ( that.search() !== this.value ) {
                that
                    .search( this.value )
                    .draw();
            }
        } );
    } );
} );

cha59

The console prints this on "Budget". No message on "Faggrupper".

VM1374 dataTables.fixedColumns.min.js:32 Uncaught TypeError: Cannot read property 'Api' of undefined
    at VM1374 dataTables.fixedColumns.min.js:32
    at VM1374 dataTables.fixedColumns.min.js:5
    at VM1374 dataTables.fixedColumns.min.js:5

cha59

Hi Kevin
I'am a beginner to this, and to the use of Bin, but I have copied all 731 lines of code to "Budget" into Bin on the html page, so you can see the code. Don't really know how to seperate it into JS and CSS, and far less how to generate ajax data from MySQL for use in bin output. But here is the code:
http://live.datatables.net/upgrade/213/edit

kthorngren

Strange that I missed that code yesterday FixedColumns clones the header which is causing the problem for you. To place the search inputs with FixedColumns you will need to wait until Datatables initializes and place your code to insert the inputs into initComplete. You will need to use a different jQuery selector to select the "cloned" header. Here is a simple example:
http://live.datatables.net/qufuxuso/2/edit

One suggestion is to use two headers for column searching. One for sorting and one for searching. This will stop the table form sorting each time you click the search input. Here is an example of this using FixedColumns,
http://live.datatables.net/buziligu/1/edit

Note the use of orderCellsTop to move the sort elements to the top header. You would use this technique for text inputs too.

VM1374 dataTables.fixedColumns.min.js:32 Uncaught TypeError: Cannot read property 'Api' of undefined

I suspect the problem might be the JS load order. Try loading fixedColumns.js after datatable.js.

    <script type="text/javascript" language="javascript" src="https://cdn.datatables.net/fixedcolumns/3.2.6/js/dataTables.fixedColumns.min.js"></script>
    <script type="text/javascript" language="javascript" src="https://cdn.datatables.net/1.10.19/js/jquery.dataTables.min.js"></script>

I'am a beginner to this, and to the use of Bin, but I have copied all 731 lines of code to "Budget" into Bin

If you want to build a test case the best thing to do is reduce the code to what is needed to reproduce the issue. Similar to the example I just provided. Sometimes building up test cases like this you can find the problem yourself.

Kevin

cha59

Tanks for your help Kevin. It seems to me, that I haven't succeded in explaining my problem. And what is worse, I can't google any examples that makes a search felt for joined data (or many to one relations). But I will try explain again.

One line in my datatable "Budget" equals a course and can have one or several "laerer" in the column "laerer". And it can have one or several "hold" in the column "hold". So what I need is a search felt, that searches the "laerer" column for one "laerer" only. No matter if he is "laerer1", "laerer2" or "laerer3". And a search felt for the "hold" column, that searches the "hold" column for one "hold" only. No matter if it's "hold1", "hold2" or "hold3"

Datatables original search felt does the trick. It doesn't care, if it's "laerer1", "laerer2" or "laerer3". But I need this kind of search for column "laerer" only, and for column "hold" only. But none of all the examples you have found or I can find, will even show up as a search felt.

I have no trouble making search felts on the other datatables on my site. But datatable "Budget" is speciale because I have used PHP to join tables, so that I can have many "laerer" to one course, or many "hold" to one course.

So I think it must be possible because Datatables original search does the trick. I Just need two more original searches - one excluslivly on the column "laerer" and one excluslivly on the column "laerer". And it doesn't need to be in the header or footer. It can be next to the original DataTable search felt.

Does this make sense? Any ideas?

kthorngren

Is a previous comment you said:

On "Budget" a Search should appear on the column "Timer"

The search input is not appearing in the column. I provided information what you need to do to have that search input appear. And any others you want. I figured we would first start with getting the inputs to appear for you. Whether the data is from joined tables or not doesn't affect the ability to display the inputs.

Here is an example from the JSON fro one of your rows:

{
        "DT_RowId": "row_15",
        "budget": {
            "nr": "0",
            "timer": "78",
            "fag": "Studietur",
            "bem": "Bruxelles",
            "u31": null,
            ...... 
            "u24": null,
            "u25": null,
            "u26": null,
            "u27": null
        },
        "hold": [{
            "id": "11",
            "init": "ABE19"
        }, {
            "id": "3",
            "init": "ABM19"
        }],
        "laerer": [{
            "id": "85",
            "init": "CHA"
        }, {
            "id": "145",
            "init": "\u00d8L"
        }]
    }

For the laerer column you have this: { data: "laerer", render: "[, ].init" }

That column displays like this CHA, ØL. With a column search you should be able to search for either CHA or ØL. If you need something more lets discuss it but for now lets get the search inputs into the header or where ever you want them. Then we can fine tune the search.

Please update your test case by using initComplete as I described and have an example of above.

Kevin

cha59

Hi Kevin
Thank you for your answer and endurance. Problem solved with standard code. Right now I can't explain it, but I will look deeper into it and expect that the problem really existed from the keyboard and upwards.
Claus

kthorngren

Looks like you disabled FixedColumns and scrollX:

/*fixedColumns: {    
            leftColumns: 3// 3 første kolonner er fixed ved scroll
        },
    "scrollX": true,//tænder for scroll vandret i datatable*/

With these enabled the table header is cloned to facilitate the scrolling. To use FixedColumns and scrollX you need to wait until Datatables builds this structure before adding the inputs. To do this you need to move the code to initComplete and change the selector used to place the inputs in the correct place. This example does that:
http://live.datatables.net/qufuxuso/2/edit

HTH,
Kevin

cha59

Hi Kevin
Search felt works, but I can't get it to work with fixed columns. I've tried to combine your example http://live.datatables.net/qufuxuso/2/edit with my Budget, but somethings wrong in my code: http://live.datatables.net/upgrade/215/edit.
Console reports: jquery-3.3.1.js:3827 Uncaught TypeError: Cannot read property 'on' of undefined

Any ideas?

Claus

kthorngren

Your test case generates errors but not the one you mention. Its difficult to debug that much code without it running. Can you post a link to your test page?

Does the traceback for the error point to a line in your code? That would be the area to look at. You may need to use console.log statements to debug that are to see which variable is undefined.

Kevin

cha59

Hi Kevin
Here is the link: https://plearn.it/tb/Editor/examples/cha_html/budget2.html
Claus

kthorngren

Here is the full error:

Uncaught TypeError: Cannot read property 'on' of undefined
    at HTMLDocument.<anonymous> (editor-demo.js:39)
    at HTMLDocument.dispatch (jquery-3.3.1.js:5183)
    at HTMLDocument.elemData.handle (jquery-3.3.1.js:4991)
    at Object.trigger (jquery-3.3.1.js:8249)
    at HTMLTableElement.<anonymous> (jquery-3.3.1.js:8327)
    at Function.each (jquery-3.3.1.js:354)
    at jQuery.fn.init.each (jquery-3.3.1.js:189)
    at jQuery.fn.init.trigger (jquery-3.3.1.js:8326)
    at r (jquery.dataTables.min.js:76)
    at ua (jquery.dataTables.min.js:49)

The error is coming from editor-demo.js on line 39 which is the code:

        $(document)
            .off( 'init.dt.demoSSP' )
            .on( 'init.dt.demoSSP', function () {
                /* Show and syntax highlight submit and return data */
                editor.on('preSubmit', function (e, data) {

Doesn't look like you have the editor variable defined. Not sure what you are wanting to do with this code. The error seems to be stopping your script so its not fetching the data for Datatables. Maybe comment out include line in budget2.html:

<script type="text/javascript" language="javascript" src="../resources/editor-demo.js"></script>

Kevin

cha59

Hi Kevin
I have commented out the line in: https://plearn.it/tb/Editor/examples/cha_html/budget2.html

Getting:
DataTables warning: table id=example - Cannot reinitialise DataTable. For more information about this error, please see http://datatables.net/tn/3

and no data.

Claus

kthorngren

Did you follow the troubleshooting steps in the link provided in the error?

It will guide you to the problem. Somewhere you are trying to initialize Datatables a second time.

Kevin

cha59

Hi Kevin
I've tried retrive

$('#example').DataTable( { 
        retrieve: true,
        paging: false,

It make's the error message disappear, but gives me no data.

Claus

kthorngren

Why are you reinitializing the Datatable?

Looks like you are initializing it once then configuring Editor then initializing Datatables again. You probably are wanting to do this once. If you need combine the config options from the first into the second so you are initializing only once.

Kevin

cha59

Hi Kevin
I've made an example that is far more simpel. But something isn't right. I'am getting "$.fn.dataTable.Editor is not a constructor" and I can't figure out what is wrong. Tried to google for help, but that dit not help me, as far as I can see. The code is:

var editor; 

$(document).ready( function () {
    editor = new $.fn.dataTable.Editor( {//her styres editor men blandes sammen pga inline edit
      
        ajax: "../cha_php/chabudget.php",
        table: "#example",
        fields: [   {
                label: "Lærer",
                name: "laerer[].id",//koden for flere end ét hold
                type: "checkbox"
            },  {
                label: "Hold",
                name: "hold[].id",//koden for flere end ét hold
                type: "checkbox"
            },  {
                label: "Fag",
                name: "budget.fag"
            },    
        ],//lukker fields  
          

    } );//lukker editor, men ikke ready
    

    var table = $('#example').DataTable({
    fixedColumns: true,
    scrollX: true,
        initComplete: function () {
        var api = this.api();
            $('.search-sort', api.table().header()).each( function (i) {
                var title = $(this).text();
                $(this).html( '<input type="text" placeholder="Search '+title+'" />' );
            } );//lukker search
        }//lukker initComplete
    });//lukker var table

    // Apply the search
    table.columns('.search-sort').every( function () {
        var that = this;
        $( 'input', this.header() ).on( 'keyup change', function () {
            if ( that.search() !== this.value ) {
                that
                    .search( this.value )
                    .draw();
            }//lukker if
        } );//lukker $( 'input', this.header()
    } );//lukker table.columns

} );//lukker ready

The link is:
https://plearn.it/tb/Editor/examples/cha_html/budget4.html

Claus

cha59

Hi Kevin
Let me refrase my code. I'll be back.
Claus

colin

"$.fn.dataTable.Editor is not a constructor" and I can't figure out what is wrong.

You would see that if you weren't including the libraries for Editor.

I just tried your link, and it seems to be working fine now, so assuming all resolved.

cha59

Hi Kevin
Had to have help from my son this Christmas, but now it works fine using initComplete, as you suggested. He also cleaned up my code, which was needed, so hurra for having kids. Thanks for your patience and help. The case can be closed.
Claus