Jump to:

17452 Posts in 4473 Topics by 1971 members

Archive

SilverStripe Forums » Archive » Silly Question - Converting SQL to DataObject?

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

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

Page: 1
Go to End
Author Topic: 2638 Views
  • fordy
    Avatar
    Community Member
    46 Posts

    Silly Question - Converting SQL to DataObject? Link to this post

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

  • fordy
    Avatar
    Community Member
    46 Posts

    Re: Silly Question - Converting SQL to DataObject? Link to this post

    Any one???

  • fordy
    Avatar
    Community Member
    46 Posts

    Re: Silly Question - Converting SQL to DataObject? Link to this post

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

  • Sam
    Avatar
    Administrator
    679 Posts

    Re: Silly Question - Converting SQL to DataObject? Link to this post

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

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