12.7k times

Any Laravel users on here? I would still consider myself a Laravel newbie, but I am trying to create an Eloquent relationship on my User Model so that I can have a user display their settings. Ideally from a controller or view I would prefer to be able to write code like this:


Which would give me all of the settings for the currently authenticated user. I would also like to be able to write something like these:


Which would let me display the settings for any particular user, or with the 2nd line display the settings for all users if I were to throw that in a foreach loop.

The part that makes this more complex is I have a table with all the settings possible for a user and the default values that go with those settings, and then a pivot table setup for any user who has specifically updated their settings. So the tables I have in a simplified manner are:

users (id, name, email)
settings (id, slug, name, default_value)
setting_user (setting_id, user_id, value)

On the User Model the first thing I tried was creating a relationship that almost did the trick:

public function settings()
    return $this->belongsToMany('Ozzu\Models\Setting')->withPivot('value');

This would return almost exactly what I want, but it would only display the settings that the user had updated or changed. So for example if I had 14 settings for each user, and this particular user only changed 5 of them, then by doing something like:


You would only see the 5 settings that they had actually updated. The rest of the settings and their default values would not come through. This was the result set:

Wrong SQL Result Set

What I really need for this to work is a left join. The following query does exactly what I need:

SELECT, s.slug,, s.default_value, su.value
FROM (settings s, users u)
LEFT JOIN setting_user su ON ( = su.setting_id AND = su.user_id)

With that query you get a result set that looks like this:

Correct SQL Result Set

That is perfect, notice if they set a value then the value column will display it, otherwise it will show null if they haven't set it yet.

I would imagine with Laravel I am going to have to do some sort of join with a closure. Any ideas how I could accomplish the above with a User Model method? I feel like something so simple should be possible. I have started to try something like this:

public function settings()
    return $this->belongsToMany('Ozzu\Models\Setting')->withPivot('value')
       ->leftJoin('settings as s2', function($join) {

Just doesn't seem right, and to have to do another left join on top of that relationship would make a query not as efficient as I mentioned above.

Maybe something along the lines like?:

public function settings()
    return DB::table( 'settings' )
        ->leftJoin( 'setting_user', function ( $join ) {
            $join->on( '', '=', 'setting_user.setting_id' )
                 ->on( 'setting_user.user_id', '=', Auth::user()->id );

The main issue there is I am passing in the fixed authenticated user id. I could create a method argument to pass that in, but then that starts to make things not as elegant:


Plus I don't think this can even chain like that since nothing is really connected to the user model.

Any thoughts on how to achieve what I want?

add a comment

0 Answers

  • Votes
  • Oldest
  • Latest