2

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 | +------------------------------------+-----------------------------+--------------------------------------+--------------------------+------------------------+
Improve this question

1 Answer 1

Reset to default
2

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'); }
Improve this answer
Sign up to request clarification or add additional context in comments.

14 Comments

I will edit in some more info on my DB structure in a sec! :=)
edited in the info on the relations between coretable and extensions :)
Thanks! Which of the foreign keys are subject to a UNIQUE constraint?
Basically all of the FKs from extensiontable to coretable. Coretable and extensiontable always have a One-To-One Relationship. Therefore I added the UNIQUE Constraint to the foreign keys. I hope this can be deemed a not entirely bad practice? xD
Also, there seems to be no relation between coretable and the tables having a foreign key in it. Is that quite right?
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.