Skip to main content

This site requires you to update your browser. Your browsing experience maybe affected by not having the most up to date version.

We're retiring the forums!

The SilverStripe forums have passed their heyday. They'll stick around, but will be read only. We'd encourage you to get involved in the community via the following channels instead:

General Questions /

General questions about getting started with SilverStripe that don't fit in any of the categories above.

Moderators: martimiz, Sean, Ed, biapar, Willr, Ingo, swaiba

Search results for child pages with multiple parents

Go to End



Community Member, 48 Posts

30 May 2012 at 9:50am

Edited: 30/05/2012 10:03am

I have a search form (posted about in a separate post) like such:

function DealSearchForm() {
    	$regions = DataObject::get("RegionPage");
    	$fields = new FieldSet(
	        new DropdownField('Region', 'Specify Region', $regions->map("ID", "Title", "Any")),
	        new DropdownField('Resort', 'Specify Resort', array(''=>'Any'))
	    // Create actions
	    $actions = new FieldSet(
	        new FormAction('doDealSearch', 'Search')
    	return new Form($this, 'DealSearchForm', $fields, $actions);

The Resort dropdown is dynamically populated by jQuery from the jSON generated by:

function getRegionsJson(){
	$regionsArray = array();
    	$regions = DataObject::get("RegionPage");

    	foreach($regions as $region) {
    		$regionsArray[$region->ID] = array();
    		foreach($region->Children() as $resort) {
    			$regionsArray[$region->ID][$resort->ID] = $resort->MenuTitle;
    	return json_encode($regionsArray);

The search being processed with:

public function getResults($searchCriteria = array()) {
		$start = ($this->request->requestVar('start')) ? (int)$this->request->requestVar('start') : 0;
		$limit = 20;
		$where = '"SiteTree"."ClassName" = \'ResortPage\'';
		$sort	= '"SiteTree"."Title"';
		$join	= '';
		if(isset($searchCriteria['Region']) && $searchCriteria['Region'] !=''){
			$where .= ' AND "SiteTree"."parentID" = ' . Convert::raw2sql($searchCriteria['Region']);
		if (isset($searchCriteria['Resort']) && $searchCriteria['Resort'] !=''){
			$where .= ' AND "SiteTree"."ID" = ' . Convert::raw2sql($searchCriteria['Resort']);
		$total = DataObject::get('SiteTree', $where, $sort, $join);
		$records = DataObject::get('SiteTree', $where, $sort, $join, array('start'=>$start,'limit'=>$limit));
		if($records) {
			$records->setPageLimits($start, $limit, $total->Count());
			return $records;
		return new DataObjectSet;

Which is working swimmingly.... However, there is a many to many relationship that I need to incorporate. A resort could appear in multiple regions. The resorts are stored as a child of a region in the site tree and for the few that that need to appear in another region as well, I have put a checkbox system in the back end to deal with this.

The additional regions are stored in the ResortPage_AdditionalRegions table (ID | ResortPageID | RegionPageID).

How can I modify my search SQL to handle this and display (if needed) a resort which is a child of a different Region based on the additional regions table if someone searched for 'any' resort in a selected region