2. PHPRunner Tips and Tricks
  3. Search Master and Details tables together
This topic is locked

Search Master and Details tables together

 		6/8/2017 17:33:10
PHPRunner Tips and Tricks
Sergey Kornilov admin

Here is how you can search master and details tables together. For instance you have Orders and OrderDetails tables and need to find orders that contain a certain product.

  1. Modify 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%')");

}


In this event we do a subquery to find all orders that contain the product in question.
More info about search API:

https://xlinesoft.com/phprunner/docs/searchapi_about.htm

https://xlinesoft.com/phprunner/docs/searchapi_set_search_sql.htm
J
 		jackwood 6/8/2017

Hi Sergey , Can it be implemented in PHPRunner V.8.1 ?
romaldus 6/9/2017

Thanks
Sergey Kornilov admin 6/13/2017

This functionality only exists in PHPRunner 9.7 and later.
G
 		gwomble 10/31/2017

Is this possible in ASP Runner as well, I tried implementing it but it doesn't seem to work
R
 		Rigmantas 11/30/2017

hi,

Sorry, but i can't master that.

My master and detail pages is the same.

there is master table: inventorius_gauta
id,

diena,

prekes_nr,

prekes_pav,

paketo_nr,

matavimo_vnt,

vieneto_kaina,

vienetai,

suma,

pastabos,

gavo,

vartotojas,

skyrius,

padalinys,

sn,

gauta_data,

grupe,

kita_informacija,.....
detail table is "inventorius_nr" and there is new column "idgauta" (master id)

I want search in master and detail tables let's say sn number

I do all what you wrote above:

$srchObj = SearchClause::getSearchObject("inventorius_gauta");
$value = $srchObj->getFieldValue("sn");
if( $value != null ) {

$srchObj->setSearchSQL("sn", "inventorius_gauta.id in (select idgauta from inventorius_nr where sn like '%$value%')");

}

But this is not worked for me <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=83814&image=1&table=forumreplies' class='bbc_emoticon' alt=':(' />
Thanks,

Regards

Rimantas
M
 		monoofy 2/7/2018

tried also, but with no luck

$srchObj = SearchClause::getSearchObject("clients");

$value = $srchObj->getFieldValue("subscriber");

if( $value != null ) {
$srchObj->setSearchSQL("subscriber", "clentid in (select company from subscribers where name like '%$value%')");
}



I have master table "Clients" and details "Subscribers"

the link between them is the company field from details and clentid from master