Jump to:

23490 Posts in 18996 Topics by 2878 members

General Questions

SilverStripe Forums » General Questions » Grouping Output (DataObjects) pt. 2 [SOLVED]

General questions about getting started with SilverStripe that don't fit in any of the categories above.

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

Page: 1
Go to End
Author Topic: 3365 Views
  • socks
    Avatar
    Community Member
    190 Posts

    Grouping Output (DataObjects) pt. 2 [SOLVED] Link to this post

    I have a StoreLocation DataObject. I want to list all stores grouped by State then by City.

    I borrowed code from this post: http://silverstripe.org/dataobjectmanager-module-forum/show/267505#post267505 and that got me halfway there.

    The page is properly grouping by State (and only listing each state one time), but I now need to get it to group by City as well. (See Screenshot)

    This is what I have so far (just a direct copy from other post)

    class StoreLocationsPage_Controller extends Page_Controller {
       
       function Locations() {
             $States = new DataObjectSet();
             $results = DB::query("SELECT DISTINCT State FROM StoreLocation ORDER BY State ASC");
             if($results) {
                foreach($results as $result) {
                   $state = $result['State'];
                   $States->push(new ArrayData(array(
                      'State' => $state,
                      'Locations' => DataObject::get("StoreLocation","State = '$state'", 'Name')
                   )));
                }
                return $States;   
             }

             return false;
          }

    }

    Any help is much appreciated, Thanks.

  • zenmonkey
    Avatar
    Community Member
    528 Posts

    Re: Grouping Output (DataObjects) pt. 2 [SOLVED] Link to this post

    I needed to do the same thing with with initial code from UncleCheese. But he got me halfway there. Took a bit of tinkering but I was able to sort it out, you need to create functions within the initial function that pass each level of the sort onto the next one. This probably isn't the most elegant solution but you should be able to re-apply it to your data

    Attached Files
  • dalesaurus
    Avatar
    Community Member
    283 Posts

    Re: Grouping Output (DataObjects) pt. 2 [SOLVED] Link to this post

    Sounds like you just need to tighten up your SQL game on for this one. Show me some table structures and lets get dirty with SQLQuery.

  • socks
    Avatar
    Community Member
    190 Posts

    Re: Grouping Output (DataObjects) pt. 2 [SOLVED] Link to this post

    Hello dalesaurus,

    I haven't tried to adapt zenmonkey's code to my scenario yet, but I'm sure the both of us would love to see one simplified function.

    I did try

    $results = DB::query("SELECT DISTINCT State, City FROM StoreLocation GROUP BY State, City ORDER BY State");

    If that is somehow correct, then I don't know how to do the foreach loop or maybe my template is wrong.

    I included a screenshot of the StoreLocation dataobject / table in the database. I hope that's what you meant (I'm not as well versed as I'd like to be in PHP and MySQL).

    thanks

    Attached Files
  • ajshort
    Avatar
    Community Member
    244 Posts

    Re: Grouping Output (DataObjects) pt. 2 [SOLVED] Link to this post

    Actually a better solution that looking behind the ORM is just to use a plain DataObject::get() call to get all the objects, then call DataObjectSet->GroupedBy() in your template to create the effect you want:

    Getter Method in your Controller/Model:

    public function Stores() {
       return DataObject::get('Stores');
    }

    Template Code:

    <% control Stores.GroupedBy(State) %>
       ...
       <% control Children.GroupedBy(City) %>
          ...
       <% end_control %>
       ...
    <% end_control %>

  • socks
    Avatar
    Community Member
    190 Posts

    Re: Grouping Output (DataObjects) pt. 2 [SOLVED] Link to this post

    Hey ajshort,

    The function & template code you suggested were great, but it was only returning 1 Store per City (so in my example City1, Oklahoma only returned 1 out of the 3 Locations in that city). Are their any more examples of template code like this in SS documentation somewhere (for future reference)?

    I played around and came up with this... it works

    <% control Stores.GroupedBy(State) %>
       <h1>$State</h1>
    <% control Children.GroupedBy(City) %>
          <h2>$City</h2>      
          <% control Children %>
             <h3>Store $Name</h3>
             $Address<br/>$City, $State $Zip
          <% end_control %>
       <% end_control %>
    <% end_control %>

    I also added some sorting on State, City, & Zip Code...

    public function Stores() {
    return DataObject::get('StoreLocation', '', 'State, City, Zip');
    }

    So unless anyone has any objections, I shall excuse myself and go do a happy dance.

    Thanks to all!

    Attached Files
    3365 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.