php - Laravel retrieve data with condition from many-to-many condition

Question

I have three tables:

email_accounts (list of business emails)
-------------
id
email
sender_name

users
------
id
name
email

email_accounts_users
--------------------
email_account_id
user_id

And the eloquent relationship is declared as:

    User
    ------
    public function emailAccounts()
    {
        return $this->belongsToMany(EmailAccount::class,'email_accounts_users');
    }

    EmailAccount
    ------------
    public function users()
    {
        return $this->belongsToMany(User::class,'email_accounts_users');
    }

I am trying to fetch rows from email_accounts that belongs to current user. This could be correctly fetched as:

$email_acc = \DB::table('email_accounts')
            ->join('email_accounts_users', 'email_accounts_users.email_account_id', 'email_accounts.id')
            ->join('users', 'users.id', 'email_accounts_users.user_id')
            ->select('email_accounts.*')
            ->where('user_id',auth()->user()->id)
            ->get();
dd($email_acc);

However, I am trying to get with laravel eloquent model. So, I tried as follows:

$email_acc = EmailAccount::with(['users' => function(){
    $query->where('users_id',auth()->user()->id)
}])->get();
dd($email_acc);

However, its returning all the emails from email_accounts, rather its filtering the data from users table and sending only the currently logged in user. from users table.

However, my requirement is to fetch emails form email_accounts that belong to currently logged in users.

Like

标签： phplaravel

Answer 1

Specify foreign keys as follows

public function emailAccounts()
    {
        return $this->belongsToMany(EmailAccount::class,'email_accounts_users', 'id', 'email_account_id' );
    }

    EmailAccount
    ------------
    public function users()
    {
        return $this->belongsToMany(User::class,'email_accounts_users', 'id', 'user_id');
    }