Autocomplete select

MickB

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

allan

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

MickB

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);

    }

MickB

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

MickB

I'm thinking Select2?

Just not sure how do that via Ajax?

Mick

allan

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

MickB

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);
    }

MickB

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

MickB

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();

allan

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

MickB

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

allan

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

MickB

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

allan

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

allan

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 {

<?php
>
?>


{
  "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

MickB

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

MickB

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

allan

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

MickB

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

allan

Awesome. If that works go with it!

Allan

Nakkeeran

****~~1. hjygvyujgjh* # ~~****