Jump to:

3375 Posts in 999 Topics by 712 members

Data Model Questions

SilverStripe Forums » Data Model Questions » Add a WHERE to search based on CustomSearchContext

Moderators: martimiz, Sean, biapar, Willr, Ingo, swaiba, simon_w

Page: 1
Go to End
Author Topic: 922 Views
  • Nobrainer Web
    Avatar
    Community Member
    134 Posts

    Add a WHERE to search based on CustomSearchContext Link to this post

    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
          );
       }

  • Pigeon
    Avatar
    Community Member
    243 Posts

    Re: Add a WHERE to search based on CustomSearchContext Link to this post

    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;
    }

  • Nobrainer Web
    Avatar
    Community Member
    134 Posts

    Re: Add a WHERE to search based on CustomSearchContext Link to this post

    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)

  • Pigeon
    Avatar
    Community Member
    243 Posts

    Re: Add a WHERE to search based on CustomSearchContext Link to this post

    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.

  • Nobrainer Web
    Avatar
    Community Member
    134 Posts

    Re: Add a WHERE to search based on CustomSearchContext Link to this post

    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)

    922 Views
Page: 1
Go to Top

Want to know more about the company that brought you SilverStripe? Then check out SilverStripe.com

Comments on this website? Please give feedback.