Laravel framework offers easy database in application manipulation like creating and using a migration file. It even allows us to join two tables in a query when we need data from two or more tables. For instance, suppose we have tables for users and their orders. If we join these tables, we can generate a useful report for the admin, like how many orders each user has made. Let’s learn how we can query from two database tables with a condition in Laravel.
Joining tables also allows us to retrieve a result from multiple tables as if they are from one. In this tutorial, we will learn how to use the join() method for data retrieval from multiple tables.
join() method
The join() method is a part of the query builder and can be used to perform the table joining table operation in Laravel.
Syntax
＄users = DB::table('users')
->join('contacts', 'users.id', '=', 'contacts.user_id')
->join('orders', 'users.id', '=', 'orders.user_id')
->select('users.*', 'contacts.phone', 'orders.price')
->get();Parameters
The join() method receives multiple parameters:
- The table to be joined
- Optional constraints for the join
Example
＄usersDetails = DB::table('users')
->join('contacts', 'users.id', '=', 'contacts.user_id')// joining the contacts table , where user_id and contact_user_id are same
->select('users.*', 'contacts.phone')
->get();
return ＄usersDetails;
}In the example shown above, we are doing the join operation from a controller and importing the DB class.
Explanation
We are first getting the users, from our primary table, i.e. the table that relates to the other tables we are trying to join with. Then, we chain users to the join() method. Here, the first parameter we use to join to the users table is the contacts table. 'users.id', '=', and 'contacts.user_id' are the constraints, and they are joining the contacts table, where user_id and contact user_id are same. We will then use the select method to filter the columns required for the application. We will also chain the get() method, which retrieves the query results for us. Finally, we return the retrieved query from two database tables.
