Hey Hans. After using Dave's trick to display SQL generated by ofbiz, shown here:
http://markmail.org/message/s57f7znzczb6us6h And seeing what is occuring during the category listing, it appears that only the product_category_rollup table is initially queried: GenericDAO.java:753:INFO ] Ran query in 1 milli-seconds: EntityName: ProductCategoryRollup Sql: SELECT PRODUCT_CATEGORY_ID, PARENT_PRODUCT_CATEGORY_ID, FROM_DATE, THRU_DATE, SEQUENCE_NUM, LAST_UPDATED_STAMP, LAST_UPDATED_TX_STAMP, CREATED_STAMP, CREATED_TX_STAMP FROM public.PRODUCT_CATEGORY_ROLLUP WHERE ((PARENT_PRODUCT_CATEGORY_ID = ?)) ORDER BY SEQUENCE_NUM ASC where clause:{PARENT_PRODUCT_CATEGORY_ID=GOOGLE_BASE} So it appears that there is no secondary sort being generated on category_name. The order returned is some random database order when there is no sequence_num entered. Since this table doesn't even have category_name, it's probably inefficient to join in the product_category table in order to sort the name. However, it does make sense to perform a sub-sort on product_category_id, which is available from the rollup table. This works for word-based product_category_id's (like "GB_SPRTNG_GDS" or "GB_OFFC_SPPLS"), but doesn't work great for numeric product_category_id's, like mine. I've resorted to just filling out the sequence_num field, which still works great. So: The best thing to do by default is to first sort on sequence_num, followed by a sub-sort on product_category_id. On Wed, Mar 30, 2011 at 7:14 PM, Hans Bakker <mailingl...@antwebsystems.com> wrote: > > Now the tree just retrieves what is shown. if you open a branch, that > branch is fetched at that moment..... > > let is know what is shown wrong according to you and we will have a > look. > > Regards, > Hans > > On Wed, 2011-03-30 at 18:51 -0700, Mike wrote: > > >At the moment the tree is sorted by seqnum and categoryId, > > >we will change it to segnum, categoryName. > > > > I think the above seems correct. From what understand regarding your > > recent code modification, you were trying to optimize large categories, > > probably by caching the entire category listing in one shot. Is this > > correct? What is puzzling is why the Demo Google is affected (a separate > > catalog), unless the caching is taking place at the browse root level (one > > gigantic cache), and not at the individual catalog level (multiple catalog > > cache entries). > > > > On Wed, Mar 30, 2011 at 4:10 PM, Hans Bakker > > <mailingl...@antwebsystems.com>wrote: > > > > > can you explain what you think is the correct sort order? > > > > > > 'reversed' is a bit difficult to understand for us. > > > > > > > > > Regards, > > > Hans > > > > > > On Wed, 2011-03-30 at 12:58 -0700, Mike wrote: > > > > I checked, and it's running post r1086436 code, and the Google Demo > > > category > > > > sort still appears reversed. > > > > > > > > ------------------------------------------------------------------------ > > > > r1086436 | hansbak | 2011-03-28 15:59:50 -0700 (Mon, 28 Mar 2011) | 1 > > > line > > > > Changed paths: > > > > M > > > > > > > /ofbiz/trunk/applications/product/src/org/ofbiz/product/category/CategoryServices.java > > > > > > > > > > > > > > > > On Wed, Mar 30, 2011 at 6:57 AM, Jacques Le Roux < > > > > jacques.le.r...@les7arts.com> wrote: > > > > > > > > > Actually in this case it was ok (I checked yesterday). You can check > > > the > > > > > rel-rev # in the footer... > > > > > > > > > > Jacques > > > > > > > > > > From: "Hans Bakker" <mailingl...@antwebsystems.com> > > > > > > > > > > Hi Mike, > > > > >> > > > > >> 1. it can take up to 24hrs until the revision shows up in the trunk > > > > >> 2. is the sorting now acceptable or not and how you would like to > > > > >> have > > > > >> the sorting? > > > > >> > > > > >> regards, > > > > >> Hans > > > > >> > > > > >> On Tue, 2011-03-29 at 01:03 -0700, Mike wrote: > > > > >> > > > > >>> Hey Hans. I tested this and it doesn't seem to work. Trunk-demo > > > shows > > > > >>> the > > > > >>> same. > > > > >>> > > > > >>> On Mon, Mar 28, 2011 at 4:01 PM, Hans Bakker > > > > >>> <mailingl...@antwebsystems.com>wrote: > > > > >>> > > > > >>> > Committed revision 1086436: "catalog category tree now ordered by > > > > >>> seqnum > > > > >>> > and category name > > > > >>> > > > > > >>> > Regards, > > > > >>> > Hans > > > > >>> > > > > > >>> > On Sun, 2011-03-27 at 08:47 -0700, Mike wrote: > > > > >>> > > Something has changed with the sorting of the category listing. > > > I > > > > >>> > noticed > > > > >>> > > this about 1-2 weeks ago: > > > > >>> > > > > > > >>> > > If you go to demo: > > > > >>> > > > > > > >>> > > http://demo-trunk.ofbiz.apache.org/ecommerce/control/main > > > > >>> > > > > > > >>> > > <http://demo-trunk.ofbiz.apache.org/ecommerce/control/main > > > >Select > > > > >>> the > > > > >>> > > "Google Catalog", and you'll see that the category list is now > > > > >>> reversed > > > > >>> > > (Animals were always on top before). I also noticed the same on > > > my > > > > >>> own > > > > >>> > > category listing. > > > > >>> > > > > > > >>> > > Any idea? > > > > >>> > > > > > >>> > -- > > > > >>> > Ofbiz on twitter: http://twitter.com/apache_ofbiz > > > > >>> > Myself on twitter: http://twitter.com/hansbak > > > > >>> > Antwebsystems.com: Quality services for competitive rates. > > > > >>> > > > > > >>> > > > > > >>> > > > > >> > > > > >> -- > > > > >> Ofbiz on twitter: http://twitter.com/apache_ofbiz > > > > >> Myself on twitter: http://twitter.com/hansbak > > > > >> Antwebsystems.com: Quality services for competitive rates. > > > > >> > > > > >> > > > > > > > > > > > -- > > > Ofbiz on twitter: http://twitter.com/apache_ofbiz > > > Myself on twitter: http://twitter.com/hansbak > > > Antwebsystems.com: Quality services for competitive rates. > > > > > > > > -- > Ofbiz on twitter: http://twitter.com/apache_ofbiz > Myself on twitter: http://twitter.com/hansbak > Antwebsystems.com: Quality services for competitive rates. >