Guriddo » Topic: Sort Types

Sort Types

Guriddo
Sort Types

This topic has 13 replies, 3 voices, and was last updated 16 years, 4 months ago by MarkC.

Creator

Topic
December 8, 2009 at 5:03 am #74905

MarkC
Participant

Hi Mark/Tony

(Using MySQL->ASP->XML)

I am doing a project to define the grid from a database (like a grid contruction kit, with a backend). The data table to fill the grid will initially have 3 fields called col1,col2,col3 they will all be VARCHAR(30). We are not concerned with the datatype in the table it could be dates, integers, text, floats all wrapped around a varchar (you'll see why later)

All is working fine and indexing/sorting correctly based on text sorttype. Now i'm trying to define datatypes at grid level. (presume data is correct for now e.g. integer will only have strings of 0to9)

My next step is to allow col1,col2,col3 to be associated as integer/text/date in the backend database, so even though they are defined as VARCHAR(30) in the database, i thought i could use SORTTYPE to define how they are sorted at grid level.

For example: if col1 has 3 records with data:

1
10
2

When SORTTYPE set to INT, i was expecting

1
2
10

even though the data at database level is varchar

But i still get 1,10,2.

Is this possible, do i need to look at formatter module,to cast Varchars into a different type for sorting. Any advice or steering in the right direction would be helpful.

—

The reason i am not defining the datatypes at database level, is eventually this needs to be a generic system with upto 7 columns all based around a CATEGORY, each category will have a different set of data e.g. col1 could be an integer for categoryA, but col1 for CategoryB could be TEXT…and so on. SO i thought define them as Varchars and handle the types at grid level if possible. All the data is held in one table and when a user selects a category (dropdown combo) it reloads the grid framework and data defined by the currently selected category.

Regards

Mark C
Creator

Topic

Viewing 13 replies - 1 through 13 (of 13 total)

Author

Replies
December 8, 2009 at 6:27 pm #91306

tony
Keymaster

Hello,

Not sure, but you define SORTTYPE, you should sorttype :'int'

Regards

Tony

December 8, 2009 at 8:01 pm #91313

MarkC
Participant

Hi Tony,

Yes i am using sorttype: 'int'

Maybe my post was too long winded…simply put….

If you have a column that in MySQL is defined as varchar(10) and it holds some data say 5 records

December 8, 2009 at 8:09 pm #91314

tony
Keymaster

Hello,

Could you please post the entry jqGrid options(including colModel and etc) – without modifications.

Best Regards

Tony

December 8, 2009 at 8:38 pm #91316

MarkC
Participant

My colModel is fine, {name:'col1', index:'index1', align:'right', sorttype:'int', sortable:true, width:80}

Simply…what is sorttype? maybe it's not what i thought it was, as in take any data and sort it like it was an integer?

The reason i get a sorted column like

1
10
2
2000
300

is that the SQL has already sorted the data based on it's datatype (VARCHAR) This is then parsed into the XML cells. EWhixh is then displayed in the grid. I thought the sorttype might do something with teh xml ordering, by saying now define it as an integer even though it is a varchar. Basically, sort a VARCHAR as if it was an integer to get 1,2,10,300,2000, hence my first question why use sorttype if it has already been sorted as a varcgar,data,integer,float by the SQL:

strSQL = “SELECT DOCID,INDEX1,INDEX2 FROM DOCUMENTS ” &strWhere
strSQL = strSQL & ” ORDER BY “&strIdx&” “&strOrd&” LIMIT “&strStart&” , “&strLimit&” “

and parsed into XML in that order.

Regards

MarkC

December 8, 2009 at 8:45 pm #91318

Mark
Participant

Hi Mark,

I think your problem is on the server side. sorttype only applies to local sorting, and I dont think you're using that (you /could/, by fetching all the data, and then switching to datatype: “local”, but you didnt mention that).

So you need to look at your sort code on the server. I think what you need is to add the datatype to postdata in a beforeRequest handler.

Basically, in your beforeRequest handler, look up the sorttype for the sort column, and add that to postdata (any name you like), and then modify you SQL to sort according to that…

Mark

December 8, 2009 at 8:45 pm #91319

MarkC
Participant

I can do it at SQL level:

strSQL = “SELECT DOCID,CAST(INDEX1 AS SIGNED) as index1,INDEX2 FROM DOCUMENTS ” &strWhere
strSQL = strSQL & ” ORDER BY “&strIdx&” “&strOrd&” LIMIT “&strStart&” , “&strLimit&” “

this takes VARCHAR(20) INDEX1 and casts it into an signed integer, so when ordering it will be corectly sorted like an integer.

Works fine. BUt i thought the grid would do it using sorttype!!!

December 8, 2009 at 8:47 pm #91320

MarkC
Participant

Hi MarkW,

Will have a look at your suggestions.

Regards

Mark C.

December 8, 2009 at 8:55 pm #91321

tony
Keymaster

Hello Mark C.,

I Think that Mark catch the problem in the right way.

In order to perform sorting client side you should set the datatype to local. Just test it

Regards

Tony

December 8, 2009 at 9:11 pm #91322

tony
Keymaster

Hello,

I think that you maybe should look here:

http://www.trirand.com/jqgridwiki/doku.php?id=wiki:howto_grid_base#client_side_sorting_but_server_side_paging

Regards

Tony

December 8, 2009 at 11:32 pm #91329

MarkC
Participant

Thanks Tony,

I will have a look.

I am using scrolling not paging as we are talkng about 200,000 records and i am allowing the user to auto incremtn search on keypressing. (like your bigset.php demo)

But i'll have a look into the local setting.

regards

MarkC

December 9, 2009 at 12:16 am #91330

MarkC
Participant

I have grasped the 'local' sorttype concept now. I tested loading 20 records of type TEXT (defined in database) and set it to sorttype:'int' with with datatype: 'local', refreshed grid and it was sorted as if it was integer.

But, i am using 200,000 records (LIMIT 20 at a time SQL) with scroll:true, so no paging.

Is it possible to switch between 'local' and 'xml' when we start scrolling, similar to when we start paging (onPaging). So as we scroll and retrieve the xml data, when it stops, i set it to local, refresh grid and data takes on the from of an integer again.

Also, i am filterng the data by searching on keypress, so would have to do something similar , switch to XML when keypressed get data, swicth back to local,refresh grid.

Regards

MarkC

December 9, 2009 at 12:24 am #91331

Mark
Participant

If you have 200000 records, the “local” approach isnt going to work (even 10,000 would be a stretch).

So I think you'll have to send the sort-type to the host, and sort there…

Mark

December 9, 2009 at 12:39 am #91332

MarkC
Participant

Hi Mark,

Yes i agree, but the way i was designing the datbase each grid column will also have a datatype field associated to it e.g.

col1 varchar(40)
col1datatype char(1)
col2 varchar(24)
col2datatype char(1)
.
.
. and so on

where the datatype is I,T,F,D (integer,text,float,Date). This allows the main data table of 200,000 records to all be VARCHAR(40) but sorted by a particlaur type. (presuming data is valid!!!!) by casting.

So i will interpret the I,T,F,D on the server side and SQL CAST() it, it will then be in the correct form for the grid so no need to use sorttype.

For a one off project i would just define the datatypes at the datbase level, but this is supposed to be a generic design of upto 20 columns each column having different types of data…and as i mentioned earlier there is a CATEGORY field, and col1 for categoryA could be a date, but col1 for categoryB could be an integer….hence the 'warpping of teh data in a VARCHAR(40)

Thanks for the guidance once again….when i eventually get paid for this i'll have to donate some beer money!!!

Regards

MarkC
Author

Replies