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.

We've moved the forum!

Please use forum.silverstripe.org for any new questions (announcement).
The forum archive will stick around, but will be read only.

You can also use our Slack channel or StackOverflow to ask for help.
Check out our community overview for more options to contribute.

Data Model Questions /

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

Add a WHERE to search based on CustomSearchContext


Go to End


5 Posts   1720 Views

Avatar
Nobrainer Web

Community Member, 138 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
dhensby

Community Member, 253 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, 138 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
dhensby

Community Member, 253 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, 138 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)