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

Transforming a SQLQuery() result to DataObjectSet


Reply

4 Posts   4608 Views

Avatar
fostahgix

14 February 2009 at 7:51am Community Member, 9 Posts

Hello All,

I am just diving into SilverStripe and I'm Loving it!

My Challenge:
I have an eCommerce cart installed with my silverstripe site and I need to pull data from the store into a SilverStripe template.

Normally this is cake with the DataObjectSet, However I run into two problems:
1. Converting my result to an DataObjectSet is giving me an error. How do I setup the object set to convert from this SQL statement:

   $sqlQuery = new SQLQuery();
      $sqlQuery->select = array(
      'ds_photos.objectID as ProductId',
      'ds_photos.photoName as Url',
      'ds_language_photos.pCaption as Caption');
      $sqlQuery->from = array("ds_photos","LEFT JOIN ds_language_photos ON ds_photos.photoID = ds_language_photos.photoID");
      $sqlQuery->where = array("ds_language_photos.pAlt LIKE '%SIA%'");
   
      // get the raw SQL
      $rawSQL = $sqlQuery->sql();
      
      // execute and return a Query-object
      $result = $sqlQuery->execute();

// let Silverstripe work the magic
$myDataObjectSet = singleton('Player')->buildDataObjectSet($result); //this is the line that is giving me problems as 'Player' is not defined and I'm not sure how to map this out right.

2. Also is there a way to use <% control %> to go through my raw SQL result?

Thanks for your time!

Avatar
fostahgix

14 February 2009 at 9:02am Community Member, 9 Posts

I figured it out,Solution below, however in my template if I try to access my $Variables with $Variable.XML to make it XML safe it strips out all of the data, anyone have an idea why it would do this?

File: tutorial/code/XmlPage.php (template code in /tutorial/templates/XmlPage.ss) Hope this helps someone :)

class XmlPage_Controller extends Page_Controller {
   function allImages()
   {
      
      $sqlQuery = new SQLQuery();
      $sqlQuery->select = array(
      'ds_photos.objectID as ProductId',
      'ds_photos.photoName as Url',
      'ds_language_photos.pCaption as Caption');
      $sqlQuery->from = array("ds_photos","LEFT JOIN ds_language_photos ON ds_photos.photoID = ds_language_photos.photoID");
      $sqlQuery->where = array("ds_language_photos.pAlt LIKE '%SIA%'");
   
      // get the raw SQL
      $rawSQL = $sqlQuery->sql();
      // execute and return a Query-object
      $result = $sqlQuery->execute();
      //setup our blank DataObjectSet to push SQL result data into it.
      $dataObject1 = new DataObjectSet();
      foreach($result as $row) {
         //move the SQL result data to the DataObjectSet
          $dataObject1->push(new ArrayData($row));
      }
      //var_dump($dataObject1->first());
      //customise The data object set to be accessible in my XML template.
      //My template code
      /*
      <?xml version="1.0" encoding="utf-8"?>
         <picturelist base="$BaseHref.ATT">
            <% if Pictures %><% control Pictures %>
            <picture>
            <url>$ProductId</url>
            <url>$Url</url>
            <caption>$Caption</caption>
            </picture><% end_if %>
            <% end_control %>
         </picturelist>
      */
      return $this->customise(array("Pictures" => $dataObject1))->renderWith("XmlPage"); }

   }
   
?>

Avatar
_Vince

20 June 2009 at 2:02pm Community Member, 161 Posts

That worked for me. Thank you very much! :)

Avatar
Herries

5 August 2010 at 6:18pm Community Member, 12 Posts

Your sql query into dataobject method REALLY helped me thanks!!!!!!!!!