Jump to:

23003 Posts in 11856 Topics by 2828 members

General Questions

SilverStripe Forums » General Questions » Transforming a SQLQuery() result to DataObjectSet

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: 4437 Views
  • fostahgix
    Avatar
    Community Member
    9 Posts

    Transforming a SQLQuery() result to DataObjectSet Link to this post

    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!

  • fostahgix
    Avatar
    Community Member
    9 Posts

    Re: Transforming a SQLQuery() result to DataObjectSet Link to this post

    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"); }

       }
       
    ?>

  • _Vince
    Avatar
    Community Member
    157 Posts

    Re: Transforming a SQLQuery() result to DataObjectSet Link to this post

    That worked for me. Thank you very much!

  • Herries
    Avatar
    Community Member
    12 Posts

    Re: Transforming a SQLQuery() result to DataObjectSet Link to this post

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

    4437 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.