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.

Archive /

Our old forums are still available as a read-only archive.

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

Silly Question - Converting SQL to DataObject?


Go to End


4 Posts   3330 Views

Avatar
fordy

Community Member, 46 Posts

8 April 2008 at 5:38am

This may be really simple but i have been looking at it to long and my head is about to expode. I am trying to do a left join on a category table to return the category name instead of the MyCategoryID (foriegn key) This is my code:

$sqlQuery = new SQLQuery();
$sqlQuery->select = array(
'v.ID AS vID',
'v.vTitle AS vTitle',
'v.vRef As vRef',
'v.vPosition As vPosition',
'v.vLocation As vLocation',
'v.vSalary As vSalary',
'v.vDescription As vDescription',
'vc.vCategory As Category'
);
$sqlQuery->from = array(" Vacancy v LEFT JOIN VacancyCategory vc ON v.MyCategoryID = vc.ID");

// $sqlQuery->where = "";
// $sqlQuery->having = "";
$sqlQuery->orderby = "vID DESC";
// $sqlQuery->limit = "";
// $sqlQuery->distinct = true;

// get the raw SQL
$rawSQL = $sqlQuery->sql();

// execute and return a Query-object
$result = $sqlQuery->execute();

if i return it, i get:

MySQLQuery::__set_state(array( 'database' => MySQLDatabase::__set_state(array( 'dbConn' => NULL, 'active' => true, 'database' => 'csITA', 'mysqlVersion' => NULL, 'tableList' => NULL, 'fieldList' => NULL, 'indexList' => NULL, 'schemaUpdateTransaction' => NULL, 'extension_instances' => array ( ), 'class' => 'MySQLDatabase', )), 'handle' => NULL, 'currentRecord' => NULL, 'rowNum' => -1, 'extension_instances' => array ( ), 'class' => 'MySQLQuery', ))

DOH! I dont want that.

How do i convert the $result to a Dataobject that i can easily plonk onto the template?

Any help will be REALLY appreciated!!!

Avatar
fordy

Community Member, 46 Posts

9 April 2008 at 12:09am

Any one???

Avatar
fordy

Community Member, 46 Posts

9 April 2008 at 4:57am

BumP :-(

Anyone please??? I have been ripping my hair out for 2 days on this.

I have been trying to work of the documentation but it isnt working for me. http://doc.silverstripe.com/doku.php?id=sqlquery#transforming_a_result_to_dataobjectset

My code is returning results on the Debug::show($results); :

$sqlQuery = new SQLQuery();
$sqlQuery->select = array(
'v.ID AS ID',
'v.vTitle AS vTitle',
'v.vRef As vRef',
'v.vPosition As vPosition',
'v.vLocation As vLocation',
'v.vSalary As vSalary',
'v.vDescription As vDescription',
'vc.vCategory As Category',
/*"if(`Vacancy`.ClassName,`Vacancy`.ClassName, 'Vacancy') As RecordClassName"*/
);
$sqlQuery->from = array(" Vacancy v LEFT JOIN VacancyCategory vc ON v.MyCategoryID = vc.ID");

// $sqlQuery->where = "";
// $sqlQuery->having = "";
$sqlQuery->orderby = "ID DESC";
// $sqlQuery->limit = "";
// $sqlQuery->distinct = true;

// get the raw SQL
$rawSQL = $sqlQuery->sql();

// execute and return a Query-object
$result = $sqlQuery->execute();

$vacancies = new DataObjectSet();

foreach($result as $row) {

$vacancies->push($row);

}

Debug::show($vacancies);

return $vacancies;

But the return $vacancies is returning a blank page!!!!!

Avatar
Sam

Administrator, 690 Posts

14 April 2008 at 5:13pm

Edited: 14/04/2008 5:14pm

The problem is that you weren't using buildDataObjectSet().

You don't necessary need to use buildDataObjectSet, but you will need to build ViewableData objects somehow. The simplest is ArrayData. ArrayData will turn a regular PHP map into a ViewableData object suitable for use in a template.

Change
$vacancies->push($row);

to
$vacancies->push(new ArrayData($row));