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

Custom sql query and template [solved]


Reply

3 Posts   1619 Views

Avatar
rob.s

23 November 2010 at 8:48pm Community Member, 78 Posts

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

Avatar
rob.s

23 November 2010 at 9:43pm (Last edited: 23 November 2010 9:44pm), Community Member, 78 Posts

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

Avatar
Bambii7

12 August 2011 at 12:32pm Community Member, 254 Posts

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