Use row-selector on hidden column with ID numbers from database

Use row-selector on hidden column with ID numbers from database

crash85crash85 Posts: 48Questions: 5Answers: 0

I have a table that is generated through an ajax call. In my database I have a column of ID numbers, but I don't display them in the table because they aren't useful to the user...but I want to use them for being able to select/deselect certain rows from a button.

fiddle: http://jsfiddle.net/crashvector/q6af23y9/

The ID column visibility is set to false

{
        title: 'ID',
        'className': 'dt-left',
        'visible': false,
        data: 'ID'
      },

I was hoping to be able to use https://datatables.net/reference/type/row-selector by passing an array of ID numbers to it in the example.

{
      text: 'Select Default Library 1',
      action: function (e, dt, node, conf) {
        alert('This button will automatically check all rows that match predefined list 1 using the hidden ID column. (IE ID 4, 7, 8)');

      }
    }

I'm just not sure how to trigger the row selection for that specific column via the button in a way that allows the on.select/deselect functions to work.

Answers

  • crash85crash85 Posts: 48Questions: 5Answers: 0
    edited April 2019

    Would I need to do something like:

    rowId: ID
    

    ?

  • crash85crash85 Posts: 48Questions: 5Answers: 0
    edited April 2019

    EDIT: Updated Fiddle here: http://jsfiddle.net/crashvector/q6af23y9/5/

    I added a row ID line and changed "test" to "ID" in the block below.

    data: srcData,
        rowId: 'ID',
        columns: [
    
    dataTable.on('select', function (e, dt, type) {
        if (type === 'row') {
          var row = dataTable.row(dt);
          $(row.node()).find('td:eq(4)').html(
            '<select id="test">' + category.reduce((options, item) =>
              options += `<option value="${item}" ${
                item == row.data().Category ? 'selected' : ''}>${
                item}</option>`, '') + '</select>'
          );
          toggleDataAndDraw(row, type, '1');
        }
        
        $('#test').on('change', function () {
        var optionSelected = $("option:selected", this);
        var valueSelected = this.value;
        var row = $(this).closest('tr');
    
    var cell = dataTable.cell(row, 6);
    cell.data(valueSelected)
    
    })
    
  • crash85crash85 Posts: 48Questions: 5Answers: 0

    UPDATE: I used a function to create a unique id after defining the columns, and am attempting to select defined rows via a button.

     rowId: function(a) {
        return 'sampleid_' + a.ID;
      },
    

    I am trying to do some work here on the rowid and selecting via a button: http://live.datatables.net/rixufida/1/edit?js,console,output

    The console log changes dramatically when changing from "dataTable.row(rowSelector)" to "dataTable.rows(rowSelector)" Is that expected?

     {
          text: 'Select Default Library 1',
          action: function (e, dt, node, conf) {
         var rowSelector = [ '#sampleid_10', '#sampleid_2', '#sampleid_401'  ];
         var row1 = dataTable.rows(rowSelector);
         var data1 = row1.data();
         console.log(data1);
         alert('This button will automatically check all rows that match predefined list 1 using the hidden ID column.'+ rowtest);
          }
        },
    

    The goal here is to trigger the row select function for the rowSelector array.

    Question: Will adding 200 row id's to the rowSelector array cause performance issues?

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin
    Answer ✓

    I've replied to some of your questions here: https://datatables.net/forums/discussion/55686/have-generated-dropdown-with-original-value-as-default-and-update-on-change#latest .

    Question: Will adding 200 row id's to the rowSelector array cause performance issues?

    It shouldn't no.

    Allan

  • crash85crash85 Posts: 48Questions: 5Answers: 0

    Thanks for the answers! I'll keep my follow ups in this thread.

    I have been trying different ways to get the selector to work the way I think it should, but keep getting errors. I think it might be something to do with the timing of the table loading and the function being defined? When I click the default button, i get errors for Category not being defined.The rows still get toggled to selected, but it doesn't update their checked-unchecked status or redraw the table as expected.

    I've attempted to add commenting to the script to describe the desired functions/problems

    http://live.datatables.net/rixufida/8/edit

  • tangerinetangerine Posts: 3,342Questions: 35Answers: 394

    Your example is reporting 44 errors.

  • crash85crash85 Posts: 48Questions: 5Answers: 0

    Yea, I'm not even sure where to start on that. When I start chasing down what it is telling me in an error, it doesn't make sense.

  • crash85crash85 Posts: 48Questions: 5Answers: 0

    I think all of these behaviors need to be separate instead of nested together?

    dataTable.on('select'
    $('#dropdown').on('change'
    dataTable.on('deselect',

    It seems to work decently if you select 1 row at a time, and change it before moving on. But that's not how it will be used.

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736
    edited April 2019 Answer ✓

    I'm seeing a few of these errors:

    Uncaught ReferenceError: jQuery is not defined

    It looks like you have duplicated JS and CSS files and are loading some JS files before jQuery.

    In the other thread Allan suggested appending the change event directly to the select and remove the ID's. One problem is you are ending up with duplicate ID's. Her suggested something like this:

    .html(
      $('<select/')
        .on('change', function () { ... } )
        .append( ... options ... )
    );
    

    I updated your example with this suggestion plus fixed the code to get the value from the select. It seems to be working better now. I used the download builder to build the appropriate files for DT, Select and Buttons.

    You can test by selecting multiple rows and changing the options to something different for each row. Then you should be able to search and see the updated rows.
    http://live.datatables.net/zaqubaxa/1/edit

    Kevin

  • crash85crash85 Posts: 48Questions: 5Answers: 0

    Oh wow, so much better! Thank you. I had missed what Allan had suggested when I got sidetracked on making the default button work. ( And I honestly wouldn't have known how to implement it.)

    Multiple follow ups:

    1) It seems that the deselect toggleDataAndDraw isn't resetting the check-uncheck state to 0. The select is changing it from 0 to 1 though. That function seemed pretty straightforward. The dropdown goes away, but the 0 doesn't get set.

    2) If you select something (or multiple somethings) and deselect them without changing the dropdown value, it sets an additional 1 in the checkbox column when it deselects the row. Selecting an option (other than the original option) from the dropdown and then deselecting the row results in the additional 1 not being written to the table. (I'm not sure what code is writing a 1 on deselect.)

    3) The defaultselect1 function selects the rows by #id, but doesn't trigger the checkbox/check-uncheck value/or the table draw. BUT anything that is selected by the default button, shows up when you use the download button. So the table is seeing the selected rows, just not performing the mechanics as if the row was physically clicked on. I was looking at other things like trigger, or change that maybe would check the checkbox for that row? Would that trigger the rest of the mechanics?

  • crash85crash85 Posts: 48Questions: 5Answers: 0

    Kevin, my select deselct problem is still what you mentioned in the other post right?

    "Another problem is you should use the Select Extension API's (row().select() and row().deselect()) to toggle the select state instead of toggling the selected class. By toggling the class the deselect event is never called."

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736
    edited April 2019

    That's probably correct. I haven't had a chance to look at your questions but I would recommend using the select APIs for this.

    Give it a shot and let us know.

    Kevin

  • crash85crash85 Posts: 48Questions: 5Answers: 0

    Forgive my complete lack of knowledge in jquery, but are you saying that:

    dataTable.on('select', function (e, dt, type) {
    

    and

    dataTable.on('deselect', function (e, dt, type) {
    

    need to be changed to something different? I'm trying to understand what the table is triggering when you click on a row. (And how to make it work.)

    My default button is using the correct API right?

    dataTable.rows(rowSelector1).select();
    
  • crash85crash85 Posts: 48Questions: 5Answers: 0

    Should I be looking at something like:

    $('#samples').on('click', '#select_all', function() {
        if ($('#select_all:checked').val() === 'on') {
          dataTable.rows().select();
        }
        else {
          dataTable.rows().deselect();
        }
    
  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736
    Answer ✓

    I had a chance to look at the example again and found/fixed a couple issues.

    1 and 2 are due to writeCell function. Instead of removing and adding the row (which I think is causing your 0 and 1 data to become corrupted) you should use row().data() to update the data. Looks like this currentRow.data(rowData).draw();.

    Number 3 needs some changes due to the number of rows that can be selected, via the button. I found that instead of using just dt to get the row indexes it seems to work better to use dt[0]. dt[0] contains an array of indexes whether you select one row or multiple. I added this var dt_indexes = dt[0]. Since its an array and you can have multiple you need a loop to process each selected row.

    Here is the updated example:
    http://live.datatables.net/vifixequ/1/edit

    Additionally I don't think you want to call writeCell() in the deselect event. This causes console errors.

    HTH,

    Kevin

  • crash85crash85 Posts: 48Questions: 5Answers: 0

    Thats definitely the issue with the 0/1 data. I have a close friend who is also helping with this in his spare time, and he came up with a similar solution last night as well:

    var writeCell = dropdown => {
        var currentRow = dataTable.row(dropdown.closest('tr'));
        var rowData = currentRow.data();
        rowData.Category = dropdown.val();
        $(currentRow.node()).find('td:eq(6)').html(
          currentRow.data().Category
        );
        currentRow.draw();
      };
    

    I've combined both of your codes in the .on select block. It doesn't seem to have effected the functionality of either version. (But i'm likely wrong in that observation! -I'm guessing that each of you solved it in their own way and combining the code in any way doesn't add any value in one direction or the other.)

    I've changed some basic table formatting, added more #id's to the select button, and added a deselect button. (I wasn't successful in finding an example that would allow me to use the button as a select/deselect for the list of #id's. -That would be ideal honestly.)

    I added the loop into the deselect portion as well to facilitate deselecting multiple rows.

    http://live.datatables.net/kaqumame/1/edit?js,output

    Any thoughts on why the default select button will only move a few selected samples to the top of the list? If you click multiple (6 or so) times, you can get 6 samples to the top. (I've found after 6 or so clicks you can't get anymore to draw at the top, regardless of how many times you click.)
    It selects the remaining rows visually, but does not update their checked-unchecked status from 0 to 1.

    The deselect all is finicky. Sometimes it deselects everything, including updating the checked/unchecked status. Other times it takes multiple clicks to accomplish. And I'm not sure what to do about making the dropdown revert back to the original table value. (or the value that was last selected via the dropdown if the original table value is lost.)

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736
    edited April 2019 Answer ✓

    The console was showing this when using the Select Default button:

    Exiting potential infinite loop at line 201. To disable loop protection: add "// noprotect" to your code

    I removed dataTable.draw(); from toggleDataAndDraw and placed it at the end of the select event. Did the same for the deselect event. You don't want to call draw for each update in a loop, just once after the loop.

    The same infinite loop message appeared when deselecting all. You will notice that it is running through 414 rows of data when deselecting all. If I comment out toggleDataAndDraw(row, type, 0); in the deselect event then the error doesn't occur. So I changed from a for loop to using jQuery each() and that eliminates the error. You may want to change the for loop in the select event.

    In your deselctAll function you will want to only process the rows that are selected by using the selector-modifier for selected. I have it commented now so you can see the what happens when deselecting 414 rows with $.each(). Uncomment it and remove the second line.

      deselectAll = function (dt){
                        //dataTable.rows({selected:true}).deselect()
                        dataTable.rows().deselect() .  // remove this
      };
    

    Updated example:
    http://live.datatables.net/mofubuya/1/edit

    Kevin

  • crash85crash85 Posts: 48Questions: 5Answers: 0

    Ah, cool. Now instead of toggling 414 times, it only does as many as needed.

    Removing the draw from the toggleDataAndDraw causes the check-uncheck value to no longer update. Is there a way to have this value be "toggled"? Is that column still needed at this point?

    I attempted to use

    $.each( dt_indexes, function ( index ) {
            var row = dataTable.row( index );
    

    in the select. That broke the select functionality to where it is updating the check-uncheck but not moving the row data. (But the deselect function updates the check-uncheck like it supposed to.)

    Would it make more sense to move the

    dataTable.cell({
            row: row.index(),
            column: 0,
            visible: false
          }).data(dataVal);
    

    into the select and deselect blocks? I figured it would be the same result, but it seems that the problem comes from having that function loop and draw? But without the draw, it isn't updating the check-uncheck.

    I've updated the buttons and attempted what I mentioned above...which broke different things depending on the version.

    This one uses the for loop in the select and doesn't change the check-uncheck on deselect or make the dropdown box go away.
    http://live.datatables.net/mofubuya/2/edit

    This one uses the .each function in the select and doesn't redraw the table correctly when things are selected, but the deselect updates the check-uncheck. - Manually clicking rows in this one does not trigger the check-uncheck, or populate the category dropdown.
    http://live.datatables.net/buzugobi/1/edit

    It feels like it's so close, yet completely out of my grasp. Thank you guys again for all the work you've been putting into this. I still haven't even grasped how to read this language very well.

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736
    Answer ✓

    Moving the dataTable.draw(); isn't the problem. The problem is with the index I used in the $.each() loop. The index is the loop number not the row index. When a single row is selected then deselected the index was always 0. I changed this:

    var row = dataTable.row( index );
    

    To this:

    var row = dataTable.row( dt_indexes[index] );
    

    Changed the for loop in the select event to a $.each() loop. Do some more testing to see what else is broken :smile:

    http://live.datatables.net/zijenili/1/edit

    I also changed to this in deselectAll:

    dataTable.rows({selected:true}).deselect()
    

    Kevin

  • crash85crash85 Posts: 48Questions: 5Answers: 0

    Yes!!! So close!

    The only thing not working is the hiding of the dropodown that is generated on the select.

    My current train of thought on how that could be fixed? Am I on the right trail?

    //Somewhere in here needs to be a var that saves the current category for each row to be deselected?
            //that would then be set for those columns/rows. But would also need to function for single manual select/deselects
            //(Or is there an easier way by just hiding the div?)
            //var valuecat = 
            //var cell = dataTable.cell(row, 6);
            //cell.data(valuecat);
    

    http://live.datatables.net/zijenili/2/edit

  • crash85crash85 Posts: 48Questions: 5Answers: 0

    Or could the writeCell function be added back to the deselect in order to write the dropdowns current value to the table on deselect?

  • crash85crash85 Posts: 48Questions: 5Answers: 0
    edited April 2019

    I got all excited when I added the writeCell and it mostly worked...then realized the exact same line was commented out below. Sigh

    http://live.datatables.net/rohedupo/1/edit

    Everything works as expected EXCEPT:

    1) Manually select a row
    2) choose a new Category from the dropdown
    3) deselect manually or via deselect all button

    The check-uncheck value doesn't update. (which causes the deselected row not to sort back to it's original position.)

    The error that shows up makes me think that this could be used again in a different fashion?

    if (!row.length) {
            return;
          }
    
  • crash85crash85 Posts: 48Questions: 5Answers: 0

    Bonus question: Can you trigger a table sort when you use row.select?

     dataTable.rows(rowSelector1).select().order([6]);
    

    That doesn't work, but I'm guessing there is something close to that that will?
    Currently the first click of a default button orders the selected rows by #id. The second click will sort those rows with the settings defined in table initialization.

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736
    edited April 2019 Answer ✓

    Glad you are getting most of it sorted out.

    Can you trigger a table sort when you use row.select?

    Use the draw() API to have Datatables sort the table, for example:
    dataTable.draw();

    Or are you asking how to tell Datatables to sort column 6? You would use:
    dataTable.order([6, 'asc']).draw();

    You can probably chain it like you have:
    dataTable.rows(rowSelector1).select().order([6, 'asc']).draw();

    Take a look at the order() examples.

    Kevin

  • crash85crash85 Posts: 48Questions: 5Answers: 0

    Yep, that's perfect. I was wanting to have the table sort itself after doing the selection. Used this:

    dataTable.rows(rowSelector1).select().order([5, 'asc'],[6, 'asc'],[4, 'asc']).draw();
    

    Still not sure what to do about the deselect behavior. I think it needs to be something like:

    if Category isn't defined: Category = row.column [6]
    writeCell
    
    else writeCell
    

    But not sure how to write that in jquery and have it work with everything already there.

    Basically something that can see if the dropdown is present. If it is, run writeCell which stores the dropdown value to the table OR if it isn;t present, use the current cell value to feed the writeCell function.

  • crash85crash85 Posts: 48Questions: 5Answers: 0

    Got it!

    if($(row.node()).find('select').length){
              writeCell($(row.node()).find('select'));
            }
    
This discussion has been closed.