Jump to content

SORT_NATURAL


Jason Huck
 Share

Recommended Posts

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.

Link to comment
Share on other sites

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 by kongondo
Link to comment
Share on other sites

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!

 

 

Link to comment
Share on other sites

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/

  • Like 1
Link to comment
Share on other sites

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. 

  • Like 1
Link to comment
Share on other sites

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.

  • Like 1
Link to comment
Share on other sites

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.

 

Link to comment
Share on other sites

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.

  • Like 1
Link to comment
Share on other sites

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 by kongondo
Link to comment
Share on other sites

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.

 

Link to comment
Share on other sites

  • 2 years later...

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.

 

Link to comment
Share on other sites

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.

2019-03-04_211005.png.98623a47cb15893c24023677b6059274.png

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);
	}
});

2019-03-04_212357.png.b8a6ff2c9c13db9b4efb9e78e5d8406b.png

  • Like 3
Link to comment
Share on other sites

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!

  • Like 1
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
  • Similar Content

    • By Robin S
      Repeater Easy Sort
      Adds a compact "easy-sort" mode to Repeater and Repeater Matrix, making those fields easier to sort when there are a large number of items.
      The module also enhances Repeater Matrix by allowing a colour to be set for each matrix type. This colour is used in the item headers and in the "add new" links, to help visually distinguish different matrix types in the inputfield.
      Screencasts
      A Repeater field

      A Repeater Matrix field with custom header colours

      Easy-sort mode
      Each Repeater/Matrix item gets an double-arrow icon in the item header. Click this icon to enter easy-sort mode.
      While in easy-sort mode:
      The items will reduce in width so that more items can be shown on the screen at once. The minimum width is configurable in the field settings. Any items that were in an open state are collapsed, but when you exit easy-sort mode the previously open items will be reopened. You can drag an item left/right/up/down to sort it within the items. The item that you clicked the icon for is shown with a black background. This makes it easier to find the item you want to move in easy-sort mode. You can click an item header to open the item. An "Exit easy-sort mode" button appears at the bottom of the inputfield. Configuration
      In the field settings for Repeater and Repeater Matrix fields you can define a minimum width in pixels for items in easy-sort mode. While in easy-sort mode the items will be sized to neatly fill the available width on any screen size but will never be narrower than the width you set here.
      In the field settings for Repeater Matrix you can define a custom header colour for each matrix type using an HTML "color" type input. The default colour for this type of input is black, so when black is selected in the input it means that no custom colour will be applied to the header.
      Exclusions
      The easy-sort mode is only possible on Repeater/Matrix fields that do not use the "item depth" option.
       
      https://github.com/Toutouwai/RepeaterEasySort
      https://processwire.com/modules/repeater-easy-sort/
    • By Saleena Jhon
      Hello There, I have saw a post that was covering event-calendar with php, ajax and js. That was showing a monthly overview when I click on a "month" button or when I switch the month. And show the events on one particular date when I pick a day. Also, most events are kind of exhibitions and so they have a start date and an end date much later, and occur on each day in-between as well. So on the template I put two date picking fileds date_start and date_end. Is there an elegant way to select the events using the API? If yes, kindly help me out.
      Thanks in Advance
      Regards: 
       
    • By opalepatrick
      I am working on my first Process Module. I am creating forms. Fairly straightforward. However, I really can't work out how to create multiple fieldsets?
      $fieldset = $this->modules->get('InputfieldFieldset'); $fieldset->label = 'Customer Source'; $field = $this->modules->get('InputfieldPage'); $field->inputfield = 'InputfieldSelect'; $field->findPagesSelector = 'parent_id=1449, include=hidden'; $field->labelFieldName = 'yff-lead'; $field->name = 'yfflead'; $field->columnWidth = 16; $fieldset->add($field); $field = $this->modules->get('InputfieldPage'); $field->inputfield = 'InputfieldSelect'; $field->findPagesSelector = 'parent_id=1452, include=hidden'; $field->labelFieldName = 'customer-type'; $field->name = 'customertype'; $field->columnWidth = 16; $fieldset->add($field); //Rinse and Repeat $fieldset->label = 'Contacts'; $field = $this->modules->get('InputfieldPage'); $field->inputfield = 'InputfieldSelect'; $field->findPagesSelector = 'parent_id=1538, include=hidden'; $field->labelFieldName = 'salutation'; $field->name = 'salutation'; $field->columnWidth = 16; $fieldset->add($field); I can create the first fieldset (Customer Source) but then get into trouble as the second fieldset overwrites the first. I understand why, but trying to use the open and close fieldset routine has flummoxed me. Any help appreciated.
    • By VeiJari
      Hello forum, we're trying to use Processwire as our REST-API. We are having problems with our API login to Processwire from frontend. It gives us 403 error.
      We have installed ProcessWire to subdirectory (/api/*) and our frontend is static JS files at root ( / ). Apache access logs gives 404 to our POST-request, but browser devtools shows 403 for our POST /api/login request. 
      Processwire backend panel works. We also have a GET endpoint for the API that returns 200 with correct payload.  So we're wondering why does our GET works but POST doesn't?
      Does this have something to do with Processwire .htaccess, or is this because of our webhost? What should we check first? Any help would be appreciated.
    • By rjgamer
      Hi,
      is there a hook after the current (active) page got created? Or which method got called in the Page class after the Constructor of the current page got initialized?
      Thanks.
       
×
×
  • Create New...