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

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By jom
      Hi everyone
      It seems that I don't fully understand the wireTempPath() function and I need some help.
      I use wireTempPath() to create a new location in assets/cache/WireTempDir and than copy a pdf from the assets/files/page folder to the new folder. I want the file to be accessible only for a limited time, that's why I use wireTempPath.
      The file seems to be copied to the right location, but gets deleted right afterwards, according to 
      As mentioned in the topic above, 
      $wireTempDir->setRemove(false); prevents the file to be deleted. But I like the file to be automatically deleted after a few days. So, how can I do that?
      My code so far (everything works, but the automatic removal of the tempDir folder):
      //generate and show download link $folder = time(); // timestamp as temporary folder $maxAge = (int) $settings->options_downloadlink_valid_hours * 3600; //tempDir wants maxAge as seconds $options = array( 'maxAge' => $maxAge ); $wireTempDir = wireTempDir($folder, $options); $wireTempDir->setRemove(false); $src_file = $page->ebook_download->filename; // Create a new directory in ProcessWire's cache dir if(wire('files')->mkdir($wireTempDir, $recursive = true)) { if(wire('files')->copy($src_file, $wireTempDir)){ //get subdirs from tempDir: $pos = strpos($wireTempDir, "WireTempDir"); $subdir = substr($wireTempDir, $pos, 100); $out .= "<p><a href='" . wire('pages')->get('template=passthrough')->httpUrl . "?file=" . $subdir . $page->ebook_download->basename . "' target='_blank'>$page->title</a></p>"; } } I appreciate any ideas - thanks!
      Oliver
    • By VeiJari
      Hello forum, this is my first security related post, so I'm a bit of a newbie.
      I understand that when I have direct front-input from user I should sanitize the input, but how about when I use a secret key for showing a API for a third-party supplier? Should I sanitize the input->get() key?
      I've tested this issue and I tried ?key=<?php echo $page->field; ?> And without adding any sanitization it comes back: /?key=<?php%20echo%20$page->field;%20?>
      So can I rely on this, or should I still use $sanitizer just in case?
       
      Thanks for the help!
    • By EyeDentify
      I have been experimenting with the new $page->meta() method and find it useful.

      Once i figured out that the data i "save" with it is tied to the page where i called the method from.

      So this is not obvious at least not for me in the documentation:
      https://processwire.com/api/ref/page/meta/
       
      So i just wanted to share that revelation with the community so you don´t get as confused as i was.

      Happy Coding Everyone.
    • By louisstephens
      Going through my long quest to get better with ajax and utilizing the api, I have hit yet another roadblock. I currently have a form with an image field (thanks to flydev for getting that sorted), "title" text input, and a select field set to multiple. In my ajax call, I added in:
      tags = $("#select-tags").val(); form_data.append('tags', tags); $.ajax({ type: 'POST', data: form_data, contentType: false, processData: false, url: '/ajax/upload-preview/', success: function(data) { console.log("Woo"); }, error: function(xhr, ajaxOptions, thrownError) { alert(xhr.responseText); } }); And in the ajax template: 
      $tags = $sanitizer->text($_POST['tags']); $image = $sanitizer->text($_POST['image']); $p = new Page(); $p->template = "preview"; $p->parent = $pages->get("/previews/"); $p->name = $title; $p->title = $title; $p->tags = $tags; $p->save(); If I select a "tag" from the select input and submit, it does indeed add it to the Page Reference field in the backend. However, this does not work with an array being passed to it of multiple options.

      So it does appear that my ajax call is trying to submit multiple options, but I am really just unsure how to get these two added in. I saw in other forums posts of add($page) and even add(array()). Do I need to handle this js array differently or do  I need to foreach through the $tags to add it like:
      foreach($tags as $tag) { $p->tags->add($tag); $p->save(); } I tried this approach, but apparently I am still missing something.
       
      Edit:
      I was doing some tweaking, and I know I can split the js array out like:
      for (i = 0, len = tags.length; i < len; i++) { console.log(tags[i]); } However, I am not sure then how to handle the POST in php if I were to split it out.
    • By louisstephens
      I have been messing around with creating pages from ajax requests, and it has gone swimmingly thus far. However, I am really struggling with creating a page and saving an image via ajax. 
      The form:
      <form action="./" role="form" method="post" enctype="multipart/form-data"> <div> <input type="text" id="preview" name="preview" placeholder="Image Title"> </div> <div> <input type="file" id="preview-name" name="preview-name"> </div> <div> <select id="select-tags" name="select-tags"> <?php $tags = $pages->find("template=tag"); ?> <option value="">Select Your Tags</option> <?php foreach ($tags as $tag) : ?> <option value="<?= $tag->name; ?>"><?= $tag->name; ?></option> <?php endforeach; ?> </select> </div> <div> <button type="button" id="submit-preview" name="submit" class="">Upload Images</button> </div> </form>  
      The ajax in my home template:
      $('#submit-preview').click(function(e) { e.preventDefault(); title = $("#preview").val(); image = $("input[name=preview-name]"); console.log(title); console.log(image); data = { title: title, image: image //not sure if this is actually needed }; $.ajax({ type: 'POST', data: data, url: '/development/upload-preview/', success: function(data) { console.log("Woo"); }, error: function(xhr, ajaxOptions, thrownError) { alert(xhr.responseText); } }); }); And finally in my ajax template:
      $imagePath = $config->paths->assets . "files/pdfs/"; //was from an older iteration $title = $sanitizer->text($_POST['title']); $image = $sanitizer->text($_POST['image']); $p = new Page(); $p->template = "preview"; $p->parent = $pages->get("/previews/"); $p->name = $title; $p->title = $title; $p->save(); $p->setOutputFormatting(false); $u = new WireUpload('preview_image'); $u->setMaxFiles(1); $u->setOverwrite(false); $u->setDestinationPath($p->preview_image->path()); $u->setValidExtensions(array('jpg', 'jpeg', 'gif', 'png', 'pdf')); foreach($u->execute() as $filename) { $p->preview_image->add($filename); } $p->save(); I can complete the file upload but just using a simple post to the same page and it it works well, but I was really trying to work out the ajax on this so I could utilize some modals for success on creation (and to keep my templates a little cleaner). When I do run the code I have, a new/blank folder is created under assets, and a new page is created with the correct title entered. However, no image is being processed. I do get a 200 status in my console. I have searched google for help, but everything seems to be slightly off from my needs. If anyone could help point me in the right direction I would greatly appreciate it. 
×
×
  • Create New...