How to get related data from two tables while there are two Foreign keys related to One Primary key in Laravel Framework

Question

The question that I want to ask looks silly but it took more than 4 days of my life and I'm still struggling with this problem and I found no good solution up to this point!

As you see in the image I attached, there are two tables named "Contacts" and "Users".

Each user can send contact request to other users if there are no previous/pending incoming or outgoing contact requests from target users.

For example, USER_1 can send contact request to USER_2 if there is no pending request from USER_2 to USER_1.

If any of USER_1 or USER_2 has already sent contact request and other user accepted the request, then they are in each other's contacts list!

Now, I want to get data from Users and Contacts tables at the same time in Laravel Eloquent or any other methods in the Laravel Framework.

For Example if USER_1 has sent contact request to USER_2 AND USER_2 accepted the contact request, I want to show USER_1 name and profile in USER_2 contact list AND I want to show USER_2 name and profile in USER_1 contact list.

I know it's confusing but I really want to find a solution for this problem to proceed with this project. Also if there is any problem with naming convention for table columns PLEASE let me know or give me better suggestion.

If anybody is there who can help me, please don't hesitate and post your answer. Thank you.

I also attached some codes that I tried up to this point and yes it did not work properly:

I created local scope method in Contacts Model and added this code

public function scopeContacts ($query) {
        return $query->join('users', function ($join) {
            $join->on('contacts.user_id', '=', 'users.id')
            ->on('contacts.target_id', '=', 'users.id');
        })
        ->where('user_id', '=', auth()->user()->id)
        ->where('status', '=', 'accepted')
        ->orWhere('target_id', '=', auth()->user()->id)
        ->where('status', '=', 'accepted')
        ->get();
}

Code above returns null or empty array!

Answer 1

You have to create a pivot table called contact_users that may contain user_id and contact_id.

This is because, when you have to communicate two (or more) n:n tables you MUST create a pivot table.

Laravel support this by providing the belongsToMany() method, read more HERE

Laravel docs gives a great example that can help you.