How to (re)organize this data - Lumen/Laravel

Question

So far, my Lumen based Backend-API fetches the following results from my MariaDB:

[{
  "Internal_key": "TESTKEY_1",
  "extensiontable_itc": {
    "description": "EXTENSION_iTC_1"
  },
  "extensiontable_sysops": {
    "description": "EXTENSION_SYSOPS_1"
  }
}, {
  "Internal_key": "TESTKEY_2",
  "extensiontable_itc": {
    "description": "EXTENSION_ITC_2"
  },
  "extensiontable_sysops": {
    "description": "EXTENSION_SYSOPS_2"
  }
}, {
  "Internal_key": "TESTKEY_3",
  "extensiontable_itc": {
    "description": "EXTENSION_ITC_3"
  },
  "extensiontable_sysops": {
    "description": "EXTENSION_SYSOPS_3"
  }
}, {
  "Internal_key": "TESTKEY_4",
  "extensiontable_itc": {
    "description": "EXTENSION_ITC_4"
  },
  "extensiontable_sysops": {
    "description": "EXTENSION_SYSOPS_4"
  }
}, {
  "Internal_key": "TESTKEY_5",
  "extensiontable_itc": {
    "description": "EXTENSION_ITC_5"
  },
  "extensiontable_sysops": {
    "description": "EXTENSION_SYSOPS_5"
  }
}]

What you're seeing is the fetch from 3 Tables, one Coretable and two extensiontables. The coretable contains the "Internal_Key" and it is referenced by the extensiontables via its id, which I declared hidden in the model and therefore its currently not being displayed in the fetchresults. The line of code executing this fetch looks like this:

$join = coretable::with($permittedTables)->get();

The $permittedTables is an array of tablenames, so basically any number and combination of extensiontables can be fetched alongside the referenced records from the coretable.

The data shall ultimately be inserted into a list-like view. Here, for each "Internal_key" a row shall be created into which all the data associated with that key will be inserted.

I'm perfectly fine with the current structure of the data, as I can loop through it however I want and thereby extract the data accordingly to the needs of the list. However, I would like to know if there is any way to (re)organize it differently. If I wanted to put each set of data from the extensiontables on the same "arraylevel" as its respective Internal_key, how should I do this? Should I change the way I fetch the data, or should rearrange the data after the fetch? And in both cases: Whats the easiest, most reliable way to do it?

EDIT: Some more info on the structure of my DB. Coretable has an ID as primary Key which is referenced in the extensiontables via the "coretable_id" FK. Here is the schema of my foreign keys for my DB:

+------------------------------------+-----------------------------+--------------------------------------+--------------------------+------------------------+
| TABLE_NAME                         | COLUMN_NAME                 | CONSTRAINT_NAME                      | REFERENCED_TABLE_NAME    | REFERENCED_COLUMN_NAME |
+------------------------------------+-----------------------------+--------------------------------------+--------------------------+------------------------+
| ad_usersxad_groups                 | Ad_user_id                  | fk_ad_groupxad_user                  | ad_users                 | id                     |
| ad_usersxad_groups                 | Ad_group_id                 | fk_ad_userxad_group                  | ad_groups                | id                     |
| extensiontables_registryxad_groups | ad_group_id                 | fk_ad_groupxextensiontables_registry | ad_groups                | id                     |
| extensiontables_registryxad_groups | extensiontables_registry_id | fk_extensiontables_registryxad_group | extensiontables_registry | id                     |
| extensiontable_itc                 | coretable_id                | fk_extensiontable_itc_coretable      | coretable                | id                     |
| extensiontable_sysops              | coretable_id                | fk_extensiontable_sysops_coretable   | coretable                | id                     |
| inaccessibletable                  | coretable_id                | fk_inaccessibletable_coretable       | coretable                | id                     |
+------------------------------------+-----------------------------+--------------------------------------+--------------------------+------------------------+

Answer 1

First of all: we don't have informations on the coretable and extensiontables models, so we don't know if you've implemented Polymorphic Relationships, which would possibly fit your scope perfectly.

That said, a possible reorganization would be to flatten the tree to an array of objects, with

• A property referencing the internal key
• Other properties storing their type extensiontable source in the name, e.g. "desc_itc" : "EXTENSION_iTC_1"

That would leave you with something like this:

[{
  "Internal_key": "TESTKEY_1",
  "desc_itc": "EXTENSION_iTC_1",
  "desc_sysops": "EXTENSION_SYSOPS_1"
  }
}, ...
]

EDIT: You mentioned in your comment that there's a one to one relationship with every foreign key only present in an extensiontable, and referencing coretable.id with the key coretable_id .

Another way to organize this data would be to add two columns to coretable, extendable_type e extendable_id and implement a one to one polymorphic relationship: storing the model name in the extendable_type column will let you invoke all extensions by simply accessing the extendable property of your Eloquent model, e.g.

$extendable = $core->extendable;

In order to achieve this, you'd only have to define the following method in the coretable model:

/**
* Returns the matching extension data
*/
public function extendable() {
        return $this->morphTo();
    }

And this to each extensiontable_* model:

/**
* Returns the matching core data
*/
public function coretable() {
  return $this->morphOne('App\coretable', 'extendable');
}