jqGrid toolbar search with autocomplete using json data

10,405

It is difficult to provide an example in case of the usage of remote source parameter of jQuery UI Autocomplete. The main problem is that your question is about jqGrid which is pure JavaScript solution. If we would discuss the server part of tha solution we would have too options. Many users uses different languages: Java, C#, VB, PHP and so on. For example I personally prefer C#. Then we would have to choose the technology which we use: ASP.NET MVC, WCF, ASPX web service and so on. For example I would choose WCF. Then we should define the database access technology, for example, Entity Framework, LINQ to SQL, SqlDataReader, SqlDataAdapter and so on. Let us I would choose Entity Framework and would provide you the corresponding code example, but it would help you not really if you use for example PHP and MySQL.

So I just describe which interface should have the server for the remote source parameter of jQuery UI Autocomplete without any code.

You should replace in my example the source parameter to your server url like following:

dataInit: function(elem) {
    $(elem).autocomplete({
        source:'yourSearchUrl.php',
        minLength:2
    });
}

If the user types two characters (the value can be changed by minLength option), for example 'ab' then the autocomplete will make HTTP GET request with the parameter term=ab:

yourSearchUrl.php?term=ab

your server should answer with the JSON data in the same format as for the local source. I used the string array format in my previous example. Another supported format is array of objects with label/value/both properties like

[
    {
        "id": "Dromas ardeola",
        "label": "Crab-Plover",
        "value": "Crab-Plover"
    },
    {
        "id": "Larus sabini",
        "label": "Sabine`s Gull",
        "value": "Sabine`s Gull"
    },
    {
        "id": "Vanellus gregarius",
        "label": "Sociable Lapwing",
        "value": "Sociable Lapwing"
    },
    {
        "id": "Oenanthe isabellina",
        "label": "Isabelline Wheatear",
        "value": "Isabelline Wheatear"
    }
]

read the documentation for more information.

If you need to implement more complex scenario and send some additional data to the server or convert the server response in any way you can use custom source callback function. In the case you should use source: function(request, response) {/*your implementation*/}, where the request would be an object having term property (request.term). Inside of your implementation your should make ajax request to the server manually. The response would be callback function which you should call after your custom ajax request will be finished (inside of success event handler). The response function should be called with the parameter which should be array in the same format as mygetUniqueNames returns. I recommend you to examine the source code from the jQuery Autocomplete demo.

To de able to provide unique data from one column of tabele you should just use SELECT DISTINCT SQL statement which are supported in the most databases.

I hope that my description would help you.

UPDATED: If you have the local source the solution you could find in my old answer which you already use. What you just need to do is to call the filterToolbar after the source array are filled. Because you load the data from the server your should move the call of filterToolbar inside of loadComplete. You use loadonce:true jqGrid option which switch the datatype from 'json' to 'local' after the first data loading. So you can include in the loadComplete event handler of your grid the code like the following:

var grid = $('#list');
grid({
    url:'autocompleteTest.php',
    datatype: 'json',
    loadonce: true,
    // ... other parameters
    loadComplete: function(data) {
        if (grid.getGridParam('datatype') === 'json') {
            // build the set 'source' parameter of the autocomplete
            grid.jqGrid('setColProp', 'name', {
                searchoptions: {
                    sopt:['bw'],
                    dataInit: function(elem) {
                        $(elem).autocomplete({
                            source:mygetUniqueNames('name'),
                            delay:0,
                            minLength:0
                        });
                    }
                }
            });
            mygrid.jqGrid('filterToolbar',
                          {stringResult:true,searchOnEnter:true,
                           defaultSearch:"bw"});
        }
    }
});

If you will need to reload the data from the server (change the datatype to 'json' and call grid.trigger('reloadGrid')) you will have to change the code above so that you first destroy the autocomplete widget with $('#gs_name').autocomplete('destroy') and then create it one more time with the same code like inside of dataInit.

Share:
10,405
J. Kaufman
Author by

J. Kaufman

Updated on June 04, 2022

Comments

  • J. Kaufman
    J. Kaufman over 1 year

    I found the very nice demo by Oleg (http://www.ok-soft-gmbh.com/jqGrid/FillToolbarSearchFilter.htm) which shows a "jqGrid toolbar search with autocomplete using local data" but have trouble to get this to work for json via ajax. Is there a good reason why the autocomplete feature won't work - even if I force the grid to be local after loading?

    $(document).ready(function() {
    
        var mygrid = $("#mylist"),
        mygetUniqueNames = function(columnName) {
            var texts = mygrid.jqGrid('getCol',columnName), uniqueTexts = [],
                textsLength = texts.length, text, textsMap = {}, i;
            for (i=0;i<textsLength;i++) {
                text = texts[i];
                if (text !== undefined && textsMap[text] === undefined) {
                    // to test whether the texts is unique we place it in the map.
                    textsMap[text] = true;
                    uniqueTexts.push(text);
                }
            }
            return uniqueTexts;
        };
    
    
    
    
    
        mygrid.jqGrid({
                url:'autocompleteTest.php',
                datatype: "json",
                colNames:['name', 'City','stateCd'],
                colModel:[                      
                        {name:'name',index:'name',width:225, search: true},
                        {name:'City',index:'City',width:125},
                        {name:'stateCd',index:'stateCd',width:75},
                      ],
    
             rowNum: 100,
            loadonce : true,
             sortname: 'name',
            sortorder: 'desc',
             sortable: true,
            viewrecords: true,
            rownumbers: true,
            sortorder: "desc",
            ignoreCase: true,
            pager: '#mypager',
            height: "auto",
            caption: "How to use filterToolbar better with data from server"
        }).jqGrid('navGrid','#mypager',
                  {edit:false, add:false, del:false, search:false, refresh:false});
    
        mygrid.jqGrid('setColProp', 'name',
                {
                    searchoptions: {
                        sopt:['bw'],
                        dataInit: function(elem) {
                            $(elem).autocomplete({
                                source:mygetUniqueNames('name'),
                                delay:0,
                                minLength:0
                            });
                        }
                    }
                });
    
        mygrid.jqGrid('filterToolbar',
                {stringResult:true, searchOnEnter:true, defaultSearch:"bw"});
    
        });
    
  • J. Kaufman
    J. Kaufman over 12 years
    I appreciate very much your effort, but I already have the data in the grid - so I fail to see why I need to query server again for data that jqGrid already knows about. I want to take the data from a column in the grid and enable autocomplete in the same way you did for local data. I think that my question was only about jqgrid and autocomplete - not where or how I got the data from server...
  • Oleg
    Oleg over 12 years
    @: If you have the local source the solution you could find in my old answer. What you just need is to call filterToolbar after the source array are filled. So your should move the call of filterToolbar inside of loadComplete. You should call it only on the first grid loading (if datatype is still 'json').
  • J. Kaufman
    J. Kaufman over 12 years
    YES! That was it. Really great to have had your answer. Would you put that last bit as an answer to this question? I don't think I can vote yet but I want to thank you.
  • Oleg
    Oleg over 12 years
    @J. Kaufman: I updated the answer to describe all more clear. You will be able to vote up questions or answers after the first 15 points of reputation.
  • Ovi
    Ovi almost 12 years
    @Oleg, and again this is a great answer. i got one problem, if you look at my code at this question link, i get my data from webservice, and if i do this: dataInit: function(elem) { $(elem).autocomplete({ source:'./WebService.asmx/ViewNQueryData', minLength:2 }); } with my url i get a browser exception. it never gets to the server, what has to changed?
  • Oleg
    Oleg almost 12 years
    @Ovi: I just answered on your question. the error was because of is duplicates which is not permitted.
  • Ovi
    Ovi almost 12 years
    @Oleg, thanks for all answers, i fixed that problem i had, but still with the above code i wrote in the comment the grid tries to go to /WebService.asmx/ViewNQueryData?term=gg and cant do it.
  • Oleg
    Oleg almost 12 years
    @Ovi: I am not sure that I understand you correctly. Do you have an exception? Where? In what line of code? Which code you use exactly. Do you can see that the request with the parameter term=gg will be send to the server? Do you receive the request in the server code (can you set the breakpoint)? ...
  • Ovi
    Ovi almost 12 years
    @Oleg, i posted a new question here, thanks