0
\$\begingroup\$

I have the following structure:

   {
    "name": "Client Name",
    "title": "Test Client",
    "text": "Client Name : Test Client",
    "client_id": "1",
    "children": [{
        "name": "Customer Name",
        "title": "Test Customer",
        "text": "Customer Name : Test Customer",
        "customer_id": "2",
        "children": [{
            "name": "Contract Name",
            "title": "General",
            "text": "Contract Name : General",
            "contract_id": "500",
            "children": [{
                "name": "SubContract Name",
                "title": "General",
                "text": "SubContract Name : General",
                "subcontract_id": "10",
                "children": [{
                    "name": "Site Name",
                    "title": "Unknown",
                    "text": "Site Name : Unknown",
                    "site_id": "6",
                    "children": [{
                        "name": "Integration Name",
                        "title": "Integ Group 1",
                        "text": "Integration Name : Integ Group 1",
                        "src_type_id": "11",
                        "children": [{
                            "name": "Element Name",
                            "title": "Element Integ Group 1",
                            "text": "Element Name : Element Integ Group 1",
                            "element_id": "81",
                        }]
                    }, {
                        "name": "Integration Name",
                        "title": "Integ Group 2",
                        "text": "Integration Name : Integ Group 2",
                        "src_type_id": "12",
                        "children": [{
                            "name": "Element Name",
                            "title": "Element Integ Group 2",
                            "text": "Element Name : Element Integ Group 2",
                            "element_id": "82",
                        }]
                    }]
                }]
            }]
        }]
    }]
 }

create from this function:

public function viewclient(Request $client_id)
{
    $client_id_temp = $client_id->client_id;
    $client_name = ElementsMap::select('client')->where('client_id', '=', $client_id_temp)->first();
    $results['name'] = 'Client Name';
    $results['title'] = $client_name->client;
    $results['text'] = 'Client Name : ' . $client_name->client;

    $customer_name = ElementsMap::select('element_customer', 'customer_id', 'client_id')->distinct('element_customer')->where('client_id', '=', $client_id_temp)->get();

    foreach ($customer_name as $customer_name_row)
      {
        $contract_name = ElementsMap::select('element_contract', 'customer_id', 'client_id', 'contract_id')->distinct('element_contract')->where('client_id', '=', $customer_name_row->client_id)->where('customer_id', '=', $customer_name_row->customer_id)->get();
       $results6 = array();

             foreach ($contract_name as $contract_name_row)
                {
                 $subcontract_name = ElementsMap::select('element_subcontract', 'subcontract_id', 'contract_id', 'customer_id', 'client_id')->distinct('element_subcontract')->where('client_id', '=', $contract_name_row->client_id)->where('customer_id', '=', $contract_name_row->customer_id)->where('contract_id', '=', $contract_name_row->contract_id)->get();
                $results5 = array();
                foreach ($subcontract_name as $subcontract_name_row)
                    {
                    $site_name = ElementsMap::select('element_site', 'subcontract_id', 'contract_id', 'client_id', 'customer_id', 'site_id')->distinct('element_site')->where('client_id', '=', $subcontract_name_row->client_id)->where('customer_id', '=', $subcontract_name_row->customer_id)->where('contract_id', '=', $subcontract_name_row->contract_id)->where('subcontract_id', '=', $subcontract_name_row->subcontract_id)->get();
                    $results4 = array();
                        foreach ($site_name as $site_name_row)
                        {
                            $integration_name = ElementsMap::select('integration', 'src_type_id', 'subcontract_id', 'contract_id', 'client_id', 'customer_id', 'site_id')->distinct('integration')->where('customer_id', '=', $site_name_row->customer_id)->where('site_id', '=', $site_name_row->site_id)->where('contract_id', '=', $site_name_row->contract_id)->where('subcontract_id', '=', $site_name_row->subcontract_id)->where('client_id', '=', $site_name_row->client_id)->get();
                            $results3 = array();
                              foreach ($integration_name as $integration_name_row)
                              {
                                  $elements = ElementsMap::select()->where('src_type_id', '=', $integration_name_row->src_type_id)->where('customer_id', '=', $integration_name_row->customer_id)->where('site_id', '=', $integration_name_row->site_id)->where('contract_id', '=', $integration_name_row->contract_id)->where('subcontract_id', '=', $integration_name_row->subcontract_id)->where('client_id', '=', $integration_name_row->client_id)->get();
                                  $results2 = array();
                                     foreach ($elements as $element_row)
                                        {
                                          $results2[] = ['name' => 'Element Name', 'title' => $element_row->element, 'text' => 'Element Name : '.$element_row->element];
                                        }
                                  $results3[] = ['name' => 'Integration Name', 'title' => $integration_name_row->integration, 'text' => 'Integration Name : '.$integration_name_row->integration, 'children' => $results2];
                               }
                          $results4[] = ['name' => 'Site Name', 'title' => $site_name_row->element_site, 'text' => 'Site Name : '.$site_name_row->element_site, 'children' => $results3];
                        }
                      $results5[] = ['name' => 'SubContract Name', 'title' => $subcontract_name_row->element_subcontract, 'text' => 'SubContract Name : '.$subcontract_name_row->element_subcontract, 'children' => $results4];
                    }
                 $results6[] = ['name' => 'Contract Name', 'title' => $contract_name_row->element_contract, 'text' => 'Contract Name : '.$contract_name_row->element_contract, 'children' => $results5];
                }
        $results['children'][] = ['name' => 'Customer Name', 'title' => $customer_name_row->element_customer, 'text' => 'Customer Name : '.$customer_name_row->element_customer, 'children' => $results6];
    }
    return response()->json($results);

}

As shown above, it iterates query to the same SQL view in each loop (it was ok when the tree was originally imagined as two-tier, but as times goes by, the request to increase the level came). I was wondering if there's a good solution for this.

\$\endgroup\$

1 Answer 1

1
\$\begingroup\$

You likely should be thinking about using joins. One of the bad behaviors that "natural language" query builders like this can drive an unsuspecting developer to, is the behavior of making queries in loops like this. My guess is that you can likely get the data you are looking for in a single query which employs appropriate joins and grouping.

Whatever query builder you are using also make it REALLY difficult to see the problem or suggest solution, as it is not even clear to me what tables these queries are acting against (there is no FROM definition).

\$\endgroup\$
2
  • \$\begingroup\$ Hi Mike, yes the ElementsMap is joined view from (6 tables). I was just horrified to see that I'm iterating from a single view every loop, and was wondering if I can get all in a single array (a single query just starting with client_id, and with all the parent-child ids involved mapped in it) and create a structure from it. \$\endgroup\$
    – Odinovsky
    Jan 13, 2017 at 20:43
  • \$\begingroup\$ @Odinovsky you might consider adding some information on DB tables involved. \$\endgroup\$
    – Mike Brant
    Jan 14, 2017 at 5:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.