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.

We've moved the forum!

Please use forum.silverstripe.org for any new questions (announcement).
The forum archive will stick around, but will be read only.

You can also use our Slack channel or StackOverflow to ask for help.
Check out our community overview for more options to contribute.

General Questions /

General questions about getting started with SilverStripe that don't fit in any of the categories above.

Moderators: martimiz, Sean, Ed, biapar, Willr, Ingo, swaiba

Transforming a SQLQuery() result to DataObjectSet


Go to End


4 Posts   7035 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, 165 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!!!!!!!!!