Laravel eloquent joining two tables override same column issue-CodePudding

I have two tables users and payment_request

my user table as follow

and the payment_request table as follow

I want to join payment_request table to user table using laravel eloquent here is my code

$user_payment_request_list = PaymentRequest::join('currency','currency.id','=','payment_requests.currency_id')
    ->join('users','users.id','=','payment_requests.sender_id')
    // ->join('users','users.id','=','payment_requests.receiver_id')
    ->get([
        'currency.name as currency_name',
        'payment_requests.amount',
        'payment_requests.status',
        'payment_requests.description',
        'payment_requests.sender_id',
        'users.name as sender_name',
        'users.name as receiver_name',
        'payment_requests.transaction_id',
        'payment_requests.branch_id',
        'payment_requests.created_at',
        'payment_requests.updated_at',
    ])
    ->where("sender_id",$id);

I get the output where it overrides the receiver_id and give me same result from sender_id

CodePudding user response：

To avoid overriding columns, you have to select the columns you want to use. In general selecting all the columns from the first table, combined with the columns you need from the joined table, is the approach i often use.

->select(['payment_requests.*', 'users.name as sender_name', 'users.name as receiver_name'])

CodePudding user response：

Try this query:

$user_payment_request_list = PaymentRequest::join('currency','currency.id','=','payment_requests.currency_id')
            ->join('users as sender_user', 'sender_user.id','=','payment_requests.sender_id')
             ->join('users as receiver_user', 'receiver_user.id','=','payment_requests.receiver_id')
            ->get([
                'currency.name as currency_name',
                'payment_requests.amount',
                'payment_requests.status',
                'payment_requests.description',
                'payment_requests.sender_id',
                'sender_user.name as sender_name',
                'receiver_user.name as receiver_name',
                'payment_requests.transaction_id',
                'payment_requests.branch_id',
                'payment_requests.created_at',
                'payment_requests.updated_at',
            ])
            ->where("sender_id", $id)
            ->get();

I use SQL AS command for rename the User table.