Jump to:

3428 Posts in 1056 Topics by 733 members

Data Model Questions

SilverStripe Forums » Data Model Questions » Help with SQLQuery

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

Page: 1
Go to End
Author Topic: 997 Views
  • redactuk
    Avatar
    Community Member
    115 Posts

    Help with SQLQuery Link to this post

    I need to quickly and easily retrieve a value, so I've followed:

    http://doc.silverstripe.org/sapphire/en/reference/sqlquery

    and written a function:

       public function ResourceName()
       {

          $sqlQuery = new SQLQuery();
          $sqlQuery->select = array("
             EventResource.Title AS ResourceName
          ");
          $sqlQuery->from = array ("
              CalendarDateTime
             LEFT JOIN CalendarDateTime_Resources ON CalendarDateTime_Resources.CalendarDateTimeID = CalendarDateTime.ID
             LEFT JOIN EventResource ON EventResource.ID = CalendarDateTime_Resources.EventResourceID
          ");
          $sqlQuery->where = array ("
              CalendarDateTime.EventID = " . $this->Event()->ID . "
          ");

          $result = $sqlQuery->execute();
          
          //Debug::show($result);
          return $result;
          
       }

    The query returns the correct value via phpmyadmin, yet the Debug result above displays:

    MySQLQuery::__set_state(array( 'database' => MySQLDatabase::__set_state(array( 'dbConn' => NULL, 'active' => true, 'database' => 'reddemo_cms', 'supportsTransactions' => false, 'supressOutput' => false, 'tableList' => NULL, 'fieldList' => NULL, 'indexList' => NULL, 'schemaUpdateTransaction' => NULL, )), 'handle' => NULL, 'currentRecord' => NULL, 'rowNum' => -1, 'queryHasBegun' => false, ))

    What am I doing wrong?

  • rob.s
    Avatar
    Community Member
    78 Posts

    Re: Help with SQLQuery Link to this post

    Hi redactuc,

    try the following syntax:

    public function ResourceName()
    {

    $sqlQuery = new SQLQuery();
    $sqlQuery->select("EventResource.Title AS ResourceName");
    $sqlQuery->from("CalendarDateTime");
        $sqlQuery->leftJoin("CalendarDateTime_Resources", "CalendarDateTime_Resources.CalendarDateTimeID = CalendarDateTime.ID");
        $sqlQuery->leftJoin("EventResource", "EventResource.ID = CalendarDateTime_Resources.EventResourceID");
    $sqlQuery->where("CalendarDateTime.EventID = " . $this->Event()->ID );

    $result = $sqlQuery->execute();

    //Debug::show($result);
    return $result;

    }

  • redactuk
    Avatar
    Community Member
    115 Posts

    Re: Help with SQLQuery Link to this post

    Thanks for your reply, but that did not make any difference. Solution was to add:

       foreach($result as $row){
        $dataResult = $row['ResourceName'];
       }

    I guess I don't quite understan how SQLQuery works and why that was necessary, but at least it worked.

  • rob.s
    Avatar
    Community Member
    78 Posts

    Re: Help with SQLQuery Link to this post

    OK, i didn's recognize, that the function should return the value of the sql-query.

    Take a look at the documentation:
    http://api.silverstripe.org/2.4/sapphire/model/SQLQuery.html

    In your case you could do as follows:

    ...
    if( $result = $sqlQuery->execute() ) {
    return $result->value(); //   Returns the first column of the first record
    } else {
    return false;
    }

    Greetings,
    Robert
    ...

  • xavsio4
    Avatar
    Community Member
    2 Posts

    Re: Help with SQLQuery Link to this post

    Hello, I've reached that point too. I'm using a table outside the CMS. That table is managed by a third party but I still need to display its values within a template.

    So far the query execution is ok because the loop in the template (<% control table %>) iterates as it should. However, I can't find the way to display the values of each rows.

    The function which makes the query ends with "return $result". I guess I should use an array or something to be able to use $field1, $field2, $field3 of my resulting sql into the template.

    Right now, using the field names as is don't display anything.

    Regards,

    xavier

  • rob.s
    Avatar
    Community Member
    78 Posts

    Re: Help with SQLQuery Link to this post

    Hi xavsio4,

    the most simple way (not best performance) could be:
    Iterate through the results an build a DataObjectSet.

    (Untested)

    function MyRecords() {
    //.....
    $results = $query->execute();

    $dos = new DataObjectSet();

    if( $results ) foreach( $results as $row ) {
    $dos->push( new ArrayData($row) );
    }

    return $dos;
    }

    Because the return type is a DataObjectSet you can iterate inside your template

    <% control MyRecords %>
    $Field1<br />
    $Field2<br />
    <% end_control %>

    Further information:

    http://doc.silverstripe.org/sapphire/en/reference/sqlquery

    Greets, RObert

  • xavsio4
    Avatar
    Community Member
    2 Posts

    Re: Help with SQLQuery Link to this post

    Hi Roberts,

    Thank you very much, that solved my problem. Now I can display the values. It works like a charm.

    Thanks again. There should be a repository of such tips somewhere, like a playground or something.

    Many thanks.

    Best Regards,

    xavier

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