Deep related values ​​for the page in Laravel database models

Question

A page has several elements, and they are linked using the page_to_elements table. Each element has several element_fields and is linked using element_to_element_fields . Each element_field has a type and is linked using the element_to_element_fields table. The values ​​of each element_field inside an element have a value (eitehr in value_char, value_text or value_num), which is stored in the element_values table.

The following is the structure of the database:

 pages: id|name elements: id|name element_fields_types (sql_type can be char, text or num): id|name|sql_type element_fields (names can be title, intro, content, link, number, etc etc): id:element_field_type_id|name element_to_element_fields: id|element_id|element_field_id page_to_elements: id|page_id|element_id element_values: id|page_id|element_id|page_to_element_id|element_field_id|value_char|value_text|value_num 

What I'm looking for is a good hasManyToMany solution to get all values ​​when I request the page id. Now I have several loops and creating arrays to get such a structure (where the values ​​are specified from the correct column name based on what was set in element_fields ):

 $page = array( 'elements' => array( [0] => array( 'element_name_here' => array( 'fields' => array( [0] => array( 'field_name_here' => 'Field value', 'field_name_here' => 'Field value', 'field_name_here' => 'Field value', 'field_name_here' => 'Field value' ), [1] => array( 'field_name_here' => 'Field value', 'field_name_here' => 'Field value', 'field_name_here' => 'Field value', 'field_name_here' => 'Field value' ), [2] => array( 'field_name_here' => 'Field value', 'field_name_here' => 'Field value', 'field_name_here' => 'Field value', 'field_name_here' => 'Field value' ), ) ) ), [1] => array( 'element_name_here' => array( 'fields' => array( [0] => array( 'field_name_here' => 'Field value', 'field_name_here' => 'Field value', 'field_name_here' => 'Field value', 'field_name_here' => 'Field value' ), [1] => array( 'field_name_here' => 'Field value', 'field_name_here' => 'Field value', 'field_name_here' => 'Field value', 'field_name_here' => 'Field value' ), [2] => array( 'field_name_here' => 'Field value', 'field_name_here' => 'Field value', 'field_name_here' => 'Field value', 'field_name_here' => 'Field value' ), ) ) ), ) ); 

I need something like below to create the above array:

 $page = Page::find($id); print_r($page->getValues->toArray()); 

I have some experience using ownToMany or hasManyToMany, but this never happens.

Any help would be greatly appreciated.

Answer 1

Your layout is quite complex, but I believe that you can achieve the structure you want by looking forward to loading relationships and using collection methods.

 <?php class Page extends Eloquent { public function elements() { return $this->belongsToMany(Element::class, 'page_to_elements'); } public function values() { return $this->hasMany(Value::class); } public function getValues() { return $this->values() // Eager-load the value element, the value field and its type ->with(['element', 'field.type'])->get() // Group all the values by page element ->groupBy('page_to_element_id')->values() // Group the values of each page element by the element name ->groupBy(function ($values) { return $values->first()->element->name; }) // Iterate each page element ->map(function ($values, $element) { // Make an array with the element name as key and its fields as value return [ $element => [ // Group the values by element field 'fields' => $values->groupBy('element_field_id')->values() // Make an array with the field names and values for each element field ->map(function ($values) { return $values->pluck('value', 'field.name')->all(); })->all(), ], ]; })->all(); } } class Element extends Eloquent { public function pages() { return $this->belongsToMany(Page::class, 'page_to_elements'); } public function fields() { return $this->belongsToMany(Field::class, 'element_to_element_fields'); } public function values() { return $this->hasMany(Value::class); } } class Field extends Eloquent { public function type() { return $this->belongsTo(Type::class); } public function values() { return $this->hasMany(Value::class); } } class Type extends Eloquent { public function fields() { return $this->hasMany(Field::class); } } class Value extends Eloquent { public function page() { return $this->belongsTo(Page::class); } public function element() { return $this->belongsTo(Element::class); } public function field() { return $this->belongsTo(Field::class); } public function getValueAttribute() { $type = $this->field->type->sql_type; return $this->getAttribute('value_'.$type); } }