Deep linked values for a page in Laravel database models

Question

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

Below is how the database structure is:

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 am looking for is a good hasManyToMany solution to get all values when I request a page id. I now have multiple loops and array creations to get a structure like this (where the values are from the correct column name based on what was set in the 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'
                    ),
                )
            )
        ),

    )
);

So I need something like below to produce above array:

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

I have some experience with belongsToMany or hasManyToMany, but never this deep.

Any help would be much appreciated.

Answer 1

Your schema is quite complex, but I believe you can achieve the structure you want with eager-loading the relations and leveraging 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);
    }
}