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   1020 Views

Avatar
Nobrainer Web

Community Member, 137 Posts

24 February 2011 at 7:52am

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

Community Member, 243 Posts

24 February 2011 at 10:04pm

Edited: 24/02/2011 10:11pm

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

Community Member, 137 Posts

24 February 2011 at 10:18pm

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

Community Member, 243 Posts

25 February 2011 at 10:58am

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

Community Member, 137 Posts

27 February 2011 at 10:57am

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)