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

SS3 - custom query to viewable data throwing error but nothing in logs


Reply


2 Posts   531 Views

Avatar
obj63mc

Community Member, 24 Posts

21 August 2012 at 12:54pm

Hi All,

I am trying to display some specific data in a template and I couldn't get the new ORM to query the data with out erroring out, bug report has been sent on that but when using a custom query it still errors out when converting to some form of ViewableData such as ArrayList or ArrayData. No matter what I do it won't work.

Code is below - any ideas on why this won't work.

public function GetStates() {
      $results = DB::query('SELECT s.ID AS ID, s.Name AS Name, s.HasSpecial AS HasSpecial FROM State AS s JOIN Program AS P ON P.StateID = s.ID GROUP BY s.ID ORDER BY s.Name ASC;');
      $states = array();
      for ($i = 0; $i < $results->numRecords(); $i++) {
   $record = $results->nextRecord();
         $states[] = $record;
      }
      
      $data = new ArrayData($states);
      return $data;
   }

template has -

<% control GetStates %>
<option value="$ID" data-special="$HasSpecial">$Name</option>
<% end_control %>

Avatar
obj63mc

Community Member, 24 Posts

22 August 2012 at 12:52am

Okay I was able to solve this but you have to do something that didn't seem standard or at least when viewing the api docs it doesn't make sense. The api docs state you can simply pass in an array or object to create an ArrayList but really it has to be an array of ArrayData objects... not really clear the way they have it documented...

In the end I was able to do the following -

public function GetStates() {
      $results = DB::query('SELECT s.ID AS ID, s.Name AS Name, s.HasSpecial AS HasSpecial FROM State AS s JOIN Program AS P ON P.StateID = s.ID GROUP BY s.ID ORDER BY s.Name ASC;');
      $states = ArrayList::create();
      for ($i = 0; $i < $results->numRecords(); $i++) {
   $record = $results->nextRecord();
         $states->add(new ArrayData( array('Name' => $record['Name'], 'ID' => $record['ID'], 'HasSpecial' => $record['HasSpecial']) ));
      }
      
      return $states;
   }