Is it possible to use null coalescing in a field selector?

[artfulrobot]

I have a template which has a when timestamp field that is optional.

I'd like to fetch pages sorted with ORDER BY COALESCE(when, published) DESC meaning that if there's a value for when then use that, otherwise use published

Is this possible with a selector? At first I thought it might be something like sort=-(when|published) but that doesn't work. It doesn't error, but it doesn't return the correct results. Neither does sort=-when|-published (i tried quite a few other combinations too).

[bernhard]

You can apply any SQL you want by hooking into the PageFinder:

$wire->addHookAfter("PageFinder::getQuery", function (HookEvent $event) {
  // get the DatabaseQuerySelect object
  $query = $event->return;

  // dump the object to tracy
  // https://i.imgur.com/BdDEQU3.png
  bdb($query);

  // modify the query to only find pages with name length < 5
  $query->where("LENGTH(pages.name)<5");

  // remove the hook after first execution
  $event->removeHook(null);
});
bd($pages->find("id>0"));

The idea is to add the hook directly above your $pages->find() call and inside the hook you remove it so that it does not apply to any other $pages->find() operations that might occur later.

This is a DatabaseQuerySelect object:

And with that object you can add any custom ->where() or ->join() or whatever you need.

[artfulrobot]

@bernhard many thanks, really appreciated. I'll have a play!

[7Studio]

Another option could be to create a new page array, add your "when" or "published" timepstamps as an extra value to all elements for sorting purpose.

Finally you can sort pages via PHP native "array_multisort". (This is what I'm usually using when needed custom sorts).

Not tested, but guess it should work - simple function example:

function customOrder() {
	// your selector
	$selector = 'template=events';
	$matches = wire('pages')->find($selector);

	// if we find any matches
	if($matches->count) {
		foreach($matches as $match)
		{
			// assign "myorder" to all items for sorting purpose
			// any logic 
			if ($match->when) {
				$match->myorder = $match->when;
			} else {
				$match->myorder = $match->published;
			}
		}
		
		// temp array
		$array = $matches->getArray();
		
		// sort array by myorder value with PHP array_multisort
		array_multisort( array_column($array, "myorder"), SORT_DESC, $array );
		
		// create and return new PW pages array
		$results = new PageArray();
		$results->import($array);
		
		return $results;
	}
	
	return;
}

 

Edited July 25 by 7Studio

[artfulrobot]

2 hours ago, 7Studio said:

Another option ...  sort pages via PHP

Ah, thank you, but I cannot load all the pages into memory - there's thousands, so this would be inefficient in this instance.

[7Studio]

2 minutes ago, artfulrobot said:

Ah, thank you, but I cannot load all the pages into memory - there's thousands, so this would be inefficient in this instance.

Understand, guess that I'm missing your use case scenario. Above code was meant just to "custom sort" results that you can limit and get with a pw regular find() selector, definitely not for sorting all pages.

[Jan Romero]

I’ve also longed for this feature and took this occasion to spend the day figuring out what it might take to support it in the core…

Unfortunately, the when|published syntax is already supported as a shorthand for sort=when, sort=published, so we can’t use it without causing major breakage.

Putting parens around it throws an exception somewhere deep in the codebase because it’s used for OR-groups. Since that doesn’t make sense in a sort selector, the syntax could probably be used, but I didn’t dare venture that deep, so I invented my own delimiter just for the POC.

It’s /, i.e. you’d go pages()->find('template=person, sort=sortname/lastname|givenname'); and the full query would end up as:

SELECT pages.id,pages.parent_id,pages.templates_id
FROM `pages`  
    LEFT JOIN field_givenname AS _sort_givenname ON _sort_givenname.pages_id=pages.id 
    LEFT JOIN field_sortname AS _sort_sortname ON _sort_sortname.pages_id=pages.id 
    LEFT JOIN field_lastname AS _sort_lastname ON _sort_lastname.pages_id=pages.id 
WHERE pages.templates_id = 69
    AND pages.status < 1024
GROUP BY pages.id  
ORDER BY coalesce(_sort_sortname.data, _sort_lastname.data),_sort_givenname.data

Which I think is a somewhat relatable real-world example (think: surname = “van Aaken”, sortname = “Aaken, van”).

Unfortunately, the mod requires refactoring many lines into a method, so it’s a bit long to post here. But here it is regardless. Go to /wire/core/PageFinder.php and replace the entire function getQuerySortSelector() with this to check it out:

protected function getQuerySortSelector(DatabaseQuerySelect $query, Selector $selector) {

    // $field = is_array($selector->field) ? reset($selector->field) : $selector->field; 
    $values = is_array($selector->value) ? $selector->value : array($selector->value);     
    $fields = $this->fields;
    $pages = $this->pages;
    $database = $this->database;
    $user = $this->wire()->user; 
    $language = $this->languages && $user && $user->language ? $user->language : null;

    // support `sort=a|b|c` in correct order (because orderby prepend used below)
    if(count($values) > 1) $values = array_reverse($values); 
    
    foreach($values as $value) {

        $fc = substr($value, 0, 1); 
        $lc = substr($value, -1);
        $descending = $fc == '-' || $lc == '-';
        $value = trim($value, "-+"); 
        // $terValue = ''; // not currently used, here for future use
        
        if($this->lastOptions['reverseSort']) $descending = !$descending;

        if(!strpos($value, '/')) {
            $value = $this->processSortValue($query, $pages, $database, $fields, $descending, $language, $value);
        } else {
            $coalesce = [];
            foreach (explode('/', $value) as $value) {
                $value = $this->processSortValue($query, $pages, $database, $fields, $descending, $language, $value);
                if(is_string($value) && strlen($value))
                    $coalesce[] = $value;
            }

            if (!count($coalesce))
                continue;

            $value = 'coalesce(' . implode(', ', $coalesce) . ')';
        }

        if(is_string($value) && strlen($value)) {
            if($descending) {
                $query->orderby("$value DESC", true);
            } else {
                $query->orderby("$value", true);
            }
        }
    }
}

private function processSortValue($query, $pages, $database, $fields, $descending, $language, $value) {
    $subValue = '';

    if(strpos($value, ".")) {
        list($value, $subValue) = explode(".", $value, 2); // i.e. some_field.title
        if(strpos($subValue, ".")) {
            list($subValue, $terValue) = explode(".", $subValue, 2);
            $terValue = $this->sanitizer->fieldName($terValue);
            if(strpos($terValue, ".")) $this->syntaxError("$value.$subValue.$terValue not supported");
        }
        $subValue = $this->sanitizer->fieldName($subValue);
    }
    $value = $this->sanitizer->fieldName($value);
    
    if($value == 'parent' && $subValue == 'path') $subValue = 'name'; // path not supported, substitute name

    if($value == 'random') { 
        $value = 'RAND()';

    } else if($value == 'num_children' || $value == 'numChildren' || ($value == 'children' && $subValue == 'count')) {
        // sort by quantity of children
        $value = $this->getQueryNumChildren($query, $this->wire(new SelectorGreaterThan('num_children', "-1")));

    } else if($value == 'parent' && ($subValue == 'num_children' || $subValue == 'numChildren' || $subValue == 'children')) {
        throw new WireException("Sort by parent.num_children is not currently supported");

    } else if($value == 'parent' && (empty($subValue) || $pages->loader()->isNativeColumn($subValue))) {
        // sort by parent native field only
        if(empty($subValue)) $subValue = 'name';
        $subValue = $database->escapeCol($subValue);
        $tableAlias = "_sort_parent_$subValue";
        $query->join("pages AS $tableAlias ON $tableAlias.id=pages.parent_id");
        $value = "$tableAlias.$subValue";

    } else if($value == 'template') { 
        // sort by template
        $tableAlias = $database->escapeTable("_sort_templates" . ($subValue ? "_$subValue" : '')); 
        $query->join("templates AS $tableAlias ON $tableAlias.id=pages.templates_id"); 
        $value = "$tableAlias." . ($subValue ? $database->escapeCol($subValue) : "name"); 

    } else if($fields->isNative($value) && !$subValue && $pages->loader()->isNativeColumn($value)) {
        // sort by a native field (with no subfield)
        if($value == 'name' && $language && !$language->isDefault()  && $this->supportsLanguagePageNames()) {
            // substitute language-specific name field when LanguageSupportPageNames is active and language is not default
            $value = "if(pages.name$language!='', pages.name$language, pages.name)";
        } else {
            $value = "pages." . $database->escapeCol($value);
        }

    } else {
        // sort by custom field, or parent w/custom field
        
        if($value == 'parent') {
            $useParent = true;
            $value = $subValue ? $subValue : 'title'; // needs a custom field, not "name"
            $subValue = 'data';
            $idColumn = 'parent_id';
        } else {
            $useParent = false;
            $idColumn = 'id';
        }
        
        $field = $fields->get($value);
        if(!$field) {
            // unknown field
            return null;
        }
        
        $fieldName = $database->escapeCol($field->name); 
        $subValue = $database->escapeCol($subValue);
        $tableAlias = $useParent ? "_sort_parent_$fieldName" : "_sort_$fieldName";
        if($subValue) $tableAlias .= "_$subValue";
        $table = $database->escapeTable($field->table);
        if($field->type instanceof FieldtypePage) {
            $blankValue = new PageArray();
        } else {
            $blankValue = $field->type->getBlankValue($this->pages->newNullPage(), $field);
        }

        $query->leftjoin("$table AS $tableAlias ON $tableAlias.pages_id=pages.$idColumn");
        
        $customValue = $field->type->getMatchQuerySort($field, $query, $tableAlias, $subValue, $descending);
        
        if(!empty($customValue)) {
            // Fieldtype handled it: boolean true (handled by Fieldtype) or string to add to orderby
            if(is_string($customValue)) $query->orderby($customValue, true);
            $value = false;

        } else if($subValue === 'count') {
            if($this->isRepeaterFieldtype($field->type)) {
                // repeaters have a native count column that can be used for sorting
                $value = "$tableAlias.count";
            } else {
                // sort by quantity of items
                $value = "COUNT($tableAlias.data)";
            }

        } else if(is_object($blankValue) && ($blankValue instanceof PageArray || $blankValue instanceof Page)) {
            // If it's a FieldtypePage, then data isn't worth sorting on because it just contains an ID to the page
            // so we also join the page and sort on it's name instead of the field's "data" field.
            if(!$subValue) $subValue = 'name';
            $tableAlias2 = "_sort_" . ($useParent ? 'parent' : 'page') . "_$fieldName" . ($subValue ? "_$subValue" : '');
        
            if($this->fields->isNative($subValue) && $pages->loader()->isNativeColumn($subValue)) {
                $query->leftjoin("pages AS $tableAlias2 ON $tableAlias.data=$tableAlias2.$idColumn");
                $value = "$tableAlias2.$subValue";
                if($subValue == 'name' && $language && !$language->isDefault() && $this->supportsLanguagePageNames()) {
                    // append language ID to 'name' when performing sorts within another language and LanguageSupportPageNames in place
                    $value = "if($value$language!='', $value$language, $value)";
                }
            } else if($subValue == 'parent') {
                $query->leftjoin("pages AS $tableAlias2 ON $tableAlias.data=$tableAlias2.$idColumn");
                $value = "$tableAlias2.name";

            } else {
                $subValueField = $this->fields->get($subValue);
                if($subValueField) {
                    $subValueTable = $database->escapeTable($subValueField->getTable());
                    $query->leftjoin("$subValueTable AS $tableAlias2 ON $tableAlias.data=$tableAlias2.pages_id");
                    $value = "$tableAlias2.data";
                    if($language && !$language->isDefault() && $subValueField->type instanceof FieldtypeLanguageInterface) {
                        // append language id to data, i.e. "data1234"
                        $value .= $language;
                    }
                } else {
                    // error: unknown field
                }
            }
            
        } else if(!$subValue && $language && !$language->isDefault() && $field->type instanceof FieldtypeLanguageInterface) {
            // multi-language field, sort by the language version
            $value = "if($tableAlias.data$language != '', $tableAlias.data$language, $tableAlias.data)";
            
        } else {
            // regular field, just sort by data column
            $value = "$tableAlias." . ($subValue ? $subValue : "data"); ; 
        }
    }
    return $value;
}

 

Almost all of this is original PW code. The only changes are:

• The method processSortValue() was inside getQuerySortSelector() before. I think I only added the return at the end and changed a continue to a return in the middle.
• Of course, the bit where it goes if(!strpos($value, '/')) is new.

The method had to be factored out because it assumes a single “value”, but since we’re injecting multiple values into a single value by our fabricated ”/“ delimiter, we want to call it in our own loop. It figures out what joins to add to the query and transforms the original value from the selector into the corresponding SQL expression (at one point it transforms “random” into “RAND()”, to name a simple example that doesn’t mutate anything outside the method).

I have probably missed all kinds of things and introducing yet another arcane operator to selectors may not be a great idea, but my site seems to be running fine with this hack, and it seems to work.

Edited July 25 by Jan Romero

[artfulrobot]

@Jan Romero

 wow thanks for your work and for sharing it! Interesting to see what is needed to accommodate something like this.

Re yet more on the selector DSL-  yes. It fascinates me that it's possible to do so much with simple text strings and not trip up. I would love to see unit tests in PW covering these cases - this would mean the format could be developed with more confidence that the new work hadn't broken the existing logic.