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.

Customising the CMS /

Searching few tables in one shot


Reply


625 Views

Avatar
Victor

Community Member, 128 Posts

3 April 2009 at 2:10pm

If we have few tables with the same fields and want to present results together we can do something like this

function PeopleSearchForm()
{
return new Form(
$this,
"PeopleSearchForm",
new FieldSet(new TextField($name='FirstName',$title='First Name starts from', $value='',$maxLength='15'), new TextField('LastName','Last Name starts from')),
new FieldSet(new FormAction('doPeopleSearch','People Search'))
);
}

function doPeopleSearch($data, $form)
{
$results = new DataObjectSet();
$resultsf = DataObject::get("FacultyPage"," `lastname` LIKE '".$data['LastName']."%' AND `firstname` LIKE '".$data['FirstName']."%'");
$resultss = DataObject::get("StaffPage"," `lastname` LIKE '".$data['LastName']."%' AND `firstname` LIKE '".$data['FirstName']."%');
$resultst =(DataObject::get("StudentPage"," `lastname` LIKE '".$data['LastName']."%' AND `firstname` LIKE '".$data['FirstName']."%'"));
$results->merge($resultsf);
$results->merge($resultss);
$results->merge($resultst);
$results->sort('lastname');
return $this->customise(array('PeopleSearchResults' => $results))->renderWith(array('PeopleSearch_results','Page'));
}

presenting results through

<div class='typography'>
<% if PeopleSearchResults %>
<ul>
<% control PeopleSearchResults %>
<li><a href="$Link">$firstname $lastname</a></li>
<% end_control %>
</ul>
<% else %>
<font color="#FF0000">There were no results that matched your query.</font><p>
<% end_if %>
</div>
<div>
$PeopleSearchForm

</div>

However it would not work if tables contain different fields and we want to present Faculty, Students, Staff separately.

Trying

function doPeopleSearch($data, $form)
{
$resultsf = DataObject::get("FacultyPage"," `lastname` LIKE '".$data['LastName']."%' AND `firstname` LIKE '".$data['FirstName']."%'","`lastname`");
return $this->customise(array('FacultySearchResults' => $resultsf))->renderWith(array('PeopleSearch_results','Page'));

$resultss = DataObject::get("StaffPage"," `lastname` LIKE '".$data['LastName']."%' AND `firstname` LIKE '".$data['FirstName']."%'","`lastname`");
return $this->customise(array('StaffSearchResults' => $resultss))->renderWith(array('PeopleSearch_results','Page'));

$resultst = DataObject::get("StudentPage"," `lastname` LIKE '".$data['LastName']."%' AND `firstname` LIKE '".$data['FirstName']."%'","`lastname`");
return $this->customise(array('StudentSearchResults' => $resultst))->renderWith(array('PeopleSearch_results','Page'));
}

and trying to output with

<h2>Faculty</h2>
<% if FacultySearchResults %>
<ul>
<% control FacultySearchResults %>
<li><a href="$Link">$firstname $lastname</a></li>
<% end_control %>
</ul>
<% else %>
<font color="#FF0000">There were no results that matched your query.</font><p>
<% end_if %>

<h2>Staff</h2>
<% if StaffSearchResults %>
</div>

.....
....

we see that only FacultySearchResults are returned non-empty.

How to fix it?

Thank you in advance. Victor