How to eager load model relationships with defaults in Laravel?

Question

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:

```sql
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:

1. Authenticated user settings
2. Looked up user settings
3. 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.

```php
$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:

```sql
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:

![The correct SQL Resultset for single user settings](https://cdn.ozzu.com/uploads/1/post/603645/1/the-correct-sql-resultset-for-single-user-settings.png)

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:

```sql
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:

![The correct SQL Resultset for multiple user settings](https://cdn.ozzu.com/uploads/1/post/603645/1/the-correct-sql-resultset-for-multiple-user-settings.png)

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:

```php
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:

```php
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:

```php
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?

Brian Wozeniak · Accepted Answer

The short and unsatisfying answer to this question is that there are no built in Laravel ways to eager load a `belongsToMany` or `hasMany` relationship with defaults. Laravel does offer [default models](https://laravel.com/docs/11.x/eloquent-relationships#default-models) for `belongsTo`, `hasOne`, `hasOneThrough`, and `morphOne` relationships via a `withDefault` method.

With that said this is still possible with a more *hacky* solution by overriding some base queries that the relation builds up. Currently the way I am solving this for the `belongsToMany` relationship is via the following:

```php
    public function settings(): BelongsToMany
    {
        $relation = $this->belongsToMany(Setting::class, 'setting_user', 'users.id', 'settings.id');
        
        $relation->getBaseQuery()->joins = [];
        $relation->crossJoin('users');
        $relation->leftJoin('setting_user', function($join) {
            $join->on('setting_user.user_id', '=', 'users.id');
            $join->on('setting_user.setting_id', '=', 'settings.id');
        });

$relation->selectRaw(
            'settings.*, users.id as user_id, COALESCE(setting_user.value, settings.default_value) as value'
        );

$relation->orderBy('users.id')->orderBy('settings.id');

return $relation;
    }
```

This solution works for one user, multiple, users, or all users and eager loads the the query correctly to be efficient. This part is key:

```php
$relation = $this->belongsToMany(Setting::class, 'setting_user', 'users.id', 'settings.id');
```

This sets the pivot table just like before, however, instead of using `user_id` and `setting_id` from the pivot table directly, it instead will select it from the `users` table and the `settings` table where values from these are present in every row. We cannot use the pivot table directly for this because remember, the pivot table may have only a few rows present for overriding defaults, or there may be no rows present at all for the user and their associated settings. The next critical component:

```php
$relation->getBaseQuery()->joins = [];
$relation->crossJoin('users');
```

This will completely remove the JOIN on the `settings` table with the `setting_user` table, and then instead join all rows from the `users` table with all rows from the `settings` table since every user should have every setting. Remember, when defining our relationship above we are still potentially restricting what user's will be eligible for this `crossJoin` as you will see in the generated queries. For example when querying for 1 user with `User::find(1)->settings` the relevant query executed will look like this:

```sql
SELECT 
    settings.*, 
    users.id AS user_id, 
    COALESCE(setting_user.value, settings.default_value) AS value, 
    users.id AS pivot_users_id, 
    settings.id AS pivot_settings_id
FROM 
    settings
CROSS JOIN 
    users
LEFT JOIN 
    setting_user ON setting_user.user_id = users.id AND setting_user.setting_id = settings.id
WHERE 
    users.id = 1
ORDER BY 
    users.id ASC, 
    settings.id ASC;

```

When running `toArray` on the result, the array looks like this:

```php
Array
(
    [0] => Array
        (
            [id] => 1
            [slug] => theme
            [name] => Theme
            [default_value] => light
            [user_id] => 1
            [value] => light
            [pivot] => Array
                (
                    [users.id] => 1
                    [settings.id] => 1
                )

)

[1] => Array
        (
            [id] => 2
            [slug] => notifications
            [name] => Notifications
            [default_value] => disabled
            [user_id] => 1
            [value] => enabled
            [pivot] => Array
                (
                    [users.id] => 1
                    [settings.id] => 2
                )

)

[2] => Array
        (
            [id] => 3
            [slug] => sticky_header
            [name] => Sticky Header
            [default_value] => disabled
            [user_id] => 1
            [value] => disabled
            [pivot] => Array
                (
                    [users.id] => 1
                    [settings.id] => 3
                )

)

[3] => Array
        (
            [id] => 4
            [slug] => auto_watching
            [name] => Auto Watching
            [default_value] => enabled
            [user_id] => 1
            [value] => enabled
            [pivot] => Array
                (
                    [users.id] => 1
                    [settings.id] => 4
                )

)

[4] => Array
        (
            [id] => 5
            [slug] => home_threads
            [name] => Home Threads
            [default_value] => latest
            [user_id] => 1
            [value] => latest
            [pivot] => Array
                (
                    [users.id] => 1
                    [settings.id] => 5
                )

)

)
```

If I want users 1-5 with settings eager loaded with `User::with('settings')->whereIn('id', [1,2,3,4,5])->get()` then the resulting query generated from this would look like this:

```sql
SELECT 
    settings.*, 
    users.id AS user_id, 
    COALESCE(setting_user.value, settings.default_value) AS value, 
    users.id AS pivot_users_id, 
    settings.id AS pivot_settings_id
FROM 
    settings
CROSS JOIN 
    users
LEFT JOIN 
    setting_user ON setting_user.user_id = users.id AND setting_user.setting_id = settings.id
WHERE 
    users.id IN (1, 2, 3, 4, 5)
ORDER BY 
    users.id ASC, 
    settings.id ASC;
```

This will result in the correct settings associated to each user which is all **eager loaded** efficiently.

Brian Wozeniak

Database Schema

Accessing User Settings

How to do this via a SQL Query

Replicating this via Eloquent

1 Answer

Brian Wozeniak

Your Answer

User

Community

Market

Help Center

Legal

Company

Social Media

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