⇨ Click here for the table of contents for Laravel articles

I have summarized how to use Union in Laravel to retrieve different tables together. Different tables, such as the posts table and items table, can be displayed on the same list, for example, when you want to display posts and items together.

Consider Using Union

For example, let's say there is a service where users can post articles and products, each with their own detail pages.

In this case, if you want to combine these three tables and display them as a list:

Users
Articles
Products

You can use Union.

There are probably other use cases as well, so please let me know if you have any!

How to Use Union

Using Union() is very simple.

In your controller:

  use App\Models\Item;
  use App\Models\Post;

~~~
public function index()
{
  $items = Item::select('title','created_at');
  $results = Post::select('title','created_at')
      ->union($items)
      ->get();
  dd($results);
}

You can write it like this.

Simply write the Eloquent you want to retrieve inside the parentheses of union.

Searching with where

Retrieve items and posts with titles

For example:

$results = Post::select('title','created_at')
    ->union($items)
    ->whereNotNull('title')
    ->get();

Retrieve all posts and items with titles

$items = Item::select('title','created_at')
  ->whereNotNull('title');
$results = Post::select('title','created_at')
    ->union($items)
    ->get();
dd($results);

You can achieve this by using where inside union.

Most union articles in the world only catch query builders, but you can also use Eloquent.

You can also use Eloquent.

Because Eloquent relationships are excellent, right?

Selection follows the order of columns selected from the original table

Union is not always versatile.

It follows the order of columns selected from the original table.

Therefore, when using union, make sure to select the columns in the same order for the table you added with union.

The number of columns selected must match

Due to the aforementioned characteristics, the number of columns in the union must match the original table.

If you select a different number of columns:

SQLSTATE[21000]: Cardinality violation: 1222 The used SELECT statements have a different number of columns (SQL: (select `title`, `created_at` from `posts` where `title` is not null) union (select `title` from `items`))

This error will occur.

Summary

That's all.

I have written an article on how to use Union in Laravel.

I hope it can be helpful to someone.

For feedback or complaints, please contact me via Twitter DM.

That's it!

Popular Articles

Deploying a PHP 7.4 + Laravel 6 project to AWS EC2

Implementing Breadcrumbs in Laravel using laravel-breadcrumbs