Skip to main content

This site requires you to update your browser. Your browsing experience maybe affected by not having the most up to date version.

We're retiring the forums!

The SilverStripe forums have passed their heyday. They'll stick around, but will be read only. We'd encourage you to get involved in the community via the following channels instead:

Data Model Questions /

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

Custom sql query and template [solved]

Go to End

3 Posts   2279 Views


Community Member, 78 Posts

23 November 2010 at 8:48pm


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

		"History.ID = (SELECT HistoryID FROM ProcessHistory WHERE ProcessID=Process.ID ORDER BY ProcessHistory.ID DESC LIMIT 1)"


$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 ?




Community Member, 78 Posts

23 November 2010 at 9:43pm

Edited: 23/11/2010 9:44pm


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


Community Member, 254 Posts

12 August 2011 at 12:32pm

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