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!