BW

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:

Auth::user()->settings

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:

User::find(4)->settings
User::all()->settings

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:

Auth::user()->settings

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:

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

SELECT u.id, s.slug, s.name, s.default_value, su.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 = 7

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

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) {
           $join->on(...);
       });
}

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( 'settings.id', '=', '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:

User::find(4)->settings(4)

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

0 Answers

  • Votes
  • Oldest
  • Latest