Skip to main content

This site requires you to update your browser. Your browsing experience maybe affected by not having the most up to date version.

General Questions /

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

Grouping Output (DataObjects) pt. 2 [SOLVED]


Reply


6 Posts   3789 Views

Avatar
socks

Community Member, 190 Posts

25 September 2009 at 6:54pm

Edited: 28/09/2009 11:04am

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.

Avatar
zenmonkey

Community Member, 532 Posts

27 September 2009 at 1:32am

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

Avatar
dalesaurus

Community Member, 283 Posts

27 September 2009 at 5:00pm

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.

Avatar
socks

Community Member, 190 Posts

27 September 2009 at 6:43pm

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
Avatar
ajshort

Community Member, 244 Posts

27 September 2009 at 8:54pm

Edited: 27/09/2009 8:54pm

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 %>

Avatar
socks

Community Member, 190 Posts

28 September 2009 at 11:00am

Edited: 28/09/2009 11:01am

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