Description: ------------ This is two features but the record grouping requires the multiple-sort (and I'm currently working on both) so I'm making it one request. I've patched Structures_DataGrid (and the DO and Array drivers) to handle sorting my multiple fields/columns. It also displays what the ordering is if there are more than one sort columns. I have also added record grouping capabilities. Mutliple groups are nested. I have alo written some JS to allow collapsing / expanding of the groups. As it is now it doesn't even need IDs, it's allrelative to the clicked TH (I think that's damn cool :-) ). Sorting is done within the groups by the normal sort columns specified. Here's the example for both: http://pear.reversefold.com/ShowTable.html The data is grouped first by Genre then by EnumTest (it's only a testing column). The records are sorted by Genre descending then by Title ascending. Patch is here: http://pear.reversefold.com/SDG_multiSort_grouping.diff Note: the actual record grouping is done by sorting first by the grouping fields and then by the sorting fields. THe group headers are done by checking the contents of the grouping fields. I am still working on this so the example page or patch could change. Right now I'm looking for some guidance on whether you think this is a useful addition and whether you agree with my implementation. I think this could be a great addition to SDG for reporting capabilities as well as quick data exploring. I am also looking into displaying sub-records and possibly setting up totals for columns. P.S. Andrew, all of my e-mails to you (asnagy@webitecture.org) seem to be bouncing.

Hi Justin, (1) most of your links on the example page don't work because the "test" directory is password protected (2) you can also try this address: andrew (dot) nagy (at) villanova (dot) edu

The test directory is meant to be password protected. Those pages are just static examples of what the new features can do.

I've updated my patches a bit to make the JS work better. I'd really like some feedback here as I'm nearing a stopping point with this and would like to start using these changes in some real applications.

The javascript code works for me in both IE 6 and Firefox 1.0 (both on Windows) but there is a small error, see the following screenshot: www.markwiesemann.de/php/screenshot-reversefold.png You can reproduce the same error (not exactly the same as in the screenshot) by clicking on ">>" in the row with id = 13. I would also be nice to be able to test the sorting links without having to apply the patch.

Yep, that's the one final error I'm working out. Please note, however, that that is the sub-form javascript which doesn't directly pertain to this feature request. I'm working on the sub-form stuff but right now it's a hack on my application, not an addition to Structures_DataGrid. I'll create another feature request so that we have a place to discuss that as well. I could make the links "work", but I haven't set up any kind of multi-sort support for the links. If the sort links worked they would replace the multi-sort with a single sort. Perhaps I can get something working for the links as well....

What do you mean with "I haven't set up any kind of multi-sort support for the links"? Is the sorting not working with your new grouping features or do you don't want to show it on the example page because of the other possibilities like editing or deleting?

Multi-sort and grouping work just fine together. What I mean is that you can only currently set up multi-column sorting via code, there is no way (currently) to do multi-column sorting by clicking on the links in the column headers. YOu'll only get single column sorting, same as before. Here's a live example for you: http://pear.reversefold.com/example/index.php?table=movie You can also click on the other tables and such if you like. The movie table by default has multiple column sorting. If you click on a column header you'll override that with single column sorting. Also note that sorting happens *after* grouping. So the sorting column you choose only sorts records within each of the sub-groups. (Basically this means that the grouping columns are sorted on first then the "sort" columns are sorted on. This is actually how I implemented it in the code.) One more note: I've turned on Record editing and adding in that example so that you can play with the data. Please don't abuse it.

Thanks for making the links work. This way it is easier to catch how sorting works with the new grouping. It's a nice idea and useful for some sort of tables. Can you please also give a short example on how to use the grouping? I.e. how does one tell SDG to group on a specific column? Suggestion for the multiple column sorting: What do you think of clicking three instead of two times on the column headers: first click sorts ascending, second click sorts descending and the last click removes sorting from this column. Example: Click one time on "Title", then two times on "Genre". Result is something like ORDER BY title ASC, genre_id DESC. Then click on "Genre" again, and then one time on "DateAcquired". Result: ORDER BY title ASC, dateAcquired ASC. Finally click two times on "Title". Result: ORDER BY dateAcquired ASC. Together with the arrow symbols and the little numbers next to the column headers this should be easy to understand. (Just for the records: One time in the new example the [+] link only showed the column headers of the subtables, not the contents of them. I tried to reproduce this but wasn't able.)

I'm not 100% that this is how we want to leave the grouping set up, but right now you do: $dg->addGroup('someField'); $dg->addGroup('anotherField'); For multi-column sorting: $dg->sortRecordSet(array('field1', 'field2'), array('ASC', 'DESC')); As for the multi-click thing it makes some sense, but I think it will be somewhat confusing to people... Normally these kinds of grids only allow single column sorting and they'll expect clicking a column to sort only by that one. Also, that solution would mean 2 or 3 extra requests and processing to remove / add a column to the list. Of course I'm not sure what would be better... One solution would be to have other little links in the column titles to add a column to the sorting. Another would be to have little popup divs which you can use to, say, choose the sorting. Yet a third would be to leave normal sorting as it is and add some kind of DHTML sorting UI which could be easily integrated (say via a hidden DIV which is displayed when you click a button). This could use something like the following URL for easy manipulation (should we want it). http://mir.aculo.us/demos/draggable2.html That also could be useful for the grouping mechanism.

You're right, the three-click-sorting may be confusing, as it's not a known concept. The link you submitted is outdated. Maybe you mean this one? http://script.aculo.us/drag-and-drop#sortable-lists-demo Nice idea but maybe also confusing as you normally do not sort things this way on web pages. It will be hard to find a way to solve this multisort issue so that it is easy to understand, does not need much space in the table (for the links) and it fits perfectly into the different layouts. But it is still a nice feature, even if only used on initialization of the DataGrid and then overridden by normal sorting when someone clicks on the column headers.

Simply amazing. That link updated when I posted it and script.aculo.us wasn't up at that point. Looks like I caught it right on the brink. I'll see if I can hack up something simple (and easily turned off) to do multi-sort via links.

I've added docblock comments to all functions and member vars I've added as well as comments in the JavaScript. The multi-sort stuff should probably be changed a bit. There are currently 2 member vars with sorting data, the original ones and new ones I added. They should be merged somehow but I haven't delved into that yet. I just wanted to get it working.

Is their a final patch for this, or code for me to look at?

The patch linked is still the current one. I haven't taken the time as yet to combine the internal vars. This won't change my code much if at all, it would be chanegs ot how the current code handles the sorting terms.

And now the code has been severely altered, making my patch invalid...::sigh::. I really think that grouping and multi-sort are useful features for some uses of SDG (such as reports). I'd like these 2 features to get into SDG even if the JS group collapsing doesn't make it in. Does anyone have any use for this now? I'd rather not re-implement this with the new SDG code if noone is going to actually apply the patch.

Justin, grouping and multi-sorting are nice features. The same holds for my own request about custom headers and footers (#5298). The new rendering concept allows to easily write renderers that extend existing renderers. I did not go further with my header/footer request, as the new fill() functionality provides enough flexibility to add the header row directly via HTML_Table and then pass the HTML_Table instance to fill() (I'll publish this as an example soon). For your request, I (being only a developer within this package) would extend the current HTMLTable renderer and publish this a new package with a dependency on the HTMLTable renderer package (we want to split-off the DataSource and Renderer drivers into small packages). But maybe Andrew can also post a short comment about these two feature-requests? (grouping/multi-sorting)

I think the multi-sorting is a necessary feature for a stable release. I am not totally sold on the grouping though. As for the other features, such as javascript colapsing, etc. I think that is outside the scope of the class and should be added in as a new renderer that extends the current html table renderer.

In order to support such multiple field sorting feature in the future, the new renderer driver interface needs to be improved. These $_currentSortField and $_currentSortDirection protected properties are obviously not enough. I'm going to see what I can do.

The renderer layer now supports multiple field sorting. The renderer interface now has a protected $_currentSort property which can contain multiple fields and directions (see Structures_DataGrid_Renderer_Common). The core (class DataGrid) now also feature the required data structure (property $sortSpec). Whether we implement the current request or not, these changes will help for similar features in the future.

The datasource layer now supports multiple field sorting. It also supports a new "driver features" concept, which allows to do something like : if ($driver->hasFeature('fooBar')) {...} This is for BC, because the 'multiSort' feature allows to pass an array to the driver's sort() and fetch() methods. The drivers written by users for their own needs would break if we passed them an array. The first driver to have this feature is the DataObject driver. That means that : - SDG_DataSource_DataObject::hasFeature('multiSort') returns true. - and you can do : $spec = ('quantity' => 'ASC', 'price' => 'DESC); $datasource->sort($spec); or $datasource->fetch(0,10,$spec); This has been tested using the SDG_DataSource_Common:dump() method. The Structures_DataGrid core class has limited support for these new features. Some work is still required. But the two underlying layers, DataSource and Renderer, are ready (not all drivers though). All of this is in CVS. What do you all think about it ?

Okay, sorting with multiple fields (multiSort) is now fully supported. I have created a new rendering driver, HTMLSortForm, that outputs a form with select statement(s) (sort by X, then by Y, then by Z, etc...). That looks much more portable to me than complicated javascripty-triple-clicky things ;-) It's also very simple to use : $datagrid->render(DATAGRID_RENDER_SORTFORM); It's a completely separated driver so it does not bloat the HTMLTable driver, etc... You can write other sort of multiSort-capable renderers. The DataGrid accepts the following GET arguments : ?orderBy[0]=price&direction[0]=ASC&orderBy[1]=stock&direction[1]=DESC&etc... About DataSources : MDB2 and DataObject drivers are multiSort-capable. More to come. Now, I like this grouping feature, but I still don't have clear ideas about this. I must read Justin's patch again and investigate some more.

Unless I missed something, we can't yet define multiple fields sort on columns. I've made a patch that allows to define a list of fields for each column. I only tested it with HTMLTable but normally I also upgraded the other renderers. Since I don't want to clutter this thread with code, I'll send the patch directly to both Mark & Olivier.

Link to the patch: http://www.phpfi.com/126708

Michel, I don't understand your patch. Can you explain what you're trying to do ? Multiple fields sorting is 100% supported in the core. However, not all drivers support it yet. Is this what you missed ?

'Feedback' is the right status here (and after the feedback, 'Closed' might follow hopefully).

See the discussion about record grouping in bug #8353

Sorry for the delay, the last mails were lost (maybe because of the vacation...) AFAIK so far it is possible to sort by more than one column (depending on the DS) and it is possible for the end-user to click on the column title to choose another sort order. However, on the latter case only one column can be sorted. My patch allows to define several sort fields for each column to be applied when the end-user clicks on the column. I can provide more details on the code if needed

Michel, > AFAIK so far it is possible to sort by more than one > column (depending on the DS) ... In the meantime all DataSources should support it (at least the most recent used and recommended ones). > ... and it is possible for the end-user to click on the > column title to choose another sort order. However, on > the latter case only one column can be sorted. That's right. And that's the way all (?) other grid views behave: You can only sort by one column. This is even true for Excel. If you want advanced, multiple column sorting you need to open a dialog where you enter the ordering options. The same is possible with SDG and the HTMLSortForm Renderer. Therefore, can you please explain, why we should implement in a different way in SDG (compared to other grids) and how the user should know, in which column order the ordering is done? (For example: User clicks on second column's header, then on first column's header. How does he know later that the grid is first ordered by the second column and then by the first column). Maybe we have also a misunderstading here. If that's the case, please try to explain in a different way. Thanks!

This time, I am sure I didn't receive your reply by mail... So if I hadn't check manually this request, this would have take a long time before I answer! I agree that if the user wants full control on how the columns are sorted, he needs to supply the list of columns to sort (BTW thanks for HTMLSortForm, this is really convenient). However, as a developer, you know/guess that if the user requests a given column as first sort, he will want to have some other columns as secondary sort. So my point was to define for each column the list of secondary columns to sort (I'll need to update my code to rename the option to something like "secondarySort" which will be more meaningfull). I implemented that for HTMLTable, but I think it could also be implemented for XLS (through macros) and maybe also for XUL.

> This time, I am sure I didn't receive your reply by > mail... I had similar problems some months ago. If it happens again, you might consider writing to systems@php.net about this problem. > However, as a developer, you know/guess that if the > user requests a given column as first sort, he will > want to have some other columns as secondary sort. Okay, but in my opinion this must be transparent to the user of the grid. It might be convenient also without a visual note about the secondary sort, but it might also lead to confusion. Let's see what Olivier thinks about this. (and also about your request #8490)

Okay, I think I understand your request Michel, and it is completely relevant to me. However, more generally, you may want to be able to assign a "Sort expression" to a column (as in .NET), not just a list of fields. Please see the TODO item 1 & 2 about this: http://cvs.php.net/viewcvs.cgi/pear/Structures_DataGrid/TODO?revision=1.11&view=markup This would be a great feature. However this is out of the scope of the current request. To avoid confusion, I'm closing this bug: "multiple field sort" as initially mentionned here has been implemented, and the "grouping" feature is either a won't fix or could be partially implemented by a new driver (see request #8490). Michel, please open a new request about Sort Expressions.