Jump to content
Jason Huck

SORT_NATURAL

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.

Share this post


Link to post
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

Share this post


Link to post
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!

 

 

Share this post


Link to post
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

Share this post


Link to post
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

Share this post


Link to post
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

Share this post


Link to post
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.

 

Share this post


Link to post
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

Share this post


Link to post
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

Share this post


Link to post
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.

 

Share this post


Link to post
Share on other sites

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.

 

Share this post


Link to post
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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
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

Share this post


Link to post
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

  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By VeiJari
      Hi, this is the first we are trying to make a page that has only one type of user that has access to every page. 
      The other users should only have a given access to specific pages, not to the whole template.
      My structure
      -Field -Organisation -Project -Report I want that the "measurer" role only has access to "project x" and it's children, but no view access to every project, organisation or field. I've tried to do this with https://modules.processwire.com/modules/page-edit-per-user/ but it still needs a view access to the whole tree to see the "project x" page. Or is there something I haven't figured out?
      Maybe I have to make it via the API: a select field in the "organisation" template where the admins could add the users and then I use hook to update the privileges?
      Have you done something like this and how did you accomplish it?
      Any help would be appreciated.
       
    • By cosmicsafari
      Hi all,
      Before I go potentially wasting time trying to achieve the impossible.
      Can anyone confirm if its possible to have a Page Reference field on a modules config page?
      I'm wanting to essentially just output a list of select able pages based on the a given selector (likely by template at this stage), wherein the select is the pages that the module should apply to etc. I was thinking a simple checkbox list would suffice is asmSelect isn't available.
      Essentially have it display the same way a Page Reference field would display on a template, where you can easily select a bunch of them.
      public function getInputfields() { $inputfields = parent::getInputfields(); $f = $this->modules->get('InputfieldPage'); $f->attr('name', 'testSelect'); $f->setAttribute('multiple', 'checkboxes'); $f->setAttribute('findPagesSelector', 'template=development'); $f->label = 'Test'; $inputfields->add($f); return $inputfields; } Figured something akin to the above would work but can't seem to get rid of this warning on the modules config screen though.

    • By Chris Bennett
      Hi all, I am going round and round in circles and would greatly appreciate if anyone can point me in the right direction.
      I am sure I am doing something dumb, or missing something I should know, but don't. Story of my life 😉

      Playing round with a module and my basic problem is I want to upload an image and also use InputfieldMarkup and other Inputfields.
      Going back and forth between trying an api generated page defining Fieldgroup, Template, Fields, Page and the InputfieldWrapper method.

      InputfieldWrapper method works great for all the markup stuff, but I just can't wrap my head around what I need to do to save the image to the database.
      Can generate a Field for it (thanks to the api investigations) but not sure what I need to do to link the Inputfield to that. Tried a lot of stuff from various threads, of varying dates without luck.
      Undoubtedly not helped by me not knowing enough.

      Defining Fieldgroup etc through the api seems nice and clean and works great for the images but I can't wrap my head around how/if I can add/append/hook the InputfieldWrapper/InputfieldMarkup stuff I'd like to include on that template as well. Not even sure if it should be where it is on ___install with the Fieldtype stuff or later on . Not getting Tracy errors, just nothing seems to happen.
      If anyone has any ideas or can point me in the right direction, that would be great because at the moment I am stumbling round in the dark.
       
      public function ___install() { parent::___install(); $page = $this->pages->get('name='.self::PAGE_NAME); if (!$page->id) { // Create fieldgroup, template, fields and page // Create new fieldgroup $fmFieldgroup = new Fieldgroup(); $fmFieldgroup->name = MODULE_NAME.'-fieldgroup'; $fmFieldgroup->add($this->fields->get('title')); // needed title field $fmFieldgroup->save(); // Create new template using the fieldgroup $fmTemplate = new Template(); $fmTemplate->name = MODULE_NAME; $fmTemplate->fieldgroup = $fmFieldgroup; $fmTemplate->noSettings = 1; $fmTemplate->noChildren = 1; $fmTemplate->allowNewPages = 0; $fmTemplate->tabContent = MODULE_NAME; $fmTemplate->noChangeTemplate = 1; $fmTemplate->setIcon(ICON); $fmTemplate->save(); // Favicon source $fmField = new Field(); $fmField->type = $this->modules->get("FieldtypeImage"); $fmField->name = 'fmFavicon'; $fmField->label = 'Favicon'; $fmField->focusMode = 'off'; $fmField->gridMode = 'grid'; $fmField->extensions = 'svg png'; $fmField->columnWidth = 50; $fmField->collapsed = Inputfield::collapsedNever; $fmField->setIcon(ICON); $fmField->addTag(MODULE_NAME); $fmField->save(); $fmFieldgroup->add($fmField); // Favicon Silhouette source $fmField = new Field(); $fmField->type = $this->modules->get("FieldtypeImage"); $fmField->name = 'fmFaviconSilhouette'; $fmField->label = 'SVG Silhouette'; $fmField->notes = 'When creating a silhouette/mask svg version for Safari Pinned Tabs and Windows Tiles, we recommend setting your viewbox for 0 0 16 16, as this is what Apple requires. In many cases, the easiest way to do this in something like illustrator is a sacrificial rectangle with no fill, and no stroke at 16 x 16. This forces the desired viewbox and can then be discarded easily using something as simple as notepad. Easy is good, especially when you get the result you want without a lot of hassle.'; $fmField->focusMode = 'off'; $fmField->extensions = 'svg'; $fmField->columnWidth = 50; $fmField->collapsed = Inputfield::collapsedNever; $fmField->setIcon(ICON); $fmField->addTag(MODULE_NAME); $fmField->save(); $fmFieldgroup->add($fmField); // Create: Open Settings Tab $tabOpener = new Field(); $tabOpener->type = new FieldtypeFieldsetTabOpen(); $tabOpener->name = 'fmTab1'; $tabOpener->label = "Favicon Settings"; $tabOpener->collapsed = Inputfield::collapsedNever; $tabOpener->addTag(MODULE_NAME); $tabOpener->save(); // Create: Close Settings Tab $tabCloser = new Field(); $tabCloser->type = new FieldtypeFieldsetClose; $tabCloser->name = 'fmTab1' . FieldtypeFieldsetTabOpen::fieldsetCloseIdentifier; $tabCloser->label = "Close open tab"; $tabCloser->addTag(MODULE_NAME); $tabCloser->save(); // Create: Opens wrapper for Favicon Folder Name $filesOpener = new Field(); $filesOpener->type = new FieldtypeFieldsetOpen(); $filesOpener->name = 'fmOpenFolderName'; $filesOpener->label = 'Wrap Folder Name'; $filesOpener->class = 'inline'; $filesOpener->collapsed = Inputfield::collapsedNever; $filesOpener->addTag(MODULE_NAME); $filesOpener->save(); // Create: Close wrapper for Favicon Folder Name $filesCloser = new Field(); $filesCloser->type = new FieldtypeFieldsetClose(); $filesCloser->name = 'fmOpenFolderName' . FieldtypeFieldsetOpen::fieldsetCloseIdentifier; $filesCloser->label = "Close open fieldset"; $filesCloser->addTag(MODULE_NAME); $filesCloser->save(); // Create Favicon Folder Name $fmField = new Field(); $fmField->type = $this->modules->get("FieldtypeText"); $fmField->name = 'folderName'; $fmField->label = 'Favicon Folder:'; $fmField->description = $this->config->urls->files; $fmField->placeholder = 'Destination Folder for your generated favicons, webmanifest and browserconfig'; $fmField->columnWidth = 100; $fmField->collapsed = Inputfield::collapsedNever; $fmField->setIcon('folder'); $fmField->addTag(MODULE_NAME); $fmField->save(); $fmFieldgroup->add($tabOpener); $fmFieldgroup->add($filesOpener); $fmFieldgroup->add($fmField); $fmFieldgroup->add($filesCloser); $fmFieldgroup->add($tabCloser); $fmFieldgroup->save(); /////////////////////////////////////////////////////////////// // Experimental Markup Tests $wrapperFaviconMagic = new InputfieldWrapper(); $wrapperFaviconMagic->attr('id','faviconMagicWrapper'); $wrapperFaviconMagic->attr('title',$this->_('Favicon Magic')); // field show info what $field = $this->modules->get('InputfieldMarkup'); $field->name = 'use'; $field->label = __('How do I use it?'); $field->collapsed = Inputfield::collapsedNever; $field->icon('info'); $field->attr('value', 'Does this even begin to vaguely work?'); $field->columnWidth = 50; $wrapperFaviconMagic->add($field); $fmTemplate->fields->add($wrapperFaviconMagic); $fmTemplate->fields->save(); ///////////////////////////////////////////////////////////// // Create page $page = $this->wire( new Page() ); $page->template = MODULE_NAME; $page->parent = $this->wire('pages')->get('/'); $page->addStatus(Page::statusHidden); $page->title = 'Favicons'; $page->name = self::PAGE_NAME; $page->process = $this; $page->save(); } }  
    • By marcus
      wireshell 1.0.0 is out    
      See Bea's post
       


      -------- Original post -----------
        Now this one could be a rather long post about only an experimental niche tool, but maybe a helpful one for some, so stay with me   Intention Do you guys know "Artisan" (Laravel) or "Drush" (Drupal)? If not: These are command line companions for said systems, and very useful for running certain (e.g. maintenance, installation) task quickly - without having to use the Admin Interface, first and foremost when dealing with local ProcessWire installations. And since it has a powerful API and an easy way of being bootstrapped into CLIs like this, I think such a tool has a certain potential in the PW universe.    It's totally not the first approach of this kind. But: this one should be easily extendable - and is based on PHP (specifically: the Console component of the Symfony Framework). Every command is tidily wrapped in its own class, dependencies are clearly visible, and so on.   ( Here was the outdated documentation. Please visit wireshell.pw for the current one )
    • By quickjeff
      Hi Guys, 
      I have been debugging a site for the last 2 hours and cannot solve the issue. 
      I have a site running on 3.0.148. 
      I installed the Kongondo Blog module and was updating the templates to include the website style. 
      Once everything was set and done, I checked the page tree to see an error appear. 
      Template must be assigned a name before 'filename' can be accessed
      The same error appears in templates. 
      Debugging Steps
      I checked the templates in the server to ensure I didnt accidentally delete the namespace.  Deleted cache in browser and server under assets Still no go. 
      Any help is appreciated. 
      Thanks! 
×
×
  • Create New...