Defer loading "select" lists from Ajax?

Rusty Ballinger

(I feel like I'm posting things other people must have asked, and am not using the search feature effectively... sorry about that.)

Suppose I have a page for a specific user, and a DataTable on that page showing the list of organizations the user is related to. (Let's suppose also that the HTML is being generated dynamically.) When they use Editor to add a row to that table, I see two ways to provide their list of choices:

1. If the DataTable is getting its data from rows included in the HTML, I can include the list of organizations in the select's options.
2. If the DataTable is getting its data from Ajax, the instructions here show how to include the list of options in the data sent by the server.

In both of those approaches, though, I'm unable to present the user's page without getting the (possibly quite large) list of organizations from the database and sending it to the client, even though 95% of the time they're not going to need it, because they're not going to edit that table. Is there an easy way to defer the loading of that list until the user decides to edit--making an Ajax call at that point to get the contents of the list?

rf1234

This could help you: https://datatables.net/reference/option/deferRender

Rusty Ballinger

This could help you: https://datatables.net/reference/option/deferRender

It looks like that doesn't defer fetching the data, only the rendering of the data once it hits the client side.

My question may not have been clear: I'm talking about the option elements for a dropdown selection widget in Editor:

    editor = new $.fn.dataTable.Editor({
        fields: [
          {
            label: '...',
            name: '...',
            type: 'select',
            options: [
              //  THIS is the list of stuff I'd like to get from Ajax
              //  when the user starts editing.
            ]
          }, ...

allan

Is there an easy way to defer the loading of that list until the user decides to edit--making an Ajax call at that point to get the contents of the list?

Yes - listen for the initEdit event (or initCreate possibly depending on your table and data). In that make your Ajax call to get the data and then use the update() method of the select field type to set the options - e.g.

editor.on( 'initEdit', function () {
  $.ajax( {
    ...,
    success: function ( options ) {
      editor.field( 'myFieldName' ).update( options );
    }
  } );
} );

Regards,
Allan

rf1234

I think Allan's suggestion is really good. Mine is a lit different and it also details how you get to the options that you would like to show to the user by using typeahead which I believe is a really nice tool to use for this. While the user types she sees the options coming in and can very easily select from them.

I am using something similar outside Editor but you might be able to integrate it into Editor as well like this. First get rid of the "select" field type.

editor = new $.fn.dataTable.Editor({
    fields: [
      {
        label: '...',
        name: 'yourField',
        attr: {
                class: 'typeAheadField',
                placeholder: 'Please enter your search text.'
            }
        ]
      }, ...

I don't think you can set an id with "attr". But you can try {id: 'typeAheadField'}. If it works this would be better than using a class because you want to use typeahead with one specific field

$('.typeAheadField').typeahead( {
    source: function (query, process) {
        $.ajax({
            type: 'POST',
            url: 'actions.php?action=typeaheadField',
            async: true,
            //only one parameter to post is allowed by typeahead!!
            data: {
                    query: $('.typeAheadField').val()
                    },
            dataType: 'JSON',              
            success:    function(data) {                       
                        process(data);
            }
        });
    },
    items: 15,        //you would only retrieve up to 15 items
    minLength: 3   //you would only start querying after typing at least 3 letters
});

I have this in actions.php using my own database handler:

switch (filter_input(INPUT_GET, 'action')) {
    case "typeaheadField":
        $parm = [
            "queryString" => filter_input(INPUT_POST, 'query')            
        ];        
        echo json_encode ( typeaheadField($parm, $dbh) );
        break;
}

function typeaheadField then retrieves the options by searching the database with "like" where clauses and returns them to the front end. Eventually you pick one. And when you save the Editor instance this value will be saved to the database by Editor. You can have typeahead send a text and the id as a hidden part of the string to the front end. Then you use a validator that checks minimum length and whether the id is there to make sure that the user really picked one of the options instead of saving the entry without making a valid selection.

Here you can also limit the number of retrieved records to be in sync with the "items" limit at the front end:

$orgLike = "%" . $organization . "%";  
$dbh->query('SELECT name, regional_12  FROM gov
               WHERE name LIKE :name LIMIT 15');
$dbh->bind(':name', $orgLike);
$row = $dbh->resultset();
$x = 0;
foreach ($row as $key => $values) {    
     $result[$x] = $values['0'] . ", " . $values['1'];
     $x++;
}
return $result;

the value you return should be a normal array not an associative array. for that reason I didn't return the resultset straight away.

I think typeahead requires bootstrap 3 as well. But I am using it anyway.

<!-- Typeahead -->
    <script src="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-3-typeahead/4.0.2/bootstrap3-typeahead.min.js"></script>

Good luck!

allan

Oo - yes. Typehead or Select2, etc is a good option as well! Particularly if it is a large dataset.

Allan

Rusty Ballinger

Holy cow, those are both great answers. Thank you!