Asked
Updated
Viewed
13.7k times

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:

idslugnamedefault_value
1themeThemelight
2notificationsNotificationsdisabled
3sticky_headerSticky Headerdisabled
4auto_watchingAuto Watchingenabled
5home_threadsHome Threadslatest

Then with the pivot table for users and settings might look like this:

user_idsetting_idvalue
12enabled
21dark
22enabled
35popular
53enabled

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.

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

The correct SQL Resultset for single user settings

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:

The correct SQL Resultset for multiple user settings

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?

add a comment
0

1 Answer

  • Votes
  • Oldest
  • Latest
Answered

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

    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:

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

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

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:

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:

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.

add a comment
0