3.2.0

Server side filtering AJAX-style

I needed to filter a large number of rows (stored in SQL) and wrote the following code which uses an AJAX-style http.request to get the necessary rows from the Server and then filter the grid based on this list.

I am using static data in this example (to keep it simple) but it can easily be adapted to get 'live' data off a backend database.

Firstly, you need to create a grid whose RowIDs correspond to the data IDs in your database.

In this example, I presume the data IDs are 111,222,333,444,555 but you will obviously substitute this whole data construction section with your own code to represent your 'live' data.

<html>
<head>
    <script src="./runtime/lib/aw.js"></script>
    <link href="./runtime/styles/classic/aw.css" rel="stylesheet"></link>
</head>
<body>

<script>
var myData = [];

myData["111"] =    ["MSFT","Microsoft Corporation", "314,571.156", "32,187.000"];
myData["222"] =    ["ORCL", "Oracle Corporation", "62,615.266", "9,519.000"];
myData["333"] =    ["SAP", "SAP AG (ADR)", "40,986.328", "8,296.420"];
myData["444"] =    ["CA", "Computer Associates Inter", "15,606.335", "3,164.000"];
myData["555"] =    ["ERTS", "Electronic Arts Inc.", "14,490.895", "2,503.727"];

var myColumns = ["Ticker", "Company Name", "Market Cap.", "$ Sales"];


Next, create the grid and a button to test the filtering

var obj = new AW.UI.Grid;

obj.setCellText(myData);
obj.setHeaderText(myColumns);

obj.setRowIndices([111, 222, 333, 444, 555]);

obj.setRowCount(5);
obj.setColumnCount(4);

obj.setSelectorVisible(false);

document.write(obj);

document.write('<br>');

var Testbutton = new AW.UI.Button;
Testbutton.setControlText("Filter Rows");
document.write(Testbutton);


Now for the interesting bit. Using a server side scripting language such as ASP or PHP you can dynamically get a list of data IDs from your backend (using http.request) and filter the grid accordingly. In this example, I presume the user selected some drop down which resulted in rows 222 and 444 being displayed, i.e. 111,333 and 555 are filtered out as a result of this user intereaction. I am simulating this by using a button just to keep the example simple.

Testbutton.onClick = function(){
    sendRequest('');  // Put your SELECT statement in here 
};

function createRequestObject() {
    var ro;
    var browser = navigator.appName;
    if(browser == "Microsoft Internet Explorer"){
        ro = new ActiveXObject("Microsoft.XMLHTTP");
    }else{
        ro = new XMLHttpRequest();
    }
    return ro;
};
 
var http = createRequestObject();
 
function sendRequest(queryString) {
    http.open('get', 'getRecords.php?query='+queryString);		// Call a Server side script to return the rows you want
    http.onreadystatechange = handleResponse;
    http.send(null);
};

function handleResponse() {
    if (http.readyState == 4){					// Finished loading the response
        var response = http.responseText;		// Get the response from the Server 
        var indexList = response.split(',');	// Create an array from the comma-separated list
        obj.setRowIndices(indexList);			// Only display these rows
        obj.setRowCount(indexList.length);		// Set the number of rows
        obj.setSelectedRows([indexList[0]]);	// Select the top row
    }
};
</script>
</body>
</html>


The getRecords.php file referred to above can contain a SQL SELECT statement to return the Data Ids based upon some selection by the user. For simplicity (and to keep the example generic), however, I have simply returned a static list of DataIDs in this example:

<?php
/***********************
getRecords.php
This can (and should) be modified to return a comma separated list of data IDs based on some SELECT statement which can be passed to this page via the "query" parameter in the URL
************************/
echo "222,444";
?>


If you click on the "Filter" button, you should only see two rows.

I found it to be very fast, especially on large data sets. I hope you find it useful.
Rick Jordan
October 14,
Rick

This is excellent and I have used the technique to filter lists of 10,000 records pretty effectively. It could also be applied I'm sure to autocomplete & search type filters etc.

One small change I might suggest is to switch the order in a couple of lines, namely:

obj.setRowIndices(indexList); // Only display these rows
obj.setRowCount(indexList.length); // Set the number of rows

becomes

obj.setRowCount(indexList.length); // Set the number of rows
obj.setRowIndices(indexList); // Only display these rows

what I was finding was that when the filter value changed, the bottom rows of the grid were often left in an 'untidy' state because, I guess, the grid didn't know how many rows to display for the new set of RowIndices. This small change seems to rectify the problem.

I'm surprised you haven't had responses on this post singing your praises!

Will

(for an autocomplete I imagine a good way would be to construct something that searches a progressively smaller recordset?)
Will
October 21,
Dear Will

Thanks for your kind words and good tip regarding the order of those methods - I will try it out.

Yes, I was also suprised about the lack of response - I thought I was either stating the bleedin' obvious or hadn't explained it too well.

Anyway, I am glad you found it useful because it works really well for me.

I thought I should "give something back" to the community, because I always seem to be siphoning good ideas off everyone else. I am glad you enjoyed it.

Rick Jordan
October 24,
Rick,

actually this is a really great example (many thanks!!!!) and you will see that this thread will grow to 30-40 posts over the next months...
Alex (ActiveWidgets)
October 24,
Sorry for not thanking you sooner. This answered many HTTPRequest questions that I had.

Great example!
Frank
October 24,
Hi guys,

Isn't it better to use Active.XML.Table not static javascript arrays.

Here's some code, that works just fine for me:

// create ActiveWidgets data model - XML-based table
var grid_table = new Active.XML.Table;

// provide external model as a grid data source
grid.setDataModel(grid_table);

// provide data URL
grid_table.setURL("http://server/something/?default_filter=yes");

// set rows XPath
grid_table.setRows('//data/*');

// start asyncronous data retrieval
grid_table.request();


..........


function ApplyFilter(from_date,to_date) {
    url = "http://server/something/?from_date="+from_date+"&to_date="+to_date;
    grid_table.setURL(url);
    grid_table.request();
    grid.refresh();
}
Nikolay Simeonov
November 8,
Great example! This is an important step towards reaching full functionality of Grid components available for desktop applications. I think that this functionality should be added to the official release. Just an idea - maybe to provide something like this for the extended grid:

grid_table.filterrow = true;

and there could be one additional row on top of the grid with textbox for entering filter value and a combobox beside for selecting a field to filter on.

It's not that hard to do and could be very useful!

Leo
November 14,
I used the above functions ...

function createRequestObject() {
var ro;
var browser = navigator.appName;
if(browser == "Microsoft Internet Explorer"){
ro = new ActiveXObject("Microsoft.XMLHTTP");
}else{
ro = new XMLHttpRequest();
}
return ro;
};

var http = createRequestObject();
function sendRequest(queryString) {
http.open('get', 'getRecords.php?query='+queryString); // Call a Server side script to return the rows you want
http.onreadystatechange = handleResponse;
http.send(null);
};
function handleResponse() {
if (http.readyState == 4){ // Finished loading the response
var response = http.responseText; // Get the response from the Server
var indexList = response.split(','); // Create an array from the comma-separated list
// doChanges(indexList);
}
};
function modify_det(id, val)
{
http.open('get', 'modify_det.php?id='+id+'&val='+val); // Call a Server side script to return the rows you want
http.onreadystatechange = handleRespMod;
http.send(null);

}
function handleRespMod() {
if (http.readyState == 4){ // Finished loading the response
var response = http.responseText; // Get the response from the Server
var indexList = response.split(','); // Create an array from the comma-separated list
}
}
function modify_detalii()
{
//enab();
var val = document.form.det.checked==true?1:0;
var id = document.form.id.value;
modify_det(id, val);
}

so.. I made this functions and assigned some of them to some buttons...

the problem is that when I click one of the buttons it goes and modifies the database... but in my http object when I click some button to get the results from the database I get the same old data even if in the database they were modified...
for example.. I have an input with the value "AAA" [which is from the database], I modify it into "BBB" and then push the button to update into the database[database updated]... but when the input is displayed again it has the same old value "AAA"..
I think the problem is that the http object must be cleaned or something like this...

some feedback pls
thank you
Dragos
November 16,
Rick,

You have posted an excellent example which we were also thinking to implement in our reporting system as well. Don't know if Alex has planned to make it a part of official release, so other would also get a chance to extend it....

Excellent Stuff!!
Fahad Hanif
November 16,
Rick, Thank you very much for this example that will perfectly suits to the needs of many developers!!

There is just one thing that is unclear: after using the way you showed of sorting the grid, my grid does not display the first and last row. It seems the setRowIndices-function is not working properly. I checked the javascript array with the row-id's, the row-id's of the first and last row are in it. Is this a know bug to anybody? How to explain this reaction?
Michael
November 17,
Excellent example I think AJAX support should be supported in the final 2.0 release it's the way things are going.
Terry
November 17,
Michael

It may be a bug with regard to the setCurrentRow method. See my posting entitled "Grid not displaying top row". Maybe you are having the same problem?
Rick Jordan
November 22,
This is a very nice code! Thanks!

Since I'm using the 1.0.2 GPL as of now I have to migrate it.

I also encountered a bug wherein when you sort the columns then push the test button, the resulting row count in the response is not being applied on the grid especially if the new row count is greater than the old one. The data changes after pushing the button but the row count doesn't change. Does anybody have a workaround?

@ Dragos
I also use to encountered that kind of problem wherein the data doesn't change. I think the page that handles the response is not recompiled and the old page is still reused by the browser. I resolved it by applying some setting some response headers on the page like Cache-Control.
romz
December 12,
Dragos,
Change the following line from:
http.open('get', 'getRecords.php?query='+queryString);

to:
http.open('post', 'getRecords.php?query='+queryString);

The GET method seems to not update, while the POST method will ensure a fresh pull every time
JPC
January 12,
Great example! This makes filtering much easyer and faster! Thanks!
Laurens
February 2,
OK, I've tryed to move i a bid, cause my form has more tabs with grid on each one. I need to get data loaded into other grids by selecting record in the main tab's grid.

Here is the Code:

function createRequestObject() 
    {
        var ro;
        var browser = navigator.appName;
        if(browser == "Microsoft Internet Explorer"){
            ro = new ActiveXObject("Microsoft.XMLHTTP");
        }else{
            ro = new XMLHttpRequest();
        }
        return ro;
    }; 
    
    grid1.requestObject = createRequestObject();
 
    grid1.sendRequest = function(requestMethod, requestPage, queryString) 
    {
        alert('going to send request');
        this.requestObject.open(requestMethod, requestPage+queryString);        // Call a Server side script to return the rows you want
        this.requestObject.onreadystatechange = this.handleResponse();
        this.requestObject.onerror = alert('Eror Encountered: \n\n'+this.requestObject.statusText);
        this.requestObject.send(null);
    };

    grid1.handleResponse = function() 
    {
        if (this.requestObject.readyState == 2){alert(this.requestObject.readyState);}
    	if (this.requestObject.readyState == 4){                    // Finished loading the response
            alert('request sent - parsing');
        	var response = this.requestObject.responseText;        // Get the response from the Server 
            var indexList = response.split(';');    // Create an array from the comma-separated list
            //this.setRowIndices(indexList);            // Only display these rows
            this.setRowCount(indexList.length);        // Set the number of rows
            this.setCellText(indexList);
            this.setSelectedRows(0);    // Select the top row
            alert('Request Finished');
    	}
    };


But something does trick me out... I see that request is sent, but never gets to the stage 2... To not know why...

PS: PHP code returns this...

["10","3","AGROFOREST a.s.","Nova plan","26","792 01","BRUNTAL","","","","","3","",];
ASJ
April 25,
Perhaps I'll get back to easier solution by patching the grid by script generated in hidden <Iframe> :(

But anyway... I would like to know the sollution, cause I'd like to use that one day...

PS: FF does not show any JS error, but IW throws somethig about Type Mismatch... at

this.requestObject.open(requestMethod, requestPage+queryString);
ASJ
April 25,
BTW: I know, that it may seem stupid, but I see, that it would be better to use AW.UI.Table, but I cannot find any hint how :(
ASJ
April 25,

This topic is archived.

See also:


Back to support forum