Am having some confusion as to the best strategy for resolving a peculiar issue with an A-Z index style of listing page.

In the controller:

class Directory_Controller
	public function AlphabeticalIndex() {
	    return DataObject::get('Listing', ... , 'Name ASC')->GroupedBy('FirstLetter');
	}

class Listing
	public function getFirstLetter() {
	    return $this->Name[0];
	}

This provides exactly the data structure I want in the template, but in order to have a sensible order of listings, without the 'T' section being much longer than all the others, I have to remove listings starting with 'The' from the first letter check:

	public function getFirstLetter() {
	    return (substr($this->Name, 0, 4) == 'The ') ? $this->Name[4] : $this->Name[0];
	}

Okay, great. This is working perfectly, but for one weird issue with the sort order of the listings.

Because the ASC sorting is done on the query itself, by the time the grouping operation happens, the order doesn’t change (and I think you can understand why I am less than enthusiastic about doing this operation in SQL, considering how much slower and more annoying the substring checks would be at the DB level). This seems to be fine if there are already listings under a letter index, but if not, then listings starting with 'The' get thrown to the end of the list.

eg:

E
 - Equitycorp
 - The E Company
G
 - Goldcorp
 - The G Company

But remove one of those entries, and it ends up out of order, like:

G
- Goldcorp
- The G Company
E
- The E Company

What is the best way to get around this? Can anyone point me to a kind of best practice guide for dealing with 'stop words' in human readable indexes/titles. Personally, I much prefer having the user see 'The E Company' rather than 'E Company, The', but I’d like to get a better idea of how others have approached this situation.

A-Z index pages are not an uncommon use case (at least for certain kinds of sites), but I'm not sure that too many people would have the whole 'The' issue, so maybe this is an outlier.

For now, I’ve patched DataObjectSet->groupBy(), to ksort() the results before giving the list back to the DataObjectSet->GroupedBy()

A potential better way (I say potential as it's a slightly hacky) to easily have it in SS would be to have another column for doing the order by in the database. You could use onBeforeWrite() to strip off any prefixes you don't want then store that in a column and that would be what you sort it on. Would be the fastest method for reading the data.

static $db = array(
'Title' => 'Varchar'
'SortTitle' => 'Varchar'
);

..
function onBeforeWrite() {
parent::onBeforeWrite();
if($this->Title) {
$this->SortTitle = (substr($this->Title, 0, 4) == 'The ') ? substring($this->Title, 4, 0) : $this->Title;
}
}

...
$listings = DataObject::get('Listing', '', 'SortTitle ASC');

Not really ideal but then at least you're doing the sort on the database end which would be much faster than PHP. Not sure how much of a factor that would be for you.

Thanks Will, I think that could be the best option, given the alternatives (substr in SQL query or changing the DObjSet API by complexifying the arguments to GroupedBy()) are probably more hacky.

The advantage of using a separate field is that it can use a native database index and be queried very easily. Only disadvantage I can see is the bulk/bloat and denormalization from adding additional fields, but that’s unlikely to be a problem in most cases.

A custom substr/as with SQLQuery would probably be the most robust way (though the actual select would look quite horrible). You would run into issue with the dual columns if you imported articles using direct INSERT queries but then again that's an edge case.

Another issue I ran into was to do with the way that grouped sets are constructed. Because of the way the index of objects in the set is built, it doesn’t seem possible to use the regular API calls in the nested control block:

<span>$AlphabeticalIndex.Pos $AlphabeticalIndex.Count (works)</span>

<% control AlphabeticalIndex %>
    <div class="directory">
	  <h3>$FirstLetter (works)</h3>
          <p>$Pos $Count (doesn't work)</p>
		
	  <% control Children %>
		<div class="listing"><a href="/directory/$URLSegment/">$Name</a></h5></div>
	  <% end_control %>
    </div>

In the current (2.4.5) install, using the code I listed above, results in the $Pos and $Count variables being empty inside the control loop of this grouped set.

To get this template fragment to work, I had to make some changes to the DataObjectSet. It doesn’t seem to have broken anything, but I’d be curious to know the rationale for the existing code.

https://github.com/maetl/sapphire/commit/819536c2765cdf66c98b02bb118ae89551cc18da

I added a brief unit test for the GroupedBy functionality... but weirdly, it passes fine in both versions of the GroupBy method, so doesn’t seem to suffer from the same issue calling Pos/Modulus/etc in the foreach loop as it does in the template.

https://github.com/maetl/sapphire/commit/ee56f81dbde60802c73f8916cd18742f9c6f035c