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:

$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:

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

Full example:

YourClass.php

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

<% loop Results %>
	$Title
	$Content
	$etc
<% end_loop %>
Did you like this post? Why not subscribe?

One thought on “Silverstripe 3 SQL Queries

Leave a Reply

Your email address will not be published. Required fields are marked *