Jump to:

23490 Posts in 18996 Topics by 2878 members

General Questions

SilverStripe Forums » General Questions » Transforming a SQLQuery() result to DataObjectSet

General questions about getting started with SilverStripe that don't fit in any of the categories above.

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

Page: 1
Go to End
Author Topic: 1703 Views
  • B-Side
    Avatar
    Community Member
    42 Posts

    Transforming a SQLQuery() result to DataObjectSet Link to this post

    I'm having difficulty returning a DataObjectSet result from an SQLQuery. I've been following the docu page outline and reviewing other suggestions from the forum, but I'm consistently getting errors.

    My function is:

    function OpenhousesSaturday()
    {

       $week_number = date('W');
       $year = date('Y');
       $day = date("Y-m-d", strtotime($year."W".$week_number."6"));

       $sqlQuery = new SQLQuery();

       $sqlQuery->select = array(
    '`Openhouse`.*',
    '`PropertyPage`.*',
       );

    $sqlQuery->from = array("Openhouse", "LEFT JOIN `PropertyPage` ON `Openhouse`.`PropertyPageID` = `PropertyPage`.`ID`");
    $sqlQuery->where = array("'$day' = `Openhouse`.`ViewDate`");

    // 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));
           }
          
    return $dataObject1;
    }

    And I'm getting this error:
    Fatal error: Call to a member function XML_val() on a non-object...

    Can anyone point me in the right direction on this?

    Many thanks,

    Jayne

  • biapar
    Avatar
    Forum Moderator
    435 Posts
  • B-Side
    Avatar
    Community Member
    42 Posts

    Re: Transforming a SQLQuery() result to DataObjectSet Link to this post

    Thanks for that. I went through that and a few other resources and I'm still quite stuck.

  • Willr
    Avatar
    Forum Moderator
    5508 Posts

    Re: Transforming a SQLQuery() result to DataObjectSet Link to this post

    I'm not sure but looking at the 2.4 API the $sqlQuery->from() should only take a string eg 'Openhouse' and you should use $sqlQuery->leftJoin() to add the join.

    I'm not vastly experienced with using custom SQLs but you could rewrite that as a dataobject get you would lose the Openhouse data but as far as I can see you would only want it for the date.

    DataObject::get('PropertyPage', "Openhouse.ViewDate = '$date'", "", "LEFT JOIN Openhouse ON PropertyPage.ID = Openhouse.PropertyPageID");

  • B-Side
    Avatar
    Community Member
    42 Posts

    Re: Transforming a SQLQuery() result to DataObjectSet Link to this post

    Thanks Willr - this site is running 2.3.7, but I'll give that a try. And the reason we're using SQLQuery is that we need to display the Openhouse data as well. It's my first foray into SQLQuery! I'll provide an update later today. Thanks again, Jayne.

  • biapar
    Avatar
    Forum Moderator
    435 Posts

    Re: Transforming a SQLQuery() result to DataObjectSet Link to this post

    I think that with SQLQuery is possible to create a complicate query and thus is not possible with dataobject:get...Is possible to have more examples on SQLQuery use? ( We have already read SQLQuery page doc. I that page there is error on sqlquery syntax )...

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