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 /

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

Add a WHERE to search based on CustomSearchContext


Go to End
Reply


5 Posts   1180 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, 247 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, 247 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)