title: Search Logic to access more than 6 related tables efficient
body: <p>in my current project, the task is to be able to search through 6 tables.</p>
  
  <p>Tables are:
  A, B, C, D, E, F</p>
  
  <p>All tables are related to each other through another table in the list.</p>
  
  <p>E.g. A->B B->A->C C->F F->D</p>
  
  <p>Like User->Phonenumber Phonenumber->Country etc.</p>
  
  <p>Since the searchmask/form the user gets presented leaves the possibility open to send only certain parameters to the server it occurs that the search must find a result R no matter which parameter the user passes through.</p>
  
  <p>In this case a parameter is a column value of any of the above listed tables, e.g. Phonenumber->Provider = Verizon means, the user wants all entries with the provider = Verizon.</p>
  
  <p>The actual problem is not searching in specific tables, it is searching in specific tables an retrieving the right result from the results you found in the specific tables.</p>
  
  <p>Let me explain: Result, in our case is NOT a table object of the table we currently searched through.</p>
  
  <p>A Result should always be the same object Result which must have at least one entry in A and B.
  Example Requirements:
  A = User exists
  B = he has a Phonenumber</p>
  
  <p>Since all tables have relations to each other directly or indirectly we could access a result without searching for columns in A or B only by looking into the related tables retrieving the rows with Provider=Verizon and <code>JOIN</code>ing on the related tables via ids/keys/constraints.</p>
  
  <p>Currently I do more than one query.
  I first check the type of the parameters.
  "Are they columns from A to C"
  Yes, then do a direct SELECT on A, B or C and take the result as Result.</p>
  
  <p>"Are they columns from E"
  Yes, then do a SELECT on E, after that another SELECT on C with c_id from E.</p>
  
  <p>As Result (in PHP) I always want to have an object/array containing A.value1, B.value1
  I never want any information about C, D, E or F.
  I only want to get Result consisting of A.value1 and B.value1 by any allowed/searchable parameter that is represented in a column in one or more of the tables, e.g. Provider=Verizon AND Country=China AND OnVaccation=True</p>
  
  <p>I am sorry for the complexity. </p>