Luke Curtis

Luke Curtis

Software Developer and Project Manager

Contact Me

Scalable Search in Laravel with JSON

25/02/2019 (1 month ago) | Luke Curtis

Recently, I had a bunch of data I needed to index and search. Usually I would use something like Laravel Scout for this. However this time round the budget wasn't really there to justify Algolia and the database search ends up being too resource intensive.

So it got me thinking of a way to create not only a scalable search that didn't need all the bells and whistles of Scout, but also to index this data easily and without needing to have an awkward schema for a bunch of columns I didn't need to index unless I was searching.

This is where I actually combined 3 quite frankly amazing Spatie packages/tools into one and use Laravel's helpful eloquent queries whereby an endpoint will be able to do a full text search on a table with a JSON field.

Set up

Let's go over the three packages and tools I'll be using

I'll assume you're able to install these packages and macro with relative ease as per the links above. Lets dive right into an example.

Model

Let's say we have a `Location` model, and we have some extra attributes `address` which is a JSON representation of the Google Geocoding API spec

Our parser of the JSON address from Spatie schemaless attributes to a string

Our parser of the JSON address from Spatie schemaless attributes to a string

Great. so the first step is completed so we can go ahead and do something like Location::first()->address which will traverse our extra attributes array and get the formatted address for us.
The next step is to allow us to actually filter results from an API or endpoint to get this info, so for example, if I had an address that included {address: {route:'Test Road'}}`, I would want to do something like the following:http://test.test/api/locations?filter[where_like]='Test Ro'
Ideally this should return the previously mentioned Location right? So what would that endpoint look like?


Filter Endpoint

So by using Spatie Query Builder we can actually easily prototype something like that almost immediately with the following:

Our endpoint

Our endpoint

Notice how we're using a customer filter here. What this means is, we are over-riding that filter with our own implementation, this is where the whereLike builder macro comes into play. I'm going to create a filters class in "app/Filters"


Our filter

Our filter

Notice how I'm using some Laravel magic to go into the JSON array and let it search the entire object for anything I need to find? And this is all thanks to the Builder macro that I assume you put in your AppServiceProvider.php


What we have now is a pretty powerful endpoint that can search addresses that are stored in JSON. How about we take this one step further, what if I need to sort by that JSON?

Sort Endpoint

So lets say we had 3 locations that actually had a route of Test Road, what if we needed to sort them by house number? Well Laravel's sort and Spatie Query builder can do that super easily too! Lets upgrade our endpoint to have eligible sorts:


Our sort added in

Our sort added in

So the only addition we've made here, is we're allowing the locations to be sorted, and we're overriding one sort which is address. Spatie Query Builder makes it super easy to over-ride these sorts of things. Lets take a look at that sort.


The address sort

The address sort

What's great here is once again, we're traversing even further down our JSON address object, and sorting by the street number. So now you can not only filter on a JSON Column but also sort by it too. The endpoint in this instance would look something like:


http://test.test/api/locations?filter[where_like]=Test%20Road&sort=address


And it's literally that simple, we've now made it so we can sort a JSON column and have a full text search on that column too. It's not too resource intensive because we can specify the searchable columns and we have the added benefit of having a consistent endpoint with all our additional includes, filters and appends.

As always here's the gist. At some point in the future I will make a blog post about consuming this API in Vue and creating our very own mini Datatables with only 100 lines of code.