How to Union Queries in Laravel Query Builder

Introduction

When building complex queries in Laravel, sometimes you need to combine the results of multiple queries into a single result set. This is where the SQL UNION operator comes in handy, and Laravel’s Query Builder provides a fluent interface to perform union operations with ease. In this tutorial, you’ll learn how to use the union feature in Laravel’s Query Builder.

Understanding UNION in SQL

The SQL UNION operator is used to combine the result sets of two or more SELECT queries into a single result set. The queries must have the same number of columns, and the columns must have similar data types. The ORDER BY clause can only be used in the last SELECT query.

Basic Unions in Laravel

In Laravel, you can use the union() method to perform a union operation. Here is a simple example:

$first = DB::table('first_table')->where('status', 'active');
$second = DB::table('second_table')->where('status', 'active');
$union = $first->union($second)->get();

In the above example, we’re querying two tables, ‘first_table’ and ‘second_table’, for records where ‘status’ is ‘active’. We then combine the results using union().

Advanced Union Queries

Sometimes, you may need to use more advanced union queries that require subqueries or specific ordering and limiting of results.

Union With Subqueries

$first = DB::table('first_table')
	->select('name')
	->where('status', 'active');

$sub = DB::table('second_table')
	->select('name')
	->where('status', 'inactive')->orderBy('name');

$union = $first->union($sub);
$results = DB::query()->fromSub($union, 'sub_query')->get();

This code demonstrates a union query with a subquery. Note the use of fromSub() to give the union query a name.

Union All Queries

Laravel also supports the unionAll() method, which does not remove duplicate rows:

$first = DB::table('first_table')->where('status', 'active');
$second = DB::table('second_table')->where('status', 'active');
$unionAll = $first->unionAll($second)->get();

Ordering and Limiting Union Queries

Ordering and limiting results in a union operation involves wrapping the union as a subquery:

$first = DB::table('users')->where('votes', '>', 100);
$second = DB::table('users')->where('name', 'John');
$users = $first->union($second);
$allUsers = DB::query()->fromSub($users, 'all_users')->orderBy('votes', 'desc')->take(10)->get();

This will give us the combined result set of two queries, ordered by ‘votes’ and limited to 10 results.

Pagination with Union Queries

If you need to paginate results from a union query, you can use the paginate() method:

$first = DB::table('users')->where('votes', '>', 100);
$second = DB::table('users')->where('name', 'John');
$users = $first->union($second);
$paginatedUsers = DB::query()->fromSub($users, 'paginated_users')->orderBy('votes', 'desc')->paginate(15);

The above code will create paginated results out of the union query, allowing for efficient data display and navigation.

Combining Unions With Joins and Other Complex Queries

Laravel’s Query Builder allows you to combine union statements with join clauses and other complex queries easily.

$first = DB::table('users')
	->join('contacts', 'users.id', '=', 'contacts.user_id')
	->where('users.status', 'active');

$second = DB::table('users')
	->join('orders', 'users.id', '=', 'orders.user_id')
	->select('users.name', 'orders.price')
	->where('orders.status', 'approved');

$unionWithJoin = $first->union($second);
$results = DB::query()->fromSub($unionWithJoin, 'union_join')->get();

This example shows how to combine join clauses with unions, creating more elaborate queries while still keeping the code clear and fluent.

Conclusion

Laravel’s Query Builder makes it straightforward to work with union queries, enabling you to build advanced, efficient database queries with just a few lines of clean code. Practice these techniques to enhance your application’s data-fetching capabilities.