Looping through organization records to find relationships

Question

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 the fact that they have a code starting with "64"

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

I need to find all the sub organisations, which have a 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:

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.

Answer 1

but it's quite slow

The OP did not include any sample data generators, timing measurements, or EXPLAIN PLAN output. It would be helpful to know what you believe the complexity should be, versus what is observed in test runs.

"main" organisations ... are identified by [having] a code starting with "64"

This is crying out for introducing an is_main boolean column. If it's inconvenient to do that on the base relation, you can always CREATE VIEW to accomplish the same thing. And then for some backend DB vendors you can even specify that a materialized view shall cache relevant rows.

A compound index on (parent_org, org) should go a long way toward making your queries more efficient.

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

This suggests a GROUP BY query. Introducing a parent column for the "main" organization would signficantly simplify matters.

Answer 2

Are you able to change the schema of the tables? And are the id fields integer, string or other field types? Presuming those id fields are integers, it would likely be better to use those to relate the records between the two tables instead of using the address fields. As this answer explains using a surrogate key can keep the database tables smaller. Instead of storing the address with each record in the orgs_sub table, it would be simpler to store the id from the orgs_main table.

As you've utilized with code in subsequent posts, the Eloquent ORM allows for much simpler code. One might likely create a model for MainOrganization which would have the table property set to orgs_main, and another model for SubOrganization with its table property set to orgs_sub. Then in the MainOrganization model there could be a One to Many relationship for subOrganizations. That would allow for aggregations like counts using the withCount() method.

Then perhaps one of the biggest advantages would be eager loading. This would allow for one query to get all sub organizations in a single query instead of one query for each main organzation.