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.

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