How do I perfome muiltiple joins in more than one table in laravel without eloquent?

Question

I have four tables users, customers,towns and parcels. The parcels table has sender and recipient as foreign keys on customers table, from_town and to_town are foreign keys on towns table and to_agent as foreign key on users table. I am fetching the parcels table and joining with the other three tables using the following code as guided by this question

        $parcels = DB::table('parcels as p')
                ->join('customers as c1', 'p.sender', '=', 'c1.id')
                ->join('customers as c2', 'p.recipient', '=', 'c2.id')
                ->join('towns as t1', 'p.from_town', '=', 't1.id')
                ->join('towns as t2', 'p.to_town', '=', 't2.id')
                ->join('users as u1', 'p.to_agent', '=', 'u1.id')
                ->get();
         dd($parcels);

This is the result I am getting

Illuminate\Support\Collection {#373 ▼
     #items: array:1 [▼
0 => {#377 ▼
  +"id": 10
  +"agent": 1
  +"sender": 3
  +"recipient": 9
  +"length": 33
  +"width": 56
  +"height": 51
  +"weight": 100
  +"cost": 500.0
  +"description": "Et aut nulla quaerat"
  +"from_town": 1
  +"to_town": 5
  +"to_agent": 10
  +"received_by_agent": 1
  +"shipped": 0
  +"received_at_destination_agent": 0
  +"picked_by_recipient": 0
  +"comments": "comment"
  +"created_at": "2022-05-07 04:44:59"
  +"updated_at": "2022-05-08 12:46:45"
  +"first_name": "TaShya"
  +"second_name": "Mara"
  +"last_name": "Mohammad"
  +"email": "sabina10@example.net"
  +"phone_number": "4"
  +"id_number": "664"
  +"name": "Garret Dietrich"
  +"status": "active"
  +"email_verified_at": "2022-05-07 04:44:58"
  +"password": "$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi"
  +"role": "Agent"
  +"remember_token": "qQIsI1RrQO"
}
 ]
 #escapeWhenCastingToString: false
}

The response does not get sender details and from_town details, it only gets recipient and to_town details. How can I fetch the sender and from_town details?

score 0 · Answer 1 · answered May 10 '22 at 08:27

$parcels = DB::table('parcels as p')
        ->join('customers as c1', 'p.sender', '=', 'c1.id')
        ->join('customers as c2', 'p.recipient', '=', 'c2.id')
        ->join('towns as t1', 'p.from_town', '=', 't1.id')
        ->join('towns as t2', 'p.to_town', '=', 't2.id')
        ->join('users as u1', 'p.to_agent', '=', 'u1.id')
        ->select('u1.name','u1.email','t1.town_detail')
        ->get();
dd($parcels);

You could select only columns which you need from multiple tables as ->select('u1.name','u1.email','t1.town_detail') you could add columns from other tables as well.

If more than two columns has the same name you could differentiate them as :

 ->select('u1.id as user_id','t1.id as town_id')

How do I perfome muiltiple joins in more than one table in laravel without eloquent?

1 Answers1