Single Model with belongsToMany : How to query?

Published 1 month ago by yougotnet

I have users who own Accounts (Account Model) and I have users who share accounts with other users by way of Many to Many Relationship (account_user table).

Does Eloquent provide an easy way to retrieve the accounts for a particular user that includes the accounts he owns and the accounts that are shared with him?

Best Answer (As Selected By yougotnet)
Vilfago

You can do it in the pivot table account_user with three column :

  • account_id
  • user_id
  • is_owner (bool)

If you want only the account for which the user is the owner, you add a where clause on the "is_owner" column, and otherwise you can just take $user->accounts();

nikos
nikos
1 month ago (6,420 XP)

I had a similar problem. The cleanest solution I found was to set both one-to-many and many-to-many relation between two tables. Then set up proper relations.. For example you can call relations ownedAccounts and sharedAccounts(from users perspective). Then I suppose you could get all of them by union:

$ownedAccounts = $user->ownedAccounts();

$allAccounts = $user->sharedAccounts()->union($ownedAccounts)->get();
yougotnet

Your comment helped and found that I can do it all in one command:

$allAccounts = $user->accounts()->get()->union($user->sharedAccounts);

yougotnet

Ok, so my last statement doesn't work. It works if you have 1 account either owned or shared; but if you own an account and are shared on an account, then only the owned accounts are returned.

I tried your sample and it doesn't seem to work either. I think the problem is Shared Accounts contain a pivot property where owned accounts don't and since they don't match, I think the union is being ignored.

Vilfago

You can do it in the pivot table account_user with three column :

  • account_id
  • user_id
  • is_owner (bool)

If you want only the account for which the user is the owner, you add a where clause on the "is_owner" column, and otherwise you can just take $user->accounts();

yougotnet

So the idea is all accounts (Owned and Shared) would exists in the pivot table?

Vilfago

Yes, from my side it's easier this way

yougotnet

That seem to work good; how do I update the is_owner when I Attach the account to the user?

nikos
nikos
4 weeks ago (6,420 XP)

@Vilfago I think that it would be more complicated to query relations separately later.

@yougotnet there is an error in your statement. It should be like this

$allAccounts = $user->accounts()->union($user->sharedAccounts())->get();

The difference is that you called union on collection, and this way you're calling it on database level.

Vilfago

Why complicated ?

class User extends Model
{
    /**
     * The roles that belong to the user.
     */
    public function allAccounts()
    {
        return $this->belongsToMany('App\Account')->withPivot('is_owner');
    }

    public function ownAccounts()
    {
        return $this->belongsToMany('App\Account')->withPivot('is_owner')->wherePivot('is_owner', 1);
    }

    public function sharedAccounts()
    {
        return $this->belongsToMany('App\Account')->withPivot('is_owner')->wherePivot('is_owner', 0);
    }
}
yougotnet

Sorry, retrieving them is easy; I was trying to figure out how to set is_owner during the attach process.

Vilfago

oh yes, sorry

$user->allAccounts()->updateExistingPivot($accountId, ['is_owner' => 1]);

// or 

$user->allAccounts()->attach($accountId, ['is_owner' => 1]);
nikos
nikos
4 weeks ago (6,420 XP)

Definitely cleaner...

Vilfago

As always, it depends what you need the most. If it's a one time query, I'll go with your solution.

If he rarely update the owner's account, I think my solution is better has he can get what he wants with only one relation.

D9705996
D9705996
4 weeks ago (127,290 XP)

@yougotnet - did either of the solutions fix your problem? If so can you please mark as answer so others can find this thread

Please sign in or create an account to participate in this conversation.