2
\$\begingroup\$

I have two tables in my database, which contains a big list of organisations. They are called orgs_main and orgs_sub. This is the structure:

orgs_main:

id | code | name | address |
----------------------------

orgs_sub:

id | code | name | address | merged |
-------------------------------------

orgs_main contains "main" organisations, which are identified by that they have a code starting with "64"

orgs_sub contains "sub" organisations, which are identified by they don't have a code starting with "64".

I need to find all the sub organisations, which have matching address to the main organisations, and then list them.

Example:

Broadway Avenue 110 (orgs_main)

  • Company 1 (orgs_main)
  • Company 2 (orgs_main)
  • Company 3 (orgs_main)

  • Sub company 1 (orgs_sub)

  • Sub company 2 (orgs_sub)

The way I am doing this now, is like this:

MergerController.php:

public function merger()
{
        //Select main organisations, that have matching addresses on sub-organisations and that is not already merged together.
        $MainOrganisations = DB::table("orgs_main AS j")
        ->selectRaw("DISTINCT j.address, (SELECT COUNT(*) FROM orgs_sub i WHERE i.address = j.address) AS SubCount")
        ->whereRaw("(SELECT COUNT(*) FROM orgs_sub i WHERE i.address = j.address AND i.merged=0) > 0")
        ->simplePaginate(10);

        //Loop through the main companies, so we get all main companies with the same address.
        foreach($MainOrganisations as $key => $SameAddress){
                $CompaniesSameAddress = DB::table('orgs_main')
                ->select('name', 'code')
                ->where('address', $SameAddress->address)
                ->where('code', 'LIKE', '64%')
                ->get();
                $SameAddress->Companies = $CompaniesSameAddress;
        }

        //Foreach found Main Organisation Address, list all the sub companies with this address.
        foreach($MainOrganisations as $key => $MainOrganisation){

            $SubOrganisations = DB::table('orgs_sub')
            ->select('name', 'code', 'id')
            ->where('address', $MainOrganisation->address)
            ->get(); 
            $MainOrganisation->SubOrganisations = $SubOrganisations;

        }
            //Return the data to our view.
            $data = [
              'MainOrganisations'            => $MainOrganisations,
            ];
            return $data;
}

My view file:

merger.list.blade.php:

<table width="100%" class="merger">
    @foreach ($MainOrganisations as $Main)
        <thead>
            <th>{{ $Main->address }}  </th>
            <th>
            @foreach($Main->Companies as $Companies)
                {{$Companies->name}} (Code: {{ $Companies->code }}) <br />
            @endforeach
            </th>

        </thead>
        <tbody>
         @foreach($Main->SubOrganisations as $Sub)
            <tr>
                <td>{{ $Sub->name }}</td>       
                <td>{{ $Sub->code }}</td>
            </tr>

        @endforeach
        </tbody>

    @endforeach
</table>

Which shows something like this:

enter image description here

Any comments or alternatives methods is highly appreciated, as above code does function as intended, but it's quite slow and feel a bit "hackish" to me.

Thank you in advance!

\$\endgroup\$
  • \$\begingroup\$ Have you seen Eloquent documentation? Do you have relationships defined in the model(s)? What is merged field? How are you getting the company lists, do you have power to change schema? Can you re-import the data if needed? \$\endgroup\$ – Kyslik Sep 13 '18 at 9:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.