Grouping data objects via relationships

silverstripe-4
Tags: #<Tag:0x00007f10cea5d0f8>

#1

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!


#2

OK, maybe I’ve made a bit of progress… instead of using the SectorPageController to retrieve the Technologies for each Sector, I’ve added this function to my Sector DataObject:

public function getTechnologies() {
	$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 = ?' => $this->ID]);
	$sqlQuery->setGroupBy('Technology.ID');
	$result = $sqlQuery->execute();
	///....?
}

But how do I return these to the template in the correct iterate-able type of object?


#3

The result of that should be an array, so in theory you should be able to just transform it into an ArrayList for the template to use.


#4

Thanks Tim! So, just to clarify my Sector DataObject method now looks like this:

	public function getTechnologies() {
		$sqlQuery = new SQLSelect();
		$sqlQuery->setFrom('Channel');
		$sqlQuery->setSelect('Technology.Title, Technology.Featured, Technology.Segment');
		$sqlQuery->addInnerJoin('ProjectChannel','"ProjectChannel"."ChannelID" = "Channel"."ID"');
		$sqlQuery->addInnerJoin('Project','"Project"."ID" = "ProjectChannel"."ProjectID"');
		$sqlQuery->addInnerJoin('ProjectTechnology','"ProjectTechnology"."ProjectID" = "Project"."ID"');
		$sqlQuery->addInnerJoin('Technology','"Technology"."ID" = "ProjectTechnology"."TechnologyID"');
		$sqlQuery->addWhere(['Channel.ID = ?' => $this->ID]);
		$sqlQuery->setGroupBy('Technology.ID');
		$results = $sqlQuery->execute();
		$technologies = ArrayList::create();
		foreach($results as $result){
			$result['Featured'] = !!$result['Featured'];
			$technologies->push(ArrayData::create($result));
		}
		return $technologies;
	}

It works but is that the most efficient way to do this?

Also, I’ve got Technology.Featured which is a boolean field, but it comes back out as a string this way. How to I cast it back to being boolean (properly the SS way)?