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!!!

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!!!!!