Silverstripe 3 SQL Queries

You will occasionally need to to perform custom SQL queries in Silverstripe 3 and return this data for display in your template rather than just returning a standard DataObject. Rather than just writing raw SQL, you need to build your SQL query using the SQLQuery class and execute the query:

1
2
3
4
5
6
7
$sqlQuery = new SQLQuery();
$sqlQuery->setFrom('Source_Table'); //the name of the table you want to select data from
$sqlQuery->selectField('Title, Content, Other_Field'); //the fields you want to return
$sqlQuery->addLeftJoin('Table\_To\_Join', '"Source\_Table"."Relation\_Column" = "Table\_To\_Join"."Relation_Column"'); //any joining tables
//other joins can also be specified like the left join example above
$sqlQuery->addWhere('"Column" = "Value"'); //add a where clause
$result = $sqlQuery->execute(); //execute the SQL

Now you have your result, you need to create a new ArrayList and into it, push the results from the SQL query to return to your template:

1
2
3
4
$returnedRecords = new ArrayList();
foreach($result as $row) {
$returnedRecords->push(new ArrayData($row));
}

Full example: YourClass.php

1
2
3
4
5
6
7
8
9
10
11
12
13
function getResults(){
$sqlQuery = new SQLQuery();
$sqlQuery->setFrom('Source_Table');
$sqlQuery->selectField('Title, Content, Other_Field');
$sqlQuery->addLeftJoin('Table\_To\_Join', '"Source\_Table"."Relation\_Column" = "Table\_To\_Join"."Relation_Column"');
$sqlQuery->addWhere('"Column" = "Value"');
$result = $sqlQuery->execute();
$returnedRecords = new ArrayList();
foreach($result as $row) {
$returnedRecords->push(new ArrayData($row));
}
return $returnedRecords;
}

And loop through your results like so: YourClass.ss

1
2
3
4
5
<% loop Results %>
$Title
$Content
$etc
<% end_loop %>