Silverstripe Version: 4.3
Question:
I am creating a site with pages containing Projects, Sectors and Technologies. These are each managed by separate custom DataObjects like this:
class Project extends DataObject {
private static $many_many = [
"Channels" => [
'through' => ProjectChannel::class,
'from' => 'Project',
'to' => 'Channel',
],
"Sectors" => [
'through' => ProjectSector::class,
'from' => 'Project',
'to' => 'Sector',
],
"Services" => [
'through' => ProjectService::class,
'from' => 'Project',
'to' => 'Service',
],
"Technologies" => [
'through' => ProjectTechnology::class,
'from' => 'Project',
'to' => 'Technology',
],
];
}
class ProjectSector extends DataObject {
private static $table_name = 'ProjectSector';
private static $db = [
'Order' => 'Int',
];
private static $has_one = [
'Project' => Project::class,
'Sector' => Sector::class,
];
private static $default_sort = '"ProjectSector"."Order" ASC';
}
class Sector extends DataObject {
private static $belongs_many_many = [
'Projects' => Project::class,
];
}
class ProjectTechnology extends DataObject
{
private static $table_name = 'ProjectTechnology';
private static $db = [
'Order' => 'Int',
];
private static $has_one = [
'Project' => Project::class,
'Technology' => Technology::class,
];
private static $default_sort = '"ProjectTechnology"."Order" ASC';
}
class Technology extends DataObject {
private static $belongs_many_many = [
'Projects' => Project::class,
];
}The idea is I can add projects to the admin and easily maintain their relationships to Sectors and Technologies. This all works fine.
On the front end I want to have a Projects page displaying all the Projects with their relevant Technologies and Sectors. That’s OK too, like this:
class ProjectPageController extends PageController {
public function Projects()
{
$projects = Project::get()->filter(array('Featured' => true));
return $projects;
}
}<% loop $Projects %>
<h2 id="$Segment">$Title</h2>
<h3>$SubTitle</h3>
<ul>
<% if $Sectors %>
<li>Sector:
<ul>
<% loop $Sectors %>
<% if $Featured %>
<li><a href="/sectors#{$Segment}">$Title</a></li>
<% else %>
<li>$Title</li>
<% end_if %>
<% end_loop %>
</ul>
</li>
<% end_if %>
<% if $Services %>
<li>Services:
<ul>
<% loop $Services %>
<% if $Featured %>
<li><a href="/services#{$Segment}">$Title</a></li>
<% else %>
<li>$Title</li>
<% end_if %>
<% end_loop %>
</ul>
</li>
<% end_if %>
<% if $Technologies %>
<li>Tech:
<ul>
<% loop $Technologies %>
<% if $Featured %>
<li><a href="/technologies#{$Segment}">$Title</a></li>
<% else %>
<li>$Title</li>
<% end_if %>
<% end_loop %>
</ul>
</li>
<% end_if %>
</ul>However, I also want to have a Sectors page, that displays each Sector one-by-one, with the Projects that were done in that sector (that’s OK, via Sector.Projects) but also all the Technologies used in that sector’s projects, without duplicates.
An SQLSelect like this, gets all the Technologies per Sector:
class SectorPageController extends PageController {
public function Sectors(){
$sectors = Sector::get()->filter(array('Featured' => true));
foreach($sectors as $sector){
$sqlQuery = new SQLSelect();
$sqlQuery->setFrom('Sector');
$sqlQuery->setSelect('Technology.Title');
$sqlQuery->addInnerJoin('ProjectSector','"ProjectSector"."SectorID" = "Sector"."ID"');
$sqlQuery->addInnerJoin('Project','"Project"."ID" = "ProjectSector"."ProjectID"');
$sqlQuery->addInnerJoin('ProjectTechnology','"ProjectTechnology"."ProjectID" = "Project"."ID"');
$sqlQuery->addInnerJoin('Technology','"Technology"."ID" = "ProjectTechnology"."TechnologyID"');
$sqlQuery->addWhere(['Sector.ID = ?' => $sector->ID]);
$sqlQuery->setGroupBy('Technology.ID');
$result = $sqlQuery->execute();
// Set result into $sector.technologies?
}
return $sectors;
}
}Any ideas if this can be done, ideally without the admin having to maintain any additional relationships other than those on the Project’s DataObject?
Thanks!