Jump to:

3373 Posts in 998 Topics by 712 members

Data Model Questions

SilverStripe Forums » Data Model Questions » Custom sql query and template [solved]

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

Page: 1
Go to End
Author Topic: 1510 Views
  • rob.s
    Avatar
    Community Member
    78 Posts

    Custom sql query and template [solved] Link to this post

    Hi,

    i need to fetch data by a custom sql query and assign the records to a template.

    In order to loop through the records inside the template by <% control Records %> i have to traverse the sql result to a DataObjectSet.

    $query = new SQLQuery();

    $query->from('`Process`');
    $query->select("Process.*, History.Title AS HistoryTitle, History.ID AS HistoryID, CONCAT(Member.Surname, ', ', Member.FirstName) AS Fullname, Member.Email");
    $query->leftJoin('ProcessHistory', '(Process.ID=ProcessHistory.ProcessID)');
    $query->leftJoin("History", '(HistoryID = ProcessHistory.HistoryID)');
    $query->leftJoin('Member', '(Process.ClientID=Member.ID)');

    $query->where(
          "History.ID = (SELECT HistoryID FROM ProcessHistory WHERE ProcessID=Process.ID ORDER BY ProcessHistory.ID DESC LIMIT 1)"
          );

    $query->groupby('Process.ID');

    $query->orderby('Process.ID ASC');

    $result = $query->execute();

    $dos = new DataObjectSet();
    if( $result->numRecords() > 0 ) foreach( $result as $row ){
       $dos->push( new ArrayData($row) );
    }

    In this case i have to loop twice through the records.
    The first time inside the controller method and the second time inside the template.

    When the database is growing i think this is not the most effective way.

    I dont't know if buildDataObjectSet() is the solution for me because my custom query return fields that do not exist inside the model.

    Any suggestions/ideas/approaches ?

    Greetings,

    Rob

  • rob.s
    Avatar
    Community Member
    78 Posts

    Re: Custom sql query and template [solved] Link to this post

    Hi,

    i tried the following:

    .....
    $result = $query->execute();

    $dos = singleton('Process')->buildDataObjectSet($result);

    and it works, even though my Model "Process" does not provide properties like "Fullname" (generated in the custom query).

    GREAT !!!!!

  • Bambii7
    Avatar
    Community Member
    254 Posts

    Re: Custom sql query and template [solved] Link to this post

    Woot, just had to say thanks for posting the solution. I was trying to do by new DataObjectSet($result) which sort of worked, but the array's wern't pretty

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