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.

Data Model Questions /

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

Custom sql query and template [solved]

Go to End

3 Posts   2022 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