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