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, Ed, biapar, Willr, Ingo, swaiba

Transforming a SQLQuery() result to DataObjectSet


Go to End
Reply


4 Posts   5219 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, 163 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!!!!!!!!!