[SOLVED] How to sort pages on top when the sort date field is empty?

[PWaddict]

I would like to sort pages of specific template with a date field. The problem is that the date field might not always be filled by the editor cause he might not know yet what date to set. So, all the pages with a date should be sorted by it and those that have empty dates should sort on top. Setting the date field on the template's "Sort settings for children" sorts pages with empty dates on bottom.

[kixe]

I use a hook for something similar
 

    /** CUSTOM PAGE SORT
     * related for InputfieldPageListSelect, ProcessPageList (Page tree)
     * 
     */
    $wire->addHookBefore('ProcessPageList::find', function($e) {
        $parent = $e->arguments(1);
        $selectorString = $e->arguments(0);
        // modify $selectorString, remove, modify, add ',sort= ...'
        $e->arguments(0, $selectorString);
    });

 

[PWaddict]

4 hours ago, kixe said:

I use a hook for something similar
 

    /** CUSTOM PAGE SORT
     * related for InputfieldPageListSelect, ProcessPageList (Page tree)
     * 
     */
    $wire->addHookBefore('ProcessPageList::find', function($e) {
        $parent = $e->arguments(1);
        $selectorString = $e->arguments(0);
        // modify $selectorString, remove, modify, add ',sort= ...'
        $e->arguments(0, $selectorString);
    });

 

Thanks for your reply but I don't understand how to sort the pages with empty date field on top...

[LostKobrakai]

In MySQL `NULL` is always less then any non-null value. Therefore you either need to sort at runtime, somehow make the sql query issued sort by `ISNULL(col), col` with different directions or use my paginator module for paginating though multiple different selectors: https://github.com/LostKobrakai/Paginator

[PWaddict]

2 minutes ago, LostKobrakai said:

In MySQL `NULL` is always less then any non-null value. Therefore you either need to sort at runtime, somehow make the sql query issued sort by `ISNULL(col), col` with different directions or use my paginator module for paginating though multiple different selectors: https://github.com/LostKobrakai/Paginator

With the above hook that @kixe posted I can add multiple selectors too. I'm using 1 date field to sort pages by date but if the date is empty I want that page to be sorted on top. What's the proper selector for that???

[PWaddict]

I've created a checkbox field named "nodate" and I checked it on a page with empty date field. Then on the above hook added the sort like this:

sort=-nodate, sort=-date

It seems to be working properly. Now I have to make the nodate checkbox field hidden and automatically checked on save when the editor leaves empty the date field...

[kixe]

I think you don't need a workaround with a hidden field if you do the hook after and change the PageArray instead of the selector.

    $wire->addHookAfter('ProcessPageList::find', function($e) {
        $pages = $e->return;
        if ($pages->first()->parent->id != 111111) return; // quick exit if parent doesn't match
        $zeroDatePages = $pages->find("date=''"); // empty date pages
        $result = $pages->filter('date>0,sort=-date'); // pages with date, sort descending
        $result->prepend($zeroDatePages); // prepend empty date pages
        $e->return = $result;
    });

 

 

[PWaddict]

12 minutes ago, kixe said:

I think you don't need a workaround with a hidden field if you do the hook after and change the PageArray instead of the selector.

    $wire->addHookAfter('ProcessPageList::find', function($e) {
        $pages = $e->return;
        if ($pages->first()->parent->id != 111111) return; // quick exit if parent doesn't match
        $zeroDatePages = $pages->find("date=''"); // empty date pages
        $result = $pages->filter('date>0,sort=-date'); // pages with date, sort descending
        $result->prepend($zeroDatePages); // prepend empty date pages
        $e->return = $result;
    });

 

 

That works great on backend but on frontend I have to change the code on various pages to get the same results. I think I'm gonna do the hidden field method where I only have to change the sort. Thanks for the help.

[Robin S]

For anyone interested in how you might do this by modifying the SQL query that's created from a PW selector, here is one way:

In your template file or wherever:

// A field name is supplied in a custom "empty_first" item in the $options array
// The field name supplied here must also be used as a sort value in the selector string
$items = $pages->find('template=news_item, sort=-date_1, limit=20', ['empty_first' => 'date_1']);

In /site/ready.php:

$wire->addHookAfter('PageFinder::getQuery', function(HookEvent $event) {
	$options = $event->arguments(1);
	// Return early if there is no "empty_first" item in the $options array
	if(!isset($options['empty_first'])) return;
	// Get the DatabaseQuerySelect object
	/** @var DatabaseQuerySelect $dqs */
	$dqs = $event->return;
	// Prepend a custom ORDER BY
	$dqs->orderBy("ISNULL(_sort_{$options['empty_first']}.data) DESC", true);
	// Return the modified query
	$event->return = $dqs;
});