Question 220447 in Laravel Question posted in
The official Laravel Documentation can be found here.

Advice on best way to store nested data with relationships on PHP and Laravel?

We have a need to store user provided translations, and the current design approached the problem in what I thought was a sufficient structure:

id | language_id (FK) | item_id (FK) | translation

So, if I have item A with ID 1, with a translation for language ID 2, it’d show up as:

1 | 2 | 1 | Hello World in Spanish

Now lets say another user inherits item A’s properties (call it item B), such as that B has attribute parent_id as well, pointing to 1 (which is A)

This way, in Laravel, the following works:

    public function translations(){
        $language_ids = $this->item_languages()->pluck('language_id')->toArray();
        $item_ids = [$this->id];
        if ($this->hasParentItem()){
            array_push($item_ids, $this->parent_id);

            $parent_item = $this->parentItem()->first();
            $direct_parent_item = $parent_item;
            while ($parent_item->hasParentItem()) {
                array_push($item_ids, $parent_item->parent_id);
                $parent_item = $parent_item->parentItem()->first();
            }
        }
        return Translation::whereIn('language_id', $language_ids)->whereIn('item_id', $item_ids);
    }

All looked good!
Until we realized in QA that grand children dont work in the front end, and no wonder!
Item C is born into the world!
It supposed (and does) inherit the grand parent’s A translation, and so the above code returns translation with ID 1.
However, Item C’s own model, only has direct reference to parent_id 2 and not 3!

Therefore, the front end mapping and showing translations, has no way to know the translation actually belongs to current Item C being used!

I am looking for two possible solutions:

  1. a proper solution, which may force to migrate existing data already used in the system 🙁 I hope to avoid this although it may not be too bad depending on the solution.

  2. Alternatively, what I was thinking is to add accessor and mutator to an appended attribute in the translation model, and set extra attribute direct_parent, to each translation in the retrieved collection, to the direct parent in the current context (using $direct_parent_item in the example code).
    The downside is that it is somewhat convoluted and confusing without the above context in the future, but would allow quick mapping on the front end.

Would this work? Do you have a better alternative how to modify the original design as stated above in a proper manner?

Duplicating the Item A translation is not an option, because we need to allow A to also control and update the translations inherited.

Tags:

2

Answers


  1. 0

    In my opinion a better way to store this data is using a json object. you can store your data like

    {
"id": 1,
"language_id": 2,
"item_id": 1,
"translation": "Hello World in Spanish",
"parents":[2,3]
}

    and if this is a table in a relational database you can use JSON object just for parents part like:

    {
"parents": [2,3]
}
    Login or Signup to reply.
  2. 0

    if you need to store multi level deep parent relations you need a new table that links each parent to all of it’s children, something like:

    parent_id | item_id | through | depth
1           2         null      1
1           3         2         2
2           3         null      1

    each time a new model is created you can loop the parent_id‘s in a recursive function and insert them in this table keeping track of through and depth. And on the model you would have 3 relations:

    /** direct parent via parent_id on the model */
public function parent();

/** 
 * list of parents via the pivot table,
 * can order via depth so that the list mimics calling
 * the parent relation for each model in the chain
 */
public function parents();

/** 
 * relation via the pivot table containing all children,
 * can filter by depth  ->children()->where('depth', 1)
 */
public function children();

    parents->first() would be the same as ->parent, parents()->whereHasTranslation($key)->first(), where whereHasTranslation is some custom scope you can implement, would give you the closest, or furthest depending or your order direction by depth, that has a translation key

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search