dataset sorting

wgknowles · July 9, 2004, 5:46pm

I have a form displaying a good number of records (>400) with a decent number of fields (~12)

Using List View the scrolling rate is not acceptable. Is there a way to cache the entire foundset to increase this performance?

If not, I would go the route of showing as HTML. some concerns with this route is that I lose the sorting functionality. I have an idea of using the foundset to do the sorts, then convert it to a dataset and reload the HTML.
I can’t find a database manager function to convert a foundset into a dataset.

can someone help?

maarten · July 10, 2004, 8:39am

I would say, use database manager to perform direct query for your HTML report (much faster).
Additionally, you can add the sorting functionality by linking sort scripts to href links inside your HTML.

I’ll add a sample solution tomorrow or monday.

jcompagner · July 11, 2004, 11:45am

how is the scrolling rate not acceptable? How do you scroll? Do you have many related fields in that listview?

You could walk once through youre foundset. Then every record is cached.

wgknowles · July 12, 2004, 5:36pm

Scrolling is essentially useless when using the arrow up/down buttons, Going a page at a time is reasonable, but I would really like something that has smooth scrolling as I drag the bar up and down. The window isn’t updated until the release. In HTML view the scrolling is nice and smooth.
Looping through each record to cache doesn’t do much of anything. It seems to be an inherent Java Page Rendering issue.

Doing it all in HTML using getDataSetByQuery() requires me to code a new query for each sort possibility, both asc and desc. This is already done in Servoy Foundsets! Why reinvent the wheel?

Is it really that hard to convert a foundset into a dataset? You already have databaseManager.getFoundSetDataProviderAsArray(foundset,‘order_id’)

Using this I could loop through each field and compile a big array and then add all the html table tags… Sounds fun…

I would like to make use of dataset.getAsHTML()
I have to play around with another possibilty of getting just the pk field after a sort and then loading a dataset using the following algorithm:

databaseManager.getFoundSetDataProviderAsArray(foundset,‘order_id’)
databaseManager.convertToDataSet(Object array/ids_string)
Dataset.getAsHTML()

I’m not sure that getFoundSetDataProviderAsArray returns the foundset in the sorted order. We’ll See

maarten · July 13, 2004, 9:41am

Here’s an example of a HTML REPORT CREATOR that can be copy pasted into any solution.

create a global textfield “HTMLReport”
create a script on any form you wish that looks like this:

var query = "select o.ordersid, c.company_name from orders o, companies c where o.company_id = c.companiesid";//define your query here
globals.HTMLReport = globals.htmlCreator(100,query,'o.ordersid','desc','newPortal'); //triggers the global script named "htmlCreator"
//arguments are: maxNrRows, queryString, initial sort column, initial sort direction, name of the global field that will hold the HTML code

place the global “HTMLReport” on your form. Set properties: editable=“unchecked”, displayType=HTML_AREA
create the global script “htmlCreator” and paste following code into it.

NOTE: you can create as many reports (formscripts triggering the globalscript) as you like and where you like!
I’ll put in aliases for headers and SQLerror handling later on this week.

//##################################
// RECEIVING ARGUMENTS #############
//##################################
var maxRows = arguments[0]
var query = arguments[1]
var sortColumn = arguments[2]
var sortDirection = arguments[3]
var htmlGlobalName = arguments[4]
var externalTrigger = arguments[5]
if(!externalTrigger)
{externalTrigger="true"}
//##################################
//##################################

var sortString = "";
if(sortColumn) //if contains string
{
	sortString = " order by "+sortColumn+" "+sortDirection;
}
if(sortDirection =="asc")
{sortDirection="desc"}
else
{sortDirection="asc"}

var headerBgColor = '#cccccc'
var rowBgcolorLight = '#faf9f9'
var rowBgcolorDark = '#edeaea'


var finalQuery = query+sortString
var dataset = databaseManager.getDataSetByQuery(currentcontroller.getServerName(), finalQuery, null, maxRows);

var HTML = '<html>'+
'<HEAD>'+
'<style type="text/css">'+
'	.header{'+
'		font-family: Verdana, Verdana, Arial, Helvetica, sans-serif;'+
'		font-size: 9px;'+
'		background-color: #999999;'+
'		border: 12px solid #000000;'+
'		color: #ffffff; text-decoration: none;}'+
'	.body{'+
'		font-family: Verdana, Verdana, Arial, Helvetica, sans-serif;'+
'		font-size: 9px;'+
'		color: #000000; text-decoration: none;}'+
'	</style>'+
'</HEAD>'+
'<TABLE border=1 class="body">';



//create headers
HTML += '<TR class="header"  >';
	for(var i=1 ; i <= dataset.getMaxColumnIndex() ; i++)
	{
		var columnName = dataset.getColumnName(i)
		 HTML += '<TD border=0><a href="javascript:globals.htmlCreator('+maxRows+',\''+query+'\',\''+columnName+'\',\''+sortDirection+'\',\''+htmlGlobalName+'\',\'false\')">'+columnName+'</a></TD>';
	}
HTML += '</TR>';


//create rows and columns
for( var i = 1 ; i <= dataset.getMaxRowIndex() ; i++ )
{
	dataset.rowIndex = i;
	
	//set rowBgColor
	if(i%2==0)
	{
		HTML += '<TR bgcolor='+rowBgcolorLight+'>';
	}
	else
	{
		HTML += '<TR bgcolor='+rowBgcolorDark+'>';
	}

		for(var j=1 ; j <=  dataset.getMaxColumnIndex() ; j++)
		{
			HTML += '<TD border=0>'+dataset[j]+'</TD>';
		}
	HTML += '</TR>';
}


HTML += '</TABLE>';

if(externalTrigger=="false")
{
	globals[htmlGlobalName] = HTML;
}
else
{
return HTML;
}

wgknowles · July 15, 2004, 6:34pm

Looks Great Maarten! Thanks a bunch!

Heres a suggestion to modify the Text Displayed in the headers but keep the sorting functionality working:

//create headers 
var headerArray = ["Name1", "Name2", "Name3", "Name4"]
HTML += '<TR class="header"  >'; 
   for(var i=1 ; i <= dataset.getMaxColumnIndex() ; i++) 
   { 
      var columnName = dataset.getColumnName(i) 
       HTML += '<TD border=0><a href="javascript:globals.htmlCreator('+maxRows+',\''+query+'\',\''+columnName+'\',\''+sortDirection+'\',\''+htmlGlobalName+'\',\'false\')">'+headerArray[i]+'</a></TD>'; 
   } 
HTML += '</TR>';

Next Is a way to adjust the table cell widths.

wgknowles · July 15, 2004, 9:20pm

hey Maarten,

Is there a way to make a link on each row that removes the record from the dataset?

maarten · July 16, 2004, 7:27am

I assume you mean omitting, and not deleting ?

note: I noticed that the href links didn’t work in version 21b4.
Is fixed for next release, but could be possible it doesn’t work in other previous beta releases (< 21b4)

btw: I like your elegant solution for naming the headers in an array.
However if you want to keep this code global(=re-usable for other queries)
you’ll have to re write the headerArray for each different query.
In standard SQL you can give a column name an alias like …SELECT columnName as “myName”… By pulling out these aliases for the headers, the global script remains truly global. I’ll adjust the code along with the omit/delete links.

wgknowles · July 16, 2004, 6:35pm

I assume you mean omitting, and not deleting ?

Correct.

btw: I like your elegant solution for naming the headers in an array.
However if you want to keep this code global(=re-usable for other queries)
you’ll have to re write the headerArray for each different query.

Thanks!
I ended up tacking on an extra argument to pass in the Array of headers, but aliasing the columnnames is a great option as well!

*One thing that is still a problem
lots of queries use double quotation marks, and this terminates the javascript call early.
I modified it to use ticks to encompass javascript:globals.htmlCreator()

It renders fine in IE, but in Servoy’s htmlarea I get this as the text of the href:

and v.vendor_id=s.vendor_id and ac.account_id=s.account_id and ac.account_name like "%anderson%";','total','asc','HTMLReport','false')`>Total

heres a subsection of the generated html:

<html><body><a href=`javascript:globals.htmlCreator(500,'select order_id, date_onclip, part_no, ac.account_name, description, v.vendor_name, price, qty, qty * price total from sop_tmp s, vendors v, accounts ac where 1=1 and v.vendor_id=s.vendor_id and ac.account_id=s.account_id and ac.account_name like "%anderson%";','total','asc','HTMLReport','false')`>Total</a></body></html>

and heres the modified header code of globals.htmlCreator():

//create headers 
HTML += '<TR class="header"  >'; 
for(var i=1 ; i <= dataset.getMaxColumnIndex() ; i++) 
	{ 
	var columnName = dataset.getColumnName(i) 
	HTML += '<TD border=0><a href=`javascript:globals.htmlCreator('+maxRows+',\''+query+'\',\''+columnName+'\',\''+sortDirection+'\',\''+htmlGlobalName+'\',\'false\')`>'+headerArray[i]+'</a></TD>';
	} 
HTML += '</TR>';

[/quote]

wgknowles · July 19, 2004, 7:08pm

I played with escape characters trying to get queries with " in them to work to no avail,
The only workaround I could come up with was to stick the query in a global and then change the htmlCreator to always look in that variable for the query. The query arguement becomes useless and is no longer needed to be encoded behind the A HREF links.

I would love to see if anyone else can get it to work better than this because The idea of using the method across mulitple forms is lost in this approach. Maybe the link can carry an arguement telling it which global to get the query from?

maarten · July 22, 2004, 8:38am

Here’s an updated global script that accepts aliases

create a global named “tempQuery”
a query string would look like this:
"select col1 as"MYNAME", col2, col3 as "COL3NAME" FROM …

The query that is first fired from a form script gets stored in the global and remains there untouched when clicking on href links (sort headers) in the html table.
When triggering an other form script , the global gets overwritten again.

//################################## 
// RECEIVING ARGUMENTS ############# 
//################################## 
var maxRows = arguments[0] 

if(arguments[1] !="-")
globals.tempQuery = arguments[1]
var query = globals.tempQuery
var sortColumn = arguments[2] 
var sortDirection = arguments[3] 
var htmlGlobalName = arguments[4] 
var externalTrigger = arguments[5] 

//################################## 
//################################## 
var headerNameArray = new Array();
var columnNameArray = new Array();
var selectPartDiv = query.indexOf("from")
if(selectPartDiv==-1)
{	selectPartDiv = query.indexOf("FROM");}
var selectPart = query.substring(7 , selectPartDiv)
var selectPartArray = selectPart.split(",");
for(var i=0 ; i<selectPartArray.length ; i++)
{
	var columnPart = selectPartArray[i];
	var div = columnPart.indexOf("\"")
	if(div>-1)
	{
	columnNameArray[i] = columnPart.substring(0,div-3)
	headerNameArray[i] = columnPart.substring(div+1, columnPart.length-2)
	}
	else
	{
		columnNameArray[i] = columnPart
		headerNameArray[i] = columnPart
	}
}


var sortString = ""; 
if(sortColumn) //if contains string 
{ 
   sortString = " order by "+sortColumn+" "+sortDirection; 
} 
if(sortDirection =="asc") 
{sortDirection="desc"} 
else 
{sortDirection="asc"} 

var headerBgColor = '#cccccc' 
var rowBgcolorLight = '#faf9f9' 
var rowBgcolorDark = '#edeaea' 


var finalQuery = query+sortString 
var dataset = databaseManager.getDataSetByQuery(currentcontroller.getServerName(), finalQuery, null, maxRows); 

var HTML = '<html>'+ 
'<HEAD>'+ 
'<style type="text/css">'+ 
'   .header{'+ 
'      font-family: Verdana, Verdana, Arial, Helvetica, sans-serif;'+ 
'      font-size: 9px;'+ 
'      background-color: #999999;'+ 
'      border: 12px solid #000000;'+ 
'      color: #ffffff; text-decoration: none;}'+ 
'   .body{'+ 
'      font-family: Verdana, Verdana, Arial, Helvetica, sans-serif;'+ 
'      font-size: 9px;'+ 
'      color: #000000; text-decoration: none;}'+ 
'   </style>'+ 
'</HEAD>'+ 
'<TABLE border=1 class="body">'; 



//create headers 
HTML += '<TR class="header"  >'; 
   	for(var i=1 ; i <= dataset.getMaxColumnIndex() ; i++) 
	{
       	HTML += '<TD border=0><a href="javascript:globals.htmlCreator('+maxRows+',\'-\',\''+columnNameArray[i-1]+'\',\''+sortDirection+'\',\''+htmlGlobalName+'\',\'false\')">'+headerNameArray[i-1]+'</a></TD>'; 
   	} 
HTML += '</TR>'; 


//create rows and columns 
for( var i = 1 ; i <= dataset.getMaxRowIndex() ; i++ ) 
{ 
   dataset.rowIndex = i; 
    
   //set rowBgColor 
   if(i%2==0) 
   { 
      HTML += '<TR bgcolor='+rowBgcolorLight+'>'; 
   } 
   else 
   { 
      HTML += '<TR bgcolor='+rowBgcolorDark+'>'; 
   } 

      for(var j=1 ; j <=  dataset.getMaxColumnIndex() ; j++) 
      { 
         HTML += '<TD border=0>'+dataset[j]+'</TD>'; 
      } 
   HTML += '</TR>'; 
} 


HTML += '</TABLE>'; 

if(externalTrigger=="false") 
{ 
   globals[htmlGlobalName] = HTML; 
} 
else 
{ 
return HTML; 
}

xtsr · December 2, 2004, 8:59pm

Is there a possibilty to do the sorting not in the query, but in the dataset? I can’t use “order by” directly in my query, because I use unions and MySQL 4.0 doesn’t support nested queries.

Reto

maarten · December 2, 2004, 9:44pm

there’s no sort method available for datasets.
Will ask dev team if this can be added as feature request.

pbakker · December 3, 2004, 7:45am

An option now to do the sorting outside of the query would be to load your dataset into a multi dimensional array and then using the Array sortfunction to sort.

After the sort, you rebuild your HTMl from the sorted data in the array.

This mehtod also means you only have to run your SQL query once, which is a nice feature when your query takes a while to run.

Hope it helps…

Paul

maarten · December 4, 2004, 12:27pm

will be added in the next build (after Version R2 2.1.2-build 315)

dataset.sort(int columnIndex,boolean ascending)

Topic		Replies	Views
Manage Results from SQL Searches Classic Servoy	49	31464	March 27, 2004
views Classic Servoy	33	26424	April 8, 2005
Sorting a foundset with a calculation field Classic Servoy	28	19285	November 7, 2011
Can't sort 200+ records Classic Servoy	6	4440	February 1, 2011
restrictions of getAsText() function of JSDataSet? Classic Servoy	32	25536	June 9, 2010

dataset sorting

Related topics