Search Master and Details tables together

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 ) {
  $sql = DB::PrepareSQL("OrderID in (select OrderID from OrderDetails where ProductName like '%:1%')", $value);
  $srchObj->setSearchSQL("product", $sql);
}

In this event, we perform a subquery to find all the orders that contain the product in question.

See also:

•About Search API

•setSearchSQL

•About Search API

•Master-details relationship between tables

•SQL query screen