How to search multiple models easily with Laravel Eloquent without using packages

Photo by Daniel Leone on Unsplash

Recently at work, I have to implement a quick search that searches across multiple models and return a unified list of results. I thought I needed a third-party package to achieve that. Turns out that it’s really easy to do it ourselves using UNION.

So here’s a simple guide on how to do it. :)

The Models

Imagine I have 2 different models that I need to search across, namely School, Company.

School's structure would look like this:

+----+------+------------+------------+------------+
| id | name | chancellor | created_at | updated_at |
+----+------+------------+------------+------------+
| | | | | |
+----+------+------------+------------+------------+

Company's structure would look like this:

+----+------+-----+------------+------------+
| id | name | ceo | created_at | updated_at |
+----+------+-----+------------+------------+
| | | | | |
+----+------+-----+------------+------------+

The Queries

Now we would like to search for any records that matches a name calledharvard.

To do it separately, we would have the queries as such:

School::query()->where('name', 'LIKE', '%harvard%')->get();Company::query()->where('name', 'LIKE', '%harvard%')->get();

In order to merge the queries into 1 result set, we need to do a few things.

  1. We will be using UNION to merge our queries together into 1.
  2. In order to use the UNION operation, we need to make sure that the fields returned from all queries are the same otherwise there will be errors.
  3. I’m using MySQL here. Hopefully it works the same for other databases as well.

So now, our new queries after applying #2 to make all the returned fields the same would look as followed:

School::query()
->select('id', 'name', \DB::raw('chancellor AS leader'))
->where('name', 'LIKE', '%harvard%')
->get();
Company::query()
->select('id', 'name', \DB::raw('ceo AS leader'))
->where('name', 'LIKE', '%harvard%')
->get();

In order to make the columns match between both tables, we have to cast the chancellor and ceo fields into leader.

Our returned results for both queries would look like this:

+----+------+--------+
| id | name | leader |
+----+------+--------+
| | | |
+----+------+--------+

The UNION

Finally, let’s apply #1 or the UNION to our queries:

$q1 = School::query()
->select('id', 'name',
\DB::raw('chancellor AS leader'),
\DB::raw('"school" as type')
)
->where('name', 'LIKE', '%harvard%');
$q2 = Company::query()
->select('id', 'name',
\DB::raw('ceo AS leader'),
\DB::raw('"company" as type')
)
->where('name', 'LIKE', '%harvard%');
$q2->union($q1)->get();

And we would get a list of records that look this:

+----+------+--------+------+
| id | name | leader | type |
+----+------+--------+------+
| | | | |
+----+------+--------+------+

A few things to note here:

  1. We took out the get() from the individual queries and only apply it at the final UNION query.
  2. We added a type into our queries so that we would know where the records come from.
  3. You could also do paginate() with this UNION!
  4. There is no limit to how many tables / models that you could merge together.

That’s all! And you now have a search that works across models!

I write about Laravel, PHP, and web development related articles.

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Prefect is Open-Source! 🎉

How to Develop a Landing Page in a Cost-Effective Way

Introduction to HIVE for Beginners | Hive Architecture

Install Odoo using Docker, Nginx on Ubuntu 20.04 — AWS

Fieldbook is Shutting Down

What I don’t like about OutSystems

Yet Another Very Basic Scrapy Tutorial : Scraping Latest Earthquakes in Turkey

Git, GitHub, and Version Control

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Jian Jye

Jian Jye

I write about Laravel, PHP, and web development related articles.

More from Medium

Build REST API with Laravel

Build a REST API with Laravel API resources

How to install Laravel PHP Framework on macOS?

Laravel 8 Authentication using Jetstream Example

Laravel 8 Authentication using Jetstream Example