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.

Data Model Questions

Add a WHERE to search based on CustomSearchContext


Reply

5 Posts   993 Views

Avatar
Nobrainer Web

24 February 2011 at 7:52am Community Member, 135 Posts

Hey,

Need some help here, i have an issue with a search.
I need the code below only to return results where StartDate > CURDATE(), but i have no idea what i need to do.

Thanks in advance :o)

From my Page_Controller

   public function Find() {
      $context = singleton('Course')->getCustomSearchContext();
      $actions = new FieldSet(new FormAction('FindKursus', 'Kursus søgning'));
      
      $form = new Form(
         $this, 'Find', $context->getSearchFields(), $actions
      );
      
      $form->setFormMethod('GET');
      $form->disableSecurityToken();
   
      return $form;
   }

   public function FindKursus($data, $form) {
      $context = singleton('Course')->getDefaultSearchContext();
      $results = $this->getResults($data);
   
      return $this->customise(array(
         'Title' => 'Kurser der matcher din søgning',
         'Results' => $results,
         'Find' => $form
      ))->renderWith(array('Kursus_results', 'Page'));
   }
   
   function getResults($searchCriteria = array()) {
      $start = ($this->request->getVar('start')) ? (int)$this->request->getVar('start') : 0;
      $limit = 10;
         
      //$filter = "StartDate > CURDATE()",
      //$sort = "StartDate ASC",
      
      $context = singleton('Course')->getCustomSearchContext();
      
      $query = $context->getQuery($searchCriteria, array(), array('start'=>$start,'limit'=>$limit));

      $records = $context->getResults($searchCriteria, "StartDate ASC", array('start'=>$start,'limit'=>$limit));
      
      if($records) {
         $records->setPageLimits($start, $limit, $query->unlimitedRowCount());
      }
      
      return $records;
   }   

The relevant code from the Course.php file

   static $searchable_fields = array(
      'Title' => array(
         'field' => 'TextField',
         'filter' => 'PartialMatchFilter',
         'title' => 'Kursus navn'
),
      'City' => array(
         'field' => 'TextField',
         'filter' => 'PartialMatchFilter',
         'title' => 'By'
),
      'Location' => array(
         'field' => 'TextField',
         'filter' => 'PartialMatchFilter',
         'title' => 'Sted'
),
      'StartDate' => array(
         'field' => 'TextField',
         'filter' => 'PartialMatchFilter',
         'title' => 'Start dato'
),
      'EndDate' => array(
         'field' => 'TextField',
         'filter' => 'PartialMatchFilter',
         'title' => 'Slut dato'
)
   );

   public function getCustomSearchContext() {
      
      $fields = $this->scaffoldSearchFields(array(
         'restrictFields' => array(
            //'Title',
            'City'
            //'Location',
            //'StartDate',
            //'EndDate'
         ),
         'FieldClasses' => array(
            //'ExpiryDate' => 'HiddenField'
         )
      ));
         
      $filters = array(
         'Title' => new PartialMatchFilter('Title'),
         'City' => new PartialMatchFilter('City'),
         'Location' => new PartialMatchFilter('Location'),
         'StartDate' => new GreaterThanFilter('StartDate'),
         'EndDate' => new LessThanFilter('EndDate'),
      );
      
      // Create a dropdown of all available cities
      $cities = DB::query('SELECT DISTINCT "City" FROM "Course" ORDER BY "CITY" ASC')->column();
      $newCities = array_combine($cities, $cities);
      $CityDropDownField = new DropdownField('City', 'By', $newCities);
      
      $CityDropDownField->sethasEmptyDefault(true);
      $fields->replaceField("City", $CityDropDownField);

      // Create a dropdown of all available locations
      $locations = DB::query('SELECT DISTINCT "Location" FROM "Course"')->column();
      $newLocations = array_combine($locations, $locations);
      $LocationsDropDownField = new DropdownField('Location', 'Sted', $newLocations);
      
      $LocationsDropDownField->sethasEmptyDefault(true);
      $fields->replaceField("Location", $LocationsDropDownField);
      
      return new SearchContext(
         $this->class,
         $fields,
         $filters
      );
   }

Avatar
Pigeon

24 February 2011 at 10:04pm (Last edited: 24 February 2011 10:11pm), Community Member, 243 Posts

The most straight forward way to add the condition you want is to add it to the Query object after you've got it from the SearchContext.

So, try:

function getResults($searchCriteria = array()) {
$start = ($this->request->getVar('start')) ? (int)$this->request->getVar('start') : 0;
$limit = 10;

//$filter = "StartDate > CURDATE()",
//$sort = "StartDate ASC",

$context = singleton('Course')->getCustomSearchContext();

$query = $context->getQuery($searchCriteria, array(), array('start'=>$start,'limit'=>$limit));

$query->where('`StartDate` > CURDATE()');

$records = $context->getResults($searchCriteria, "StartDate ASC", array('start'=>$start,'limit'=>$limit));

if($records) {
$records->setPageLimits($start, $limit, $query->unlimitedRowCount());
}

return $records;
}

Avatar
Nobrainer Web

24 February 2011 at 10:18pm Community Member, 135 Posts

Hi Pigeon,

Thank you for your answer, i can see that the Query is updated with the where, but it does not affect my results.
I dont understand the code, but should $records be affected by the change to $query?
I dont see $query beeing used here: $records = $context->getResults($searchCriteria, "StartDate ASC", array('start'=>$start,'limit'=>$limit));

Using the exact code in public function Find()

Thank you :o)

Avatar
Pigeon

25 February 2011 at 10:58am Community Member, 243 Posts

Ok, then in your CustomSearchContext, you could add another filter that you didnt let the front end user access and then set the value for that yourself.

so:
add a new filter called 'LimitDate' in $filters in getCustomSearchContext() which would be a LessThanFilter and applied to the StartDate column
then in getResults add $searchCriteria['LimitDate'] = date('Y-m-d',strtotime('+1 day'))

Or, something like that.

Avatar
Nobrainer Web

27 February 2011 at 10:57am Community Member, 135 Posts

Hi Pigeon,

Thank you very much for your help!

I just added $searchCriteria['StartDate'] = date('Y-m-d'); to my getResults function and i get the results i want.
I really appreciate your help, might be a small thing for you, but i would not know how to solve it on my own or atleast i would spend ages finding a solution.

See you around :o)