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   4690 Views

Avatar
fostahgix

Community Member, 9 Posts

14 February 2009 at 7:51am

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

Community Member, 9 Posts

14 February 2009 at 9:02am

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

Community Member, 162 Posts

20 June 2009 at 2:02pm

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

Avatar
Herries

Community Member, 12 Posts

5 August 2010 at 6:18pm

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