Laravel framework offers easy database in application manipulation like creating and using a migration file. It also allows you to join two tables in a query when you need data from two or more tables. For example, let’s say you have tables for users and their orders. If you join these tables, you can generate a useful report for the admin, such as 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 you to retrieve a result from multiple tables as if they are one. In this shot, we will learn how to use the join() method.
The join() method
The join() method is part of a query builder and is used to carry out 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 above, we perform the join from a controller and import the DB class.
Explanation
We first get users, which is our primary table, i.e., the table that relates to the other tables we want to join. Then, we chain users to the join() method. In this case, the first parameter or table we want to join to the users table is the contacts table. 'users.id', '=', and 'contacts.user_id' are constraints and they mean joining the contacts table, where user_id and contact user_id are same. We then use the select method to filter the columns we need for the application. We also chain the get() method, which retrieves the query results for us. Finally, we return the retrieved query from two database tables.