[FIXED] Can i have 2 foreign keys as id from users table laravel

Issue

I have 2 tables User and post_request, i also have 3 user's A, B and C. Basically user A request for a service and i randomly assign user B or C and save the records in post_request table, how do i get User A and User B's id so they can chat to get more details of the request? Have tried

<?php namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;
use App\Models\PostRequest;
use App\Models\User;

class ChatController extends Controller
{
    public function getMessages()
    {
        return view('user/message'); //response()->json($contacts);
    }

    public function getContact()
    {
        $email = Auth::user()->email;

        $items = DB::table('post_requests')
            ->where([["email", "=", $email]])
            ->limit('1')
            ->orderBy('id', 'DESC')
            ->get();

        foreach($items as $item){
            $artisan_email = $item->email;

            $contacts = DB::table('users')
                 ->where([["email", "=", $artisan_email]])
                 ->get();
            
            return response()->json($contacts);
        }
    }
}

Solution

Yes, you can have two foreign keys from a table going to a different table.

In this case, if I've understood what you're asking, you have users, and users can both request, and be assigned, a service / post.

So your users table : id / first_name / surname / email_address (for example)

And then you posts table : id / requester_id / assignee_id

where requester_id and assignee_id are both tied to the ID on the user table, just for different records.

Edit :

Imagine you have the following users :

id / first_name / surname / email_address (for example) 1 / Billy / User / billy@user.com 2 / Tony / Tiger / tony@tiger.com

There's no reason why your entry in the post table cannot look like this :

id / requester_id / assignee_id 1 / 1 / 2

All that the foreign key does is it ensures integrity. You're saying that the requester_id of a post has to correspond to a user in the user table.

If the assignee_id of a post also has to correspond to a user in the user table, it doesn't mean that it has to correspond to the same user...

Answered By - Giles Bennett