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.

Form Questions /

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

SearchContext and Versioned : bad SQLQuery !?


Go to End
Reply


4 Posts   1646 Views

Avatar
Myrdhin

Community Member, 70 Posts

2 June 2010 at 1:07am

Edited: 02/06/2010 1:08am

Hello,

I try to use SearchContext in my project but it doesn't work :'(
Silverstripe always add "_Live" to all table, even relation tables (many_many) to the query.

My Code :

+ I have a "Level" (DataObject) with just a Title :

class Level extends DataObject {

	static $db = array(
			'Title'       => 'Varchar(255)'
	);

	static $belongs_many_many = array(
			'Documents' => 'Document'
	);


	function getCMSFields() {
		$fields = parent::getCMSFields();
		$fields->addFieldToTab(
			'Root.Main',
			new TextField('Title', $this->fieldLabel('Title'))
		);
		
		return $fields;
	}
	
	function listAll() {
		$objs = DataObject::get($this->class);

		if(!$objs) return array();

		$list = array_unique($objs->column('Title'));
		sort($list);
		return $list;
	}

}

+ A Document class (extends Page) and a many_many relation with "Level". I define the getDefaultSearchContext method to change the Level field (to a dropdown field) :

class Document extends Page {

	static $db = array(
			'Subtitle' => 'Varchar(255)'
	);

	static $many_many = array(
			'Levels'  => 'Level'
	);

	static $searchable_fields = array(
			'Levels.Title'
	);

	static $field_labels = array(
			'Levels.Title'  => 'Levels'
	);

	...

	public function getDefaultSearchContext() {
		$context = parent::getDefaultSearchContext();

		$context->removeFieldByName('Levels');

		$levelMap = null;
		$levels = Singleton('Level')->listAll();
		if($levels) { $levelMap = array_combine($levels, $levels); }

		$levelField = new DropdownField( 'Levels', 'Levels', $levelMap );
		$levelField->setHasEmptyDefault(true);

		$context->addField($levelField);

		return $context;
	}
}

+ And i created a new type of page to search Documents with Level.Title. i define a searchForm with the SearchContext of the "Document" :

class DocumentSearch extends Page {
}

class DocumentSearch_Controller extends Page_Controller {

	function DocumentSearchForm() {
		$context = singleton('Document')->getDefaultSearchContext();
		$fields = $context->getSearchFields();

		$form = new Form($this, "DocumentSearchForm",
			$fields,
			new FieldSet(
				new FormAction('DocumentSearch', _t('MemberTableField.SEARCH'))
			)
		);
		
		return $form;
	}

	function DocumentSearch($data, $form, $request) {
		$searchCriteria = array(
			'Levels.Title'  => $data['Levels']
		);

		return $this->render(
			array(
				'DocumentResults' => $this->DocumentSearchResults($searchCriteria),
				'DocumentSearchForm' => $form
			)
		);
	}

	function DocumentSearchResults($searchCriteria=array()) {
		$context = singleton('Document')->getDefaultSearchContext();
		$records = $context->getResults($searchCriteria);
		return $records;
	}

}

But when i search a document, Silverstripe give me an error :

Couldn't run query: SELECT DISTINCT "SiteTree_Live" ... blabla... Table 'MyDB.Document_Levels_Live' doesn't exist.

I have a "Document_Levels" table but not "Document_Levels_Live" !?

Really, thanks four your help !

Avatar
joern

Community Member, 28 Posts

30 June 2010 at 1:00pm

Same problem here…

Avatar
markjames

Community Member, 2 Posts

15 February 2012 at 7:08am

For anyone who hits this issue and finds this page as a result, I think the fix might be something like the following (untested) change to Versioned.php (ignore tables which are not names of classes that have the Versioned extension):

https://gist.github.com/1828664

Avatar
jsirish

Community Member, 18 Posts

31 May 2012 at 4:31am

Hey markjames,

Just wanted to verify that your Versioned edit seems to do the trick. Thanks for the tip!