Accessible User Settings

Wow, that's a good answer but now I cannot choose! @Snapey Which solution would you choose and why? (do you favor one over the other?)

@bionary I'm not a fan of json columns, and if there is chance I'll want to update arbitrary values independently then I would probably use a related table

@Snapey That's great, I never used protected $with. What's the difference with protected $append ?

@vincent15000 append adds fields to the current model. with adds relations.

I very rarely use with because its easy to get in a mess and also run queries that you don't need.

For instance, if user loads settings and settings loads user an out of memory issue s the result

@Snapey

So it sounds like maybe using a "settings" table is ideal.

Will $append on the User model work with the "settings" table or does one HAVE to use $with to grab the relation?

You said you very rarely use with, so I'm a bit confused.

Thank you in advanced.

@bionary forget append, that's not relevant

if you want this extra data on every request, then use with. Bear in mind there will be now two database queries at the start of each request not one

I have thought of an additional benefit of json column which might also be considered. If you add an extra setting with the table approach you must migrate the database to add it, but with json you can just modify the model to manage this extra field in the existing column

@Snapey Yes, avoiding a bunch of migrations each time I need a new setting was precisely the impetus for going down the json column rabbit hole to begin with! I realize simply adding columns per setting is extremely straight forward either on the users table or a related settings table. But I'm actively building this out and not sure if I need 2 or 25 settings!

I wanted to experiment and see what it would look like to create, update and search where in that json column as that could potentially make using designated columns more attractive.

Note: I'm using Laravel 8

It turns out that it's not all too bad. You have to:

• create accessors in the model that also handle missing data. (The ?? syntax makes that easy.)
• add nested attributes to $fillable
• cast the json column as an array

User Model:

protected $fillable = [
        'name',
        'email',
        'password',
		'settings->background',
    ];

    /**
     * The attributes that should be hidden for arrays.
     *
     * @var array
     */
    protected $hidden = [
        'password',
        'remember_token',
    ];

    /**
     * The attributes that should be cast to native types.
     *
     * @var array
     */
    protected $casts = [
        'email_verified_at' => 'datetime',
		'settings' => 'array',
    ];

	public function getBackgroundAttribute()
	{
		return json_decode($this->attributes['settings'])->background ?? null;
	}

Here is how to read and write to the settings JSON column:

		//Creating or Updating
		$user = Auth::user();
		$user->update(['settings->background' => 'dark']);
		
		//Return Settings->background
		$background = Auth::user()->background;

		//Query Where (Eloquent)
		$darkBackgroundUsers = User::whereJsonContains('settings', ['background' => 'dark'])->get();

		//Query Where (Query Builder)
		$darkBackgroundUsers = DB::table('users')->where('settings->background', 'dark')->get();

I couldn't find much information on using Eloquent with whereJsonContains (for Laravel 8) Taking a look at the raw queries in telescope I can see this:

Eloquent resolves to:

select * from `users` where json_contains(`settings`, '{\"background\":\"dark\"}')

Query Builder resolves to:

select * from `users` where json_unquote(json_extract(`settings`, '$."background"')) = 'dark'

Hopefully this helps someone else and of course I'm open to ideas here.

@bionary have you looked at casting the settings to an array? then $user->settings['background'] or still use accessors but without json_decode in each

@Snapey Yes, I believe I am already casting to an array. It seems to do the casting after the accessor. I tested by simply returning return $this->attributes['settings'] from inside the accessor and it is a string not an array which is not what I expected.

@bionary but what do you get from $this->settings?

You are a good teacher @snapey !

I changed:

return $this->attributes['settings']['background'] ?? null;

to this working code:

return $this->settings['background'] ?? null;

Good catch.