Jump to:

3373 Posts in 998 Topics by 712 members

Data Model Questions

SilverStripe Forums » Data Model Questions » Grouped Sorting of DataObjectSet

Moderators: martimiz, Sean, biapar, Willr, Ingo, swaiba, simon_w

Page: 1
Go to End
Author Topic: 1441 Views
  • maetl
    Avatar
    Community Member
    15 Posts

    Grouped Sorting of DataObjectSet Link to this post

    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()

  • Willr
    Avatar
    Forum Moderator
    5464 Posts

    Re: Grouped Sorting of DataObjectSet Link to this post

    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.

  • maetl
    Avatar
    Community Member
    15 Posts

    Re: Grouped Sorting of DataObjectSet Link to this post

    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.

  • Willr
    Avatar
    Forum Moderator
    5464 Posts

    Re: Grouped Sorting of DataObjectSet Link to this post

    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.

  • maetl
    Avatar
    Community Member
    15 Posts

    Re: Grouped Sorting of DataObjectSet Link to this post

    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

    1441 Views
Page: 1
Go to Top

Want to know more about the company that brought you SilverStripe? Then check out SilverStripe.com

Comments on this website? Please give feedback.