Accessing multiple users withPivot in one query

$users = User::with(['accounts' => function($q) {
    $q->where('is_owner', 1); }])
    ->find($users_id);

//$users_id should be an array of users ID

That works for a list of Users with accounts; but I was hoping to get results of Accounts with the user and pivot.

You can try that

$users_id = [1,2,3];

$accounts = Account::whereHas('users', function ($query) use($users_id) {
    $query->where('is_owner', 1)
    ->whereIn('user_id', $users_id); 
})->with('users')
->get();

Hope it'll work

Almost there! Now how can I get withPivot on each Account?

This should be made in your relation I think

class Account extends Model

public function users(){
    return $this->belongsToMany('App\User')->withPivot('is_owner');
}

//***************

class User extends Authenticatable

public function accounts(){
    return $this->belongsToMany('App\Account')->withPivot('is_owner');
}

For information, if needed, you can also filtering the relation in the model

return $this->belongsToMany('App\User')->wherePivot('is_owner', 1);

Yes, I have that set in both the Account and User models.

In your example, the results return Account->User->Pivot; unfortunately I really need Account->Pivot and Account->User as two separate properties of Account.

Ok, in this case you will need a Model for that.

Go on https://laravel.com/docs/5.7/eloquent-relationships#has-many-through and scroll up to Defining Custom Intermediate Table Models

And then, you will be able to set your relation with this new Model

Your example below returns all users and not just is_owner 1:

$users_id = [1,2,3];

$accounts = Account::whereHas('users', function ($query) use($users_id) {
    $query->where('is_owner', 1)
    ->whereIn('user_id', $users_id); 
})->with('users')
->get();

Try this

$users_id = [1,2,3];

$accounts = Account::whereHas('users', function ($query) use($users_id) {
    $query->wherePivot('is_owner', 1)
    ->wherePivotIn('user_id', $users_id); 
})->with('users')
->get();

But probably useless, if you create a Model for your pivot table.

I'm concerned with using a custom pivot model because it could/would create multiple DB queries in order to return the data in the format needed.

If a Manager was to pull 1000 accounts that belong to several users; I am thinking the custom model would have to organize the data with multiple queries to the DB in order to organize the data correctly.

Do you know if this would be the case?

Can you post a (JSON) example of how the result should look like?

Here is the JSON.

This works fine if I am pulling for just 1 user, but my application has a management hierarchy where a manager would have several users and need to pull a report of all accounts of all users where the user is the owner and the accounts are alphabetical. So the data layout for a manager needs to be the same when the user pulls it; otherwise I would have to create two different reports, etc.

[{
    "id": 23,
    "user_id": 6,
    "is_parent": 0,
    "parent_id": 0,
    "accttype": null,
    "status": "New",
    "name": "Joes Account",
    "address": null,
    "city": null,
    "state": null,
    "zip": null,
    "latitude": "0.00000",
    "longitude": "0.00000",
    "phone": null,
    "mobile": null,
    "fax": null,
    "website": null,
    "sic": null,
    "location": null,
    "taxid": null,
    "employee_count": null,
    "pain_point": null,
    "leadsource": null,
    "leadsourcename": null,
    "contactname": null,
    "contacttitle": null,
    "contactphone": null,
    "contactemail": null,
    "industry": null,
    "followupdate": null,
    "followupnote": null,
    "description": null,
    "comment": null,
    "created_at": "2018-09-20 15:14:48",
    "updated_at": "2018-09-20 15:14:48",
    "deleted_at": null,
    "pivot": {
        "user_id": 7,
        "account_id": 23
    }
}, {
    "id": 24,
    "user_id": 7,
    "is_parent": 0,
    "parent_id": 0,
    "accttype": null,
    "status": "New",
    "name": "John's Account",
    "address": null,
    "city": null,
    "state": null,
    "zip": null,
    "latitude": "0.00000",
    "longitude": "0.00000",
    "phone": null,
    "mobile": null,
    "fax": null,
    "website": null,
    "sic": null,
    "location": null,
    "taxid": null,
    "employee_count": null,
    "pain_point": null,
    "leadsource": null,
    "leadsourcename": null,
    "contactname": null,
    "contacttitle": null,
    "contactphone": null,
    "contactemail": null,
    "industry": null,
    "followupdate": null,
    "followupnote": null,
    "description": null,
    "comment": null,
    "created_at": "2018-09-20 15:17:28",
    "updated_at": "2018-09-20 15:17:28",
    "deleted_at": null,
    "pivot": {
        "user_id": 7,
        "account_id": 24
    }
}]

The accounts of all selected users should be combined in a single array/collection?

Yes, but with the Pivot information.

$accounts = User::with('accounts')->find($ids)->pluck('accounts')->flatten();?

Awesome! That works really well; any chance I would be able to get the User property added to the account like the Pivot property.

The issue I am having with your sample is that it pulls all accounts and not just is_owner 1.

$accounts = User::with('accounts')->find($ids)->pluck('accounts')->flatten();

I forgot that:

$accounts = User::with(['accounts' => function($query) {
    $query->where('is_owner', 1);
}])->find($ids)->pluck('accounts')->flatten();

Try this:

$users = User::with(['accounts' => function($query) {
    $query->where('is_owner', 1);
}])->find($ids);
foreach($users as $user) {
    foreach($user->accounts as $account) {
        $account->user = $user;
    }
}
$accounts = $users->pluck('accounts')->flatten();

Here is what I ended up doing; mainly because this would only hit the DB once. I was concerned with the other approaches becoming too complex and create multiple hits against the DB.

$accounts = DB::table('account_user')
                    ->join('accounts', 'account_user.account_id', '=', 'accounts.id')
                    ->join('users', 'account_user.user_id', '=', 'users.id')
                    ->where('account_user.is_owner',1)
                    ->whereIn('account_user.user_id', User::hierarchy(Auth::user()->id)->pluck('id'))
                    ->whereNull('accounts.deleted_at')
                    ->select('accounts.*', 'account_user.is_owner', 'account_user.access', 'users.fullname')
                    ->get()->toArray();

This gives me all the accounts along with is_owner and access in the flat array.

I appreciate everyone's help; I know I probably was a pain through this.