Tag Archives: silverstripe

Silverstripe 3 SQL Queries

Posted on
1

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

Thumbnails in Silverstripe 3 GridField Summaries

Posted on
3

Often, you will use Silverstripe 3 GridFields to handle images and will want to show image thumbnails in the summary view rather than have to give every image a title that you can later refer to when you need to edit the image. I have managed to achieve this fairly simply using the below code snippet which will give you something looking like this:

Silverstripe 3 GridField summary with Thumbnails

The important parts of the snippet are the ‘Thumbnail’ item in the the summary_fields static and the getThumbnail function:

<?php
class HeroImage extends DataObject{
	public static $db = array(
		'Title' => 'Varchar(255)',
  		'SortOrder' => 'Int'
 	);
 
 	static $has_one = array( 
		'HomePage' => 'HomePage',
		'HeroImage' => 'Image'
	);
 
 	public static $summary_fields = array(
  		'Thumbnail'=>'Thumbnail',
  		'Title' => 'Title'
 	);
 
 	public function getThumbnail() { 
		if ($Image = $this->HeroImage()->ID) { 
			return $this->HeroImage()->SetWidth(80); 
		} else { 
			return '(No Image)'; 
		} 
	}
 
 	public static $default_sort='SortOrder';
 
 	public function getCMSFields() {
	  	return new FieldList(
	   		new TextField('Title', 'Title'),
	   		new UploadField('HeroImage', 'Image')
	  	);
	}
}

A fairly short post but if you have any questions about anything on here, post them in the comments below or you can contact me and I will get back to you with an answer.

Silverstripe 3 GridField with many_many relationship

Posted on
12

After getting used to the DataObjectManager in Silverstripe 2.4 and below I’ve been trying to achieve similar functionality using the new GridFields in Silverstripe 3. A fairly common situation I encounter is the need to handle many to many relationships. In the below example, I have Tour Pages, which can have many Activities associated with them and an Activity can also be associated with many Tour Pages.

We create the activity class:

MySite/Code/Activity.php

class Activity extends DataObject{
	public static $db = array(
		'Title' => 'Varchar(255)',
  		'Summary' => 'HTMLText'
 	);
 
 	static $belongs_many_many = array( 
		'TourPages' => 'TourPage' 
	);
 
 	public static $summary_fields = array(
  		'Title' => 'Title'
 	);
 
 	public function getCMSFields_forPopup() {
	  	return new FieldList(
	   		new TextField('Title', 'Title'),
	   		new HTMLEditorField('Summary', 'Brief Summary'),
	  	);
	}
}

We now need to add the relationship to the tour page and create the GridField: Note the unlinkrelation delete action. There was a change to the codebase in the last 7 days or so (at date of post) which changed the action of the delete button in GridFields to actually delete the item rather than unlink the relation. This adds in the unlink relation button so it will not affect any of your other relations.

MySite/Code/TourPage.php

static $many_many = array(
	'Activities' => 'Activity'
);
 
function getCMSFields() {
	$fields = parent::getCMSFields();
 
	...
	$gridFieldConfig = GridFieldConfig_RelationEditor::create()->addComponents(
		new GridFieldDeleteAction('unlinkrelation')
	);
 
	$gridField = new GridField("Activities", "Activities", $this->Activities(), $gridFieldConfig);
 
	$fields->addFieldToTab("Root.Activities", $gridField);
 
	return $fields;
}

Once you have run a dev/build you will have something similar to the below.

You may have noticed the “Allow Drag and Drop” link to allow you to order these.

To allow Drag and Drop reordering, you will need to download the SortableGridField module and run a dev/build/flush=all. You will also need to add a few additional lines of code to the Tour Page.

Firstly you need to add a many_many_extraFields static to add an extra row to the linking table to store the sort order. In my example:

static $many_many_extraFields = array( 
	'Activities' => array( 
		'SortOrder' => "Int" 
	)
}

Finally add the GridFieldSortableRows to your GridField config:

$gridFieldConfig = GridFieldConfig_RelationEditor::create()->addComponents(
	new GridFieldDeleteAction('unlinkrelation'),
	new GridFieldSortableRows('SortOrder') //add to allow sorting of many_many's
);

You will also need to sort the GridField based on the sort order field created above when you are creating it:

$gridField = new GridField("Activities", "Activities", $this->Activities()->sort('SortOrder'), $gridFieldConfig);

So the final code looks like this:

MySite/Code/Activity.php

class Activity extends DataObject{
	public static $db = array(
		'Title' => 'Varchar(255)',
  		'Summary' => 'HTMLText'
 	);
 
 	static $belongs_many_many = array( 
		'TourPages' => 'TourPage' 
	);
 
 	public static $summary_fields = array(
  		'Title' => 'Title'
 	);
 
 
 	public function getCMSFields_forPopup() {
	  	return new FieldList(
	   		new TextField('Title', 'Title'),
	   		new HTMLEditorField('Summary', 'Brief Summary'),
	  	);
	}
}

MySite/Code/TourPage.php

static $many_many = array(
	'Activities' => 'Activity'
);
static $many_many_extraFields = array( 
        'Activities' => array( 
                'SortOrder' => "Int" 
        )
);
 
function getCMSFields() {
	$fields = parent::getCMSFields();
 
	...
	$gridFieldConfig = GridFieldConfig_RelationEditor::create()->addComponents(
		new GridFieldDeleteAction('unlinkrelation'),
                new GridFieldSortableRows('SortOrder') //add to allow sorting of many_many's
	);
 
	$gridField = new GridField("Activities", "Activities", $this->Activities()->sort('SortOrder'), $gridFieldConfig);
 
	$fields->addFieldToTab("Root.Activities", $gridField);
 
	return $fields;
}