Replaces the current search expression for the selected field. Please note that this function is designed to replace the search condition. It user didn't search for anything in the field this function will have no effect. To create a new search condition use setFieldValue() function. This code should go to AfterTableInit event of the List page.
Syntax
setSearchSQL($field, $sql);
Arguments
$field
the name of the field.
$sql
an SQL query to be used for the field.
Return value
No return value
Example 1
Change a search condition for the Make field.
$srchObj = SearchClause::getSearchObject("carsmodels");
$value = $srchObj->getFieldValue("make");
if( $value != null ) {
$srchObj->setSearchSQL("make", "make='$value' or id>12 and id<15");
}
Example 2
Here is how you can search master and details tables together. Let's say that you have Orders and OrderDetails tables and need to find orders that contain a certain product.
1. Modify the Orders SQL Query to add a dummy field named 'product'. Make sure this field is searchable.
SELECT
OrderID,
CustomerID,
EmployeeID,
OrderDate,
ShipAddress,
ShipCity,
ShipRegion,
ShipPostalCode,
ShipCountry,
'' as product
FROM orders
2. Orders table, AfterTableInit event:
$srchObj = SearchClause::getSearchObject("orders");
$value = $srchObj->getFieldValue("product");
if( $value != null ) {
$srchObj->setSearchSQL("product", "OrderID in (select OrderID from OrderDetails where ProductName like '%$value%')");
}
Note: in this event, we do a subquery to find all orders that contain the product in question.
See also:
