Eloquent relation on data types (fields) table

Question

Here my tables, User is the classic Auth table:

DataTypes

+----+--------------+
| id |    field     |
+----+--------------+
|  1 | address      |
|  2 | mobile_phone |
|  3 | city         |
+----+--------------+

UserData

+----+----------+--------------+---------+
| id |  value   | data_type_id | user_id |
+----+----------+--------------+---------+
|  1 | Milan    |            3 |       1 |
|  2 | 99123233 |            2 |       1 |
+----+----------+--------------+---------+

My current crazy model:

class User extends Authenticatable {
    public function field($field){
        $field=DataType::where('field',$field)->first();
        return $this->hasMany('App\UserData')->where('datatype_id',(isset($field->id) ? $field->id : 0));
    }    
}

Of course it works fine when I have to find values:

auth()->user()->field('mobile_phone')->get();

But how do I set or update a new mobile phone?

Answer 1

Another approach is link User and DataTypes as a many to many relationship and use a scope to handle it.

First of all, change UserData table to dataTypeUser, so you could use default eloquent relationships. In any model put the belongsToMany relationship. User Model

public function dataTypes()
    {
        return $this->belongsToMany('App\DataType')
            ->withPivot(['value']);
    }

public function scopeField($query, $field)
    {
       return $query->whith(['dataTypes'=>function($q)use($field){
                  $q->where('data_types.field',$field);
              }]);
    }

DataType Model

public function users()
    {
        return $this->belongsToMany('App\User')
            ->withPivot(['value']);
    }

To get some value you can use $field = $user->field('something')->first() to save new data could use $user->field('something')->updateExistingPivot($field->id,['value'=>$newValue]).

Anyway, if you don't have many data of same type attached to your user (more than one phone number for example), it could be a better approach to use one single table extending user migration, or at last an userData with columns for each datatype. In small applications you have no problems but as your application grows performance will be a problem with many tables and many relationships.

To avoid a long declaration, you could overwrite magic methods __get and __set. Both are declared in Illuminate\Database\Eloquent\Model so put in your User Model:

public function __get($key)
    {
        return $this->getAttribute($key) ?? $this->getAttributesFromPivot($key);
    }


    public function __set($key, $value)
    {
        if(in_array($key,array_keys($this->original)))
            $this->setAttribute($key, $value);
        else
            $this->setAttributeFromPivot($key, $value);
    }

    public function setAttributeFromPivot($key, $value)
    {
        $this->dataTypes()->where('field',$key)->update(['value'=>$value]);
    }

    protected function getAttributesFromPivot($key)
    {
        return $this->dataTypes()
            ->where('field',$key)
                // use this to get only one value direct
                ->first()->pivot->value ?? null;
                // or use this to get all of them as array
//                ->get()
//                ->map(function($item){ return $item->pivot->value;}) ?? null;

    }

In this approach, you could use $user->city to get field city or another one replacing ->city. Or you could use $user->address = 'foo'; to set a new value in pivot table. Note that it will update database directly.

Now, if you are not comfortable to overwrite those methods, you don't need to. Change the signatures of setAttributeFromPivot and getAttributeFromPivot to public function getField($key) and public function setField($key, $value). Now you can use them as common methods.