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, biapar, Willr, Ingo, swaiba, simon_w

Search results for child pages with multiple parents


Reply


333 Views

Avatar
Fraser

Community Member, 46 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

Thanks