search

In this first post of a series about https://github.com/jarektkaczyk/eloquence – the package that allows working with eloquent models in even simpler manner – I’m going to introduce you to the searchable feature of the builder class.

Imagine app with simple friendship system:

For the presentation it is enough – simple m-m relation between Users with friends being pivot table. In the users table we store only email and username and in profiles table real person data like first_name, last_name etc.

Obviously password, timestamps and other fields as well, but we don’t care about them right now.

 

Now, in order to find a person, let’s can use combination of a few fields: users.username, users.email, profiles.first_name, profiles.last_name, friends.first_name, friends.last_name.

This is how you do it with basic eloquent methods:

It’s pretty simple but it lacks crucial search factor – relevance score. Also, it makes no sense to search friends because of that.

Such query returning score requires some computations, but obviously we won’t build second google engine here 😉

Anyway, ideally we would like something like this:

The columns here are relation mappings – these are normal relations defined on the models, no need for anything else. Number next to the columns are weights for each one of them, so we can define their importance in the scoring.

This is how Eloquence\Builder works – automatically joins related tables, adds some clauses for performance and orders results by search relevance.

Idea for this feature and scoring algo is based on this package https://github.com/nicolaslopezj/searchable , but it provides much more flexibility and is easier to use, and most importantly it’s 4 times faster (or even better depending on the additional where clauses applied on the query).

 

Below a few examples of how you can control the way searching works. It also affects the speed obviously – fulltext search won’t take advantage of the indexes, and it can get pretty slow on big tables and/or multiple joins.

If that’s the case you may want to disable fulltext searching and use right-hand wildcard (word*) instead, if that suits your needs.

 

Give it a try and leave your feedback in the comments.

It's only fair to share...Tweet about this on TwitterShare on RedditPin on PinterestShare on FacebookShare on Google+

Related Post

Laravel 5 Eloquent Global Scope how-to If you are looking for an example of Eloquent Global Scope feature, then you're in the right place! Check the ready-to-use demo at ... h...
Querying relations in Laravel: get Models where la... Another part of Querying relations in Laravel will cover such problem: I want to get SomeModels that have latest RelatedModel (hasMany) matchin...
Laravel – querying any level far relations w... Eloquent provides one Relation type for far related tables - hasManyThrough. However it works only with with cascade of hasOne/hasMany relations a...
Laravel – how to define and use Eloquent Glo... Laravel 4.2 introduced a new way of handling soft deletes by making use of traits and query builder macros wrapped in as a feature, that many people a...