Substr in find()

[Pete]

Hey folks

I'm pretty sure it's not possible, but is there a way to mimic mySQL's SUBSTR() function using a selector in ProcessWire?

I've created fields for forename and surname and added them to the users template, but I'd love to be able to get unique initials for the surname so I can have a list of A-Z links linking to pages containing surnames that start with that letter.

I know there's a longer-winded way of doing it simply by iterating through all users but wondered if there was a quicker way?

The mySQL way is as follows in case anyone was interested, and it is lightning fast (0.0006 seconds):

SELECT DISTINCT SUBSTRING(surname,1,1) AS letter FROM yourtable

As much as 0.0006 seconds is nice, I'm less worried about speed but just don't want to load all users into memory just for this since it will add a chunk of overhead as more users are added

I also realise I could just create pages for all letters with links and display a "no users found" message on pages with no users whose surname begins with that letter, however I like to be neat and not have a link on the letters where I don't need to

[Pete]

Thinking about it, this could just be a case for MarkupCache, then it doesn't matter so much if I do it the longer way, or even saving something to a cache file on each user save maybe. Seems there are other options, though some more selector functionality would fall under the category of "nice to have"

[SiNNuT]

If you make sure your surname field is only used for users couldn't you just go with a direct sql query in your template?

$results = $db->query("SELECT DISTINCT SUBSTRING(surname,1,1) AS letter FROM surnametable");

 

[Pete]

I could, I just didn't want to do it that way. Just me being awkward

[ryan]

Nothing will be as fast as going straight to a dedicated targeted SQL query like that. But if you wanted to use PW selectors, this is still quite fast:

for($n = ord('A'); $n <= ord('Z'); $n++) {
  $letter = chr($n); 
  $cnt = $pages->count("surname^=$letter"); 
  echo "<li>$letter - $cnt people</li>";
}