SORT_NATURAL

[Jason Huck]

What's the easiest way to retrieve a PageArray -- with an offset and a limit, for use in paginated search results -- that is sorted by title using PHP's SORT_NATURAL instead of alphabetically? I was hoping there was a config setting or API method that would handle it for me, but if there is, I haven't stumbled across it yet.

[fbg13]

Try

$pages->find($selector)->sort("fieldToSortBy");

 

[kongondo]

Have a look at these discussions: Edit: Not sure will work for you actually, i.e. if you need a database level sort.

@fbg13, I'm not sure that will produce desired results since that sort will take place in-memory, i.e. after the PageArray has been retrieved. I think @Jason Huck wants the sort to take place at database level.

Edited January 22, 2017 by kongondo

[Jason Huck]

Whether truly at the database level or not, I was hoping there was a built-in PW method to apply that sorting algorithm. Otherwise I will probably have to create a separate field just for sorting, and add a hook when new pages are created that retrieves the entire result set, sorts it manually, and updates that field with each page's position in the entire set. That's the only way I can think of that will allow me to later retrieve a filtered subset that remains in the correct order. If there's another approach I should consider, I'm all ears.

Thanks!

 

 

[fbg13]

I have pages that have children whose name/title is just a number.

echo "<pre>" . print_r( $page->children()->sort("name"), true) . "</pre>";
echo "<pre>" . print_r( $page->children("sort=name"), true) . "</pre>";

The above gives me

//echo "<pre>" . print_r( $page->children()->sort("name"), true) . "</pre>";
ProcessWire\PageArray Object
(
  [items] => Array
  (
    [2] => /parent/1/
    [0] => /parent/3/
    [3] => /parent/5/
    [4] => /parent/11/
    [1] => /parent/15/
    [6] => /parent/31/
    [5] => /parent/33/
  )
)
// echo "<pre>" . print_r( $page->children("sort=name"), true) . "</pre>";
ProcessWire\PageArray Object
(
  [items] => Array
  (
    [0] => /parent/1/
    [1] => /parent/11/
    [2] => /parent/15/
    [3] => /parent/3/
    [4] => /parent/31/
    [5] => /parent/33/
    [6] => /parent/5/
  )
)

 

19 minutes ago, kongondo said:

wants the sort to take place at database level

Seems like this is not possible as mysql can't sort results that way.

http://stackoverflow.com/questions/153633/natural-sort-in-mysql

I think that's why the ways of sorting i posted above are different, first is sorted by php that can natural sort and second is sorted by mysql which can't natural sort/

[kongondo]

1 hour ago, fbg13 said:

Seems like this is not possible as mysql can't sort results that way....

No, it is not possible out-of-the-box but there are workarounds:

http://www.mysqltutorial.org/mysql-natural-sorting/

https://www.copterlabs.com/natural-sorting-in-mysql/

https://chrisjean.com/mysql-natural-sort-order-by-on-non-numeric-field-type/

1 hour ago, Jason Huck said:

....add a hook when new pages are created that retrieves the entire result set, sorts it manually, and updates that field with each page's position in the entire set...

If we are talking a potentially huge result set, I would consider a SQL solution (see links above) to update that field for each page in the result set. 

[fbg13]

@kongondo is the sql solution preferred because it's faster or uses less memory, or both, compared to php?

[adrian]

It seems to me that PW's sort() could support PHP's sort_flags - then you could specify something like:

$pages->find($selector)->sort("fieldToSortBy", NATURAL_SORT);

I just did a quick hack of WireArray.php and on first glance it all seems to work fine. The NATURAL_SORT flag does require PHP 5.4.

[kongondo]

14 minutes ago, fbg13 said:

@kongondo is the sql solution preferred because it's faster or uses less memory, or both, compared to php?

Depends on the scenario:

1. If I want to retrieve a limited number of pages (with no requirements for pagination), then have those naturally sorted, I'd go for PHP.
2. If I want paginated results, it means I need them to be already 'naturally-sorted' as they are retrieved, meaning, doing it at the database level, then I'd go for SQL.
3. The present case: Using a Hook to amend the values of a group of pages every time a new page is created. If that group of pages is substantially huge, if using PHP, it means, first retrieving the whole group, sorting them naturally, then saving them. That in itself could be a big hit on the server (we are loading lots of Page objects in memory). Assuming pages are created at a high frequency, that further compounds the issue. In such cases, SQL will most likely be faster and use less memory.

 

[Jason Huck]

1 hour ago, adrian said:

It seems to me that PW's sort() could support PHP's sort_flags - then you could specify something like:

$pages->find($selector)->sort("fieldToSortBy", NATURAL_SORT);

I just did a quick hack of WireArray.php and on first glance it all seems to work fine. The NATURAL_SORT flag does require PHP 5.4.

 

That would be a nice addition, though in my particular use case, I think I'd need support within the selector itself, e.g. something like one of these:

$pages->find('...etc...,sort=title', ['sortmethod' => NATURAL_SORT]);
$pages->find('...etc...,sort=title,sort.method=natural');

...otherwise, I'd only be sorting the returned PageArray, and not the entire set, so it couldn't be used for pagination.

[kongondo]

Maybe two options to consider:

1. WireCache: Create a natural sort field, say nat_sort. Have this hidden; we don't need to see it in the admin. Add that to the template of the pages you need to sort naturally + paginate (let's call the template 'nat-sort-pages'). Create your Hook and add a function that will build/refresh a non-expiring Wire Cache every time a page that uses 'nat-sort-pages' template is created (no need to do this when the page is edited; just when added). The cache will save the new page's ID, Title (the field to naturally sort) and a 'nat_sort' value of 0 (or whatever your starting index is; here we also assume this is the first page created). Subsequently, when another page is added, your Hook will retrieve the cache, add the details of the new page to the array, use PHP natsort() to sort that array (by Title) + change the values of nat_sort within the array, save it back to the Wire Cache, then use SQL to insert the values in your nat_sort field. Your nat_sort field will be an integer field, so you SQL (pseudo code) will INSERT nat_sort_value IN nat_sort WHERE id=page->id. That should be a very fast operation. In the frontend, use a find sorted by your 'nat_sort' field (sort=nat_sort in the selector) to retrieve paginated results.
2. getById() + SQL nat sort: This approach does not require a nat_sort field nor a Hook. It is an on-demand method for use in the frontend. Use a suitable SQL workaround to naturally sort a limited number of results (i.e. see links above + you'll need to use SQL LIMIT and START). Your SQL will only need to fetch the IDs of those pages. Then use getById (see example here) to retrieve those pages, which you then pass on to your pagination.

Both approaches have their pros and cons, obviously.

Edited January 22, 2017 by kongondo

[Jason Huck]

6 minutes ago, kongondo said:

 

1. WireCache: Create a natural sort field, say nat_sort. Have this hidden; we don't need to see it in the admin. Add that to the template of the pages you need to sort naturally + paginate (let's call the template 'nat-sort-pages'). Create your Hook and add a function that will build/refresh a non-expiring Wire Cache every time a page that uses 'nat-sort-pages' template is created (no need to do this when the page is edited; just when added). The cache will save the new page's ID, Title (the field to naturally sort) and a 'nat_sort' value of 0 (or whatever your starting index is; here we also assume this is the first page created). Subsequently, when another page is added, your Hook will retrieve the cache, add the details of the new page to the array, use PHP natsort() to sort that array (by Title) + change the values of nat_sort within the array, save it back to the Wire Cache, then use SQL to insert the values in your nat_sort field. Your nat_sort field will be an integer field, so you SQL (pseudo code) will INSERT nat_sort_value IN nat_sort WHERE id=page->id. That should be a very fast operation. In the frontend, use a find sorted by your 'nat_sort' field (sort=nat_sort in the selector) to retrieve paginated results.
2. getById() + SQL nat sort: This approach does not require a nat_sort field nor a Hook. It is an on-demand method for use in the frontend. Use a suitable SQL workaround to naturally sort a limited number of results (i.e. see links above + you'll need to use SQL LIMIT and START). Your SQL will only need to fetch the IDs of those pages. Then use getById (see example here) to retrieve those pages, which you then pass on to your pagination.
    

 

Option 1 is more or less what I assumed I would have to do. My data set is around 560 pages, and they are all created/updated in bulk via a custom import script, so I may end up just populating the natural sort field as part of that routine.

Option 2 isn't really an option in this case, because the contents of my sort field are highly irregular, and those SQL tricks rely on the sort field containing strings of predictable length and/or composition.

Thanks for the input -- I wanted to be sure I wasn't missing something in the API.

 

[Andi]

Sorry to bring up this issue once more, but I can't seem to find a proper way of implementing natural sorting for child pages in admin.

I have a template for artists, and a template for paintings as child pages. The titles of paintings often contain serial numbers as in "Forest 1", "Forest 2" ... "Forest 104". So the default way of sorting paintings by title in the page tree doesn't work, as it renders as "Forest 1", "Forest 104", "Forest 2" etc.

Is there a way of achieving this by hook or config option, that is paginateable and works for both front- and backend? Possibly without adding extra fields to the templates? It does seem to be fairly common use case but so far I've not managed to find a viable solution.. Also it's the first time that I didn't come across a "Oh well we do it this way in PW" within 3 minutes of research :)

Any help would be greatly appreciated! Thanks in advance & good evening ya'll.

 

[Robin S]

On 2/26/2019 at 6:59 AM, andi said:

Is there a way of achieving this by hook or config option, that is paginateable and works for both front- and backend? Possibly without adding extra fields to the templates?

I don't believe any such solution is possible. PW relies on SQL for sorting in $pages->find() selectors and SQL doesn't support natural sorting. So I think you'll have to do one of the following:

1. Load all the pages you want to sort into memory so you can sort them with PHP. Not a good solution if you have a large number of pages.

2. Request that content editors insert leading zeros to the numbers you want to sort on, so that standard SQL sorting will be sufficient. This would be subject to human error/oversight and would probably need to be done in a separate field to allow the title field to remain in the desired format without leading zeroes.

3. Use a saveReady hook to automatically parse titles into components and save these to dedicated sort fields. This seems like the most promising option to me.

An outline of how you could do option 3...

Add 3 new fields to the painting template: prefix (text), number (integer), suffix (text).

Add the following hook to /site/ready.php:

$pages->addHookAfter('saveReady', function(HookEvent $event) {
	$page = $event->arguments(0);
	if($page->template == 'painting') {
		// Find the last number in the title and get the prefix/suffix before/after the number
		preg_match('/^(.*\D)(\d+)(.*)$/', $page->title, $matches);
		if(count($matches)) {
			// There is a number so populate each sort field
			$page->prefix = $matches[1];
			$page->number = $matches[2];
			$page->suffix = $matches[3];
		} else {
			// There is no number so put the whole title into the prefix field
			$page->prefix = $page->title;
			$page->number = '';
			$page->suffix = '';
		}
	}
});

When a painting page is saved this results in a division of the title like below. You would set the visibility of the sort fields to "hidden" for production but I have shown them below for clarity.

Now when you want to find painting pages sorted naturally you sort on the three sort fields in order:

$paintings = $pages->find("template=painting, limit=10, sort=prefix, sort=number, sort=suffix");

You can sort the painting pages (children of a page with the "paintings" template) in the back-end with this hook:

$wire->addHookBefore('ProcessPageList::find', function(HookEvent $event) {
	$selector = $event->arguments(0);
	$page = $event->arguments(1);
	// If page is the parent 'paintings' page
	if($page->template == 'paintings') {
		// Set the children selector to sort on the three sort fields
		$selector .= ', sort=prefix, sort=number, sort=suffix';
		$event->arguments(0, $selector);
	}
});

[bernhard]

Or to reduce the chance of wrong user input you could leave the three fields visible and hide the title field which you could then populate via hook

[Andi]

2 hours ago, Robin S said:

3. Use a saveReady hook to automatically parse titles into components and save these to dedicated sort fields. This seems like the most promising option to me.

An outline of how you could do option 3...

It seems like a long way to go, but this looks fantastic. I'll get on it asap, thanks a bunch @Robin S and @bernhard.

@marcus Wondering if this might deserve a recipe, I could imagine quite a bunch of people are struggling with this issue.

Thanks again, and greetings to NZ!