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
Thanks