Here is how you can search master and details tables together.
For example, you have Orders and OrderDetails tables and you want to find the 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:
•Master-details relationship between tables