Autocomplete select

Autocomplete select

MickBMickB Posts: 103Questions: 25Answers: 2
edited March 2017 in Editor

Hi,

What is the easiest way to add an autocomplete select?

Has anyone got an example?

Using Select2?

Just found this:

https://editor.datatables.net/plug-ins/field-type/editor.autoComplete

The list of options needs to come from a MySQL table.

Thanks,

Mick

EDIT: Almost got this working now, just need to change the source to get the data from the DB.

Something like this maybe.

"opts": {
                                    "source": function( request, response ) {
                                      $.getJSON("/autocomplete/faults", {
                                        term: extractLast(request.term)
                                    }, response);
                                    }
                                }

From: https://jqueryui.com/autocomplete/#multiple-remote

This question has accepted answers - jump to:

Answers

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

    Good to hear you've been able to make progress with this. You are exactly right - the source option is the way to do this with the jQuery UI AutoComplete library. The code above should be making an Ajax request to /autocomplete/faults which needs to query the database and return the options available.

    That later part is not something that the Editor PHP or .NET libraries will do for you I'm afraid, but a simple SELECT statement should be all that is needed for it.

    Allan

  • MickBMickB Posts: 103Questions: 25Answers: 2

    Thanks Allan,

    PHP bit is easy:

    public function autocompleteFaults()
        {
            $term      = Input::get('term');
            $associate = array();
            $search    = DB::select(
                "
                SELECT description 
                from dev_selection.faults
                where match (description )
                against ('+{$term}*' IN BOOLEAN MODE)
                "
            );
    
            foreach ($search as $result) {
                $faults[] = $result->description;
    
            }
    
            return json_encode($faults);
    
        }
    
    
  • MickBMickB Posts: 103Questions: 25Answers: 2

    Now the tricky bit. :)

    I want this to work like a select. so I need to save the id.

    I am editing the fault_type_process_type table, which is used to associate process types with possible faults.

    fault_type_process_type 
    id, fault_id, process_type_id, stream_id, rank
    
    faults
    id, description
    

    The autocomplete displays the fault.description from my faults table.
    What I want to do is save the id to the fault_type_process_type table.

    My back-end looks like this:

    Editor::inst($db, 'fault_type_process_type')
                ->where("stream_id",$stream_id)
                ->where("process_type_id",$process_type_id)
                ->fields(
                    Field::inst("fault_type_process_type.id"),//always needed
                    Field::inst("fault_type_process_type.rank"),//always needed
    
    
                    //this is the select
                    Field::inst('fault_type_process_type.fault_id') //foreign key in this table
                    ->options('dev_selection.faults','id','description') //linked table, key (id) , value (text to display)
                    ->validator( 'Validate::dbValues' ),
                    //this is needed to display it joined table.field_name
                    Field::inst('dev_selection.faults.description')->validator('Validate::unique')
    
                )
                ->leftjoin('dev_selection.faults', 'fault_type_process_type.fault_id', '=', "dev_selection.faults.id")
    
                ->process($postData)
                ->json();
    
    

    So, I don't think this is correct, I am treating the autocomplete like a select.

    SQLSTATE[42S22]: Column not found: 1054 Unknown column 'dev_selection.faults.description' in 'field list'

    Mick

  • MickBMickB Posts: 103Questions: 25Answers: 2
    edited March 2017

    I'm thinking Select2?

    Just not sure how do that via Ajax?

    Mick

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

    Hi Mick,

    I think you can use either Select2 or jQuery UI - its really up to yourself which one you prefer working with. If you are using jQuery UI else where, then it makes sense to reuse it. If you aren't, then the smaller Select2 would probably be a better bet.

    Regarding the id - your auto complete PHP script is currently only returning the description column. You need it to return both the description (i.e. the label that the end user sees) and the id (i.e. the value that will be saved).

    So basically you want it to return something like:

    [
      { "label": ..., id: 1 },
      { "label": ..., id: 2 }
    ]
    

    Then we can tell whichever library you land to use those parameters.

    If you are using an Ajax auto complete, its probably not worth using the ->options() method in the Editor PHP since the data for the options is going to come from your own PHP script for it anyway.

    Regards,
    Allan

  • MickBMickB Posts: 103Questions: 25Answers: 2

    Thanks Alan,

    OK. I think I am 90% there. I have a couple of problems still. The dropdown is working and I can select a new item.

    1, faults.description is not displayed when the Editor opens.

    2, when I select a new option and click Update, the new value is not saved. The AJAX for the Update still has the original value.

    label: 'Description:',
                                    name: 'dev_selection.faults.description',
                                    type: 'select2',
                                    opts: {
                                        multiple:false,
                                        minimumInputLength: 3,
                                        ajax: {
                                            url: "/autocomplete/faults/",
                                            dataType: "JSON",
                                            delay: 250,
                                            data: function (params) {
                                                return {
                                                    query: params.term, // search term
                                                    page: params.page
                                                };
                                            },
                                            //https://select2.github.io/examples.html
                                            processResults: function (data, params) {
                                                // parse the results into the format expected by Select2
                                                // since we are using custom formatting functions we do not need to
                                                // alter the remote JSON data, except to indicate that infinite
                                                // scrolling can be used
                                                params.page = params.page || 1;
                                                return {
                                                    results: data.items,
                                                    pagination: {
                                                        more: (params.page * 30) < data.total_count
                                                    }
                                                };
                                            },
                                            cache: true
                                        },
    
                                        minimumInputLength: 3,
                                        templateResult: function(data) {
                                            return data.description;
                                        },
                                        templateSelection: function(data) {
                                            return data.description;
                                        },
    
                                        }
                                    }
    
    Editor::inst($db, 'fault_type_process_type')
                ->where("stream_id",$stream_id)
                ->where("process_type_id",$process_type_id)
                ->fields(
                    Field::inst("fault_type_process_type.id"),//always needed
                    Field::inst("fault_type_process_type.rank"),//always needed
                    Field::inst('fault_type_process_type.fault_id'),
                    Field::inst('dev_selection.faults.id'),
                    Field::inst('dev_selection.faults.description')
                )
                ->leftjoin('dev_selection.faults', 'fault_type_process_type.fault_id', '=', "dev_selection.faults.id")
                ->process($postData)
                ->json();
    
    public function autocompleteFaults()
        {
            $term      = Input::get('query');
            $associate = array();
            $search    = DB::select(
                "
                SELECT id ,description 
                from dev_selection.faults
                where match (description )
                against ('+{$term}*' IN BOOLEAN MODE)
                "
            );
            foreach ($search as $result) {
                $faults['items'][] = $result;
            }
            return json_encode($faults);
        }
    
    
  • MickBMickB Posts: 103Questions: 25Answers: 2
    edited March 2017

    Aha,

    name: 'fault_type_process_type.fault_id',
    

    The update is now working!

    Just need to sort the description when Editor loads.

    Should this happen automatically, or do I need to manually set the initial value for Select2?

    Mick

  • MickBMickB Posts: 103Questions: 25Answers: 2
    edited March 2017

    Looks similar to this:

    https://datatables.net/forums/discussion/comment/91842/#Comment_91842

    debug code: iwebim

    Trying to debug and val is null here:

     get: function ( conf ) {
    
             var val = conf._input.val();
    
  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    It should happen automatically! Are you using the lightbox editing here, or inline editing?

    The fact that it doesn't automatically show up suggests that the value that is being edited isn't in the list of values that Select2 knows about.

    Allan

  • MickBMickB Posts: 103Questions: 25Answers: 2
    edited March 2017

    Lightbox.

    When I first edit an existing record, the description is blank.
    I can select a new description (autocomplete works) and Update it.
    Then, if I open this record again, the description is displayed correctly.

    Do I need to set the "options": [ as well as fetching them results in the AJAX?

    Is this correct?:

    label: 'Description:',
    name: 'fault_type_process_type.fault_id',
    type: 'select2',
    

    I want to display the description but save the id.

    Mick

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

    Interesting one - yes I absolutely see your point.

    I've just been experimenting with the Select2 examples. The Ajax data load example (.js-example-data-ajax) is the one of interest here. If we open it and type an a then select "angular" from the list - the value is:

    > $(".js-example-data-ajax").val()
    < 35130077
    

    So the label here is not the same as the value - perfect, that matches what you are doing.

    Now reload the page and in the console do:

    $(".js-example-data-ajax").val('35130077').trigger('change')).
    

    That sets the select element to the value for the Angular entry as we had selected it above, but the label doesn't populate - exactly as you are seeing.

    Interestingly the Select2 FAQs mention exactly this issue and link to this SO post which says to call the change event. Trying that on the Select2 page doesn't actually work! Indeed, Editor does a change trigger itself when the value is set.

    This thread has some discussion on the topic as does this one.

    It appears that there isn't a nice simple way of doing this with Select2 frustratingly. I'll keep digging.

    Allan

  • MickBMickB Posts: 103Questions: 25Answers: 2

    Damn,

    Is this easy to do with a different autocomplete? I don't mind swapping.

    I guess it might be worth trying to fix this, in case other users come across the same problem.

    Mick

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

    To be honest I don't know as this is in the scope of the third party libraries rather than in Editor itself.

    I'm starting to think that it might be worth raising this in the Select2 issues list, but there are over 600 issues there at the moment, so I'm not sure how quickly it would be resolved.

    Allan

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

    Its not proven to be trivial I'm afraid, but this is the change that is needed to make it work on the client-side - in the Select2 integration plug-in for Editor replace the set function with:

        set: function ( conf, val ) {
            // The value isn't present in the current options list, so we need to add it
            // in order to be able to select it. Note that this makes the set action async!
            // It doesn't appear to be possible to add an option to select2, then change
            // its label and update the display
            if ( conf.opts && conf.opts.ajax && conf._input.find('options:attr(value["'+val+'"])').length === 0 ) {
                $.ajax( $.extend( {
                    data: {
                        initialValue: true,
                        value: val
                    },
                    success: function ( json ) {
                        $('<option/>')
                            .attr('value', val)
                            .text( json.text )
                            .appendTo( conf._input );
    
                        conf._input
                            .val( val )
                            .trigger( 'change', {editor: true} );
                    }
                }, conf.opts.ajax ) );
            }
            else {
                conf._input
                    .val( val )
                    .trigger( 'change', {editor: true} );
            }
        },
    

    This will cause an Ajax request to be made to the server with the data requested for the value selected. The parameters sent are:

    • initialValue: true
    • value: ... {i.e. the actual value}

    The server needs to respond with:

    { "id": value, "text": "Label to show" }
    

    Editor will then show the label returned.

    This is the little PHP script I've been using to test it:

    <?php
    if ( isset( $_REQUEST['initialValue'] ) ) {
      if ( $_REQUEST['value'] == 1 ) {
        echo '{ "id": "1", "text": "Edinburgh" }';
      }
      else {
        echo '{ "id": "2", "text": "London" }';
      }
    }
    else {
    ?>
    
    {
      "results": [
        { "id": "1", "text": "Edinburgh" },
        { "id": "2", "text": "London" }
      ]
    }
    
    <?php } ?>
    

    I'm not happy with that frankly, but I don't see any other way with the Select2 API (which isn't documented) to do this.

    I'm going to spend some time with the various auto complete plug-ins and tie a single one more tightly into Editor. Just need to pick the most suitable.

    Regards,
    Allan

  • MickBMickB Posts: 103Questions: 25Answers: 2

    Thanks Allan.

    I will drop that in tomorrow.

    Yes, I think it would be great if one of these components was encapsulated in the Editor.

    Please keep me updated.

    Mick

  • MickBMickB Posts: 103Questions: 25Answers: 2

    Hey Allan,

    I think I have stumbled across a solution without your fix:

    {
                                    label: 'Description:',
                                    name: 'fault_type_process_type.fault_id',
    
                                    type: 'select2',
                                    opts: {
                                        multiple:false,
    
                                        minimumInputLength: 3,
                                        ajax: {
                                            url: "/autocomplete/faults/",
                                            dataType: "JSON",
                                            delay: 250,
                                            data: function (params) {
                                                return {
                                                    query: params.term, // search term
                                                    page: params.page
                                                };
                                            },
                                            //https://select2.github.io/examples.html
                                            processResults: function (data, params) {
                                                // parse the results into the format expected by Select2
                                                // since we are using custom formatting functions we do not need to
                                                // alter the remote JSON data, except to indicate that infinite
                                                // scrolling can be used
                                                params.page = params.page || 1;
                                                return {
                                                    results: data.items,
                                                    pagination: {
                                                        more: (params.page * 30) < data.total_count
                                                    }
                                                };
                                            },
                                            cache: true
                                        },
    
                                        minimumInputLength: 3,
                                        templateResult: function(data) {
                                            return data.text;
                                        },
                                        templateSelection: function(data) {
    
                                            return data.text;
                                        },
    
                                        }
                                    }
    
    

    I changed the field names in my query:

    SELECT id ,description as text
    
    

    and updated in the templates:

                                        templateResult: function(data) {
                                            return data.text;
                                        },
                                        templateSelection: function(data) {
    
                                            return data.text;
                                        },
    

    Does this work for you? I'm not sure if I am going mad or not. :-)

    Mick

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

    I'm not sure why that would work without an Ajax request to be honest, and the problem was that Select2 wasn't triggering an Ajax request to get the data to render.

    Does that trigger an Ajax request for you?

    Allan

  • MickBMickB Posts: 103Questions: 25Answers: 2

    No, the only AJAX is when the Datatable first loads.

    The next is when I have typed in the select and it is doing the autocomplete.

    I added some logging:

     templateResult: function(data) {
               console.log('result');
                return data.text;
      },
      templateSelection: function(data) {
               console.log(data);
               console.log('selection');
                return data.text;
    

    This is the result (which is where I first saw the text field):

    Object {selected: true, disabled: false, text: "Clay Pluck", id: "10", title: ""…}
    faults:270 selection

    This shows that templateSelection gets called, when I click the Edit button on the Datatable.

    Mick

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

    Awesome. If that works go with it!

    Allan

  • NakkeeranNakkeeran Posts: 7Questions: 3Answers: 0

    ****~~1. hjygvyujgjh* # :) ~~****

This discussion has been closed.