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've moved the forum!

Please use forum.silverstripe.org for any new questions (announcement).
The forum archive will stick around, but will be read only.

You can also use our Slack channel or StackOverflow to ask for help.
Check out our community overview for more options to contribute.

Data Model Questions /

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

Custom sql query and template [solved]


Go to End


3 Posts   2561 Views

Avatar
rob.s

Community Member, 78 Posts

23 November 2010 at 8:48pm

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

Community Member, 78 Posts

23 November 2010 at 9:43pm

Edited: 23/11/2010 9:44pm

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

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