How can I create a hasMany or BelongsToMany Eloquent relationship in Laravel to display all settings for a user, including default values for settings they haven't updated? In other words, I’m trying to eager load model relationships with defaults in Laravel. Here's the scenario:
Database Schema
I have three tables:
users (id, name, email)
settings (id, slug, name, default_value)
setting_user (setting_id, user_id, value) – a pivot table for user-specific settings
Here is an example of settings defined. This is what would be available to each user:
id | slug | name | default_value |
---|---|---|---|
1 | theme | Theme | light |
2 | notifications | Notifications | disabled |
3 | sticky_header | Sticky Header | disabled |
4 | auto_watching | Auto Watching | enabled |
5 | home_threads | Home Threads | latest |
Then with the pivot table for users and settings might look like this:
user_id | setting_id | value |
---|---|---|
1 | 2 | enabled |
2 | 1 | dark |
2 | 2 | enabled |
3 | 5 | popular |
5 | 3 | enabled |
You will notice in the pivot table that the user only has settings defined if they are something other than the default. If you have 100,000 users, this would save a ton of space and allow queries to be performed faster because we are limiting the amount of rows in this table to only something set other than the default.
Accessing User Settings
To access user settings via Laravel models, there would typically be three ways in this would happen:
- Authenticated user settings
- Looked up user settings
- Eager loaded user settings for a group of users
For any of the cases, I would want every setting for the user which would include default settings or overridden settings by the user.
$settings = Auth::user()->settings; // authenticated user settings
$settings = User::find(4)->settings; // looked up user settings
$users = User::with('settings')->all();
foreach($users as $user) {
$settings = $user->settings; // user settings that were eager loaded
...
}
How to do this via a SQL Query
To get all settings, including defaults, I need a left join. This SQL query achieves the desired result:
SELECT u.id as user_id, s.id as setting_id, s.slug, s.name, COALESCE(su.value, s.default_value) as value
FROM (settings s, users u)
LEFT JOIN setting_user su ON (s.id = su.setting_id AND u.id = su.user_id)
WHERE u.id = 1
With that query obtaining user 1 settings, you get a result set that looks like this:
Notice how it shows all of the settings for the user, and the notifications setting is enabled due to the entry in the setting_user
pivot table overriding the default value of disabled
.
If I want to get the group of users between IDs 1-5, the SQL Query to do that would be:
SELECT u.id as user_id, s.id as setting_id, s.slug, s.name, COALESCE(su.value, s.default_value) as value
FROM (settings s, users u)
LEFT JOIN setting_user su ON (s.id = su.setting_id AND u.id = su.user_id)
WHERE u.id IN (1,2,3,4,5)
ORDER BY user_id;
With that you get a result that looks like this:
Notice here that again it shows all of the settings for all of the selected users utilizing the defaults, but also using the correct values from the pivot table for the settings that were overridden. Additionally you can see user id 4 there which only uses default settings as they had no entries in the pivot table.
Replicating this via Eloquent
In Laravel, I'm trying to replicate this using Eloquent. Here is what the belongsToMany
relationship had looked like:
public function settings()
{
return $this->belongsToMany(Setting::class)->withPivot('value');
}
Unfortunately this only returns any values that exist in the setting_user
pivot table where the user had something other than the defaults. Thus another attempt:
public function settings()
{
return $this->belongsToMany(Setting::class)->withPivot('value')
->leftJoin('settings as s2', function($join) {
$join->on(...);
});
}
This doesn't seem right, and it doesn’t efficiently replicate the SQL query above. Another idea:
public function settings()
{
return DB::table( 'settings' )
->leftJoin( 'setting_user', function ( $join ) {
$join->on( 'settings.id', '=', 'setting_user.setting_id' )
->on( 'setting_user.user_id', '=', Auth::user()->id );
})
}
This approach hardcodes the authenticated user ID, making it less elegant.
Thus, that brings me back to the actual question:
How can I eager load a belongsToMany
or hasMany
relationship with defaults in Laravel?