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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
// User model // many-to-many relation public function friends() { return $this->belongsToMany(static::class, 'friends', 'user_id', 'friend_id'); } // 1-1 relation public function profile() { return $this->hasOne(Profile::class); } |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
$name = strtolower(Input::get('query')); $matchingUsers = User::where(function ($q) use ($name) { $q->where('email', 'like', '%'.str_replace(' ', '', $name).'%') ->orWhere('username', 'like', "%{$name}%") ->orWhereHas('profile', function ($q) { $q->where(function ($q) { $q->where('first_name', 'like', "%{$name}%") ->orWhere('last_name', 'like', "%{$name}%"); }); }); })->get(); |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
$name = 'john doe'; $matchingUsers = User::search($name, [ 'profile.last_name' => 20, 'email' => 10, 'username' => 10, 'profile.first_name' => 5, 'friends.username' => 2, 'friends.email' => 2, 'friends.profile.first_name' => 1, 'friends.profile.last_name' => 1, ])->get() // or if these fields are not subject ot change, define them on the model: protected $searchableColumns = [ 'profile.last_name' => 20, 'email' => 10, 'username' => 10, 'profile.first_name' => 5, 'friends.username' => 2, 'friends.email' => 2, 'friends.profile.first_name' => 1, 'friends.profile.last_name' => 1, ]; // then simply call User::search($name)->get() |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
// fulltext search by default + specified weighted columns: User::search('john doe', ['email' => 10, 'profile.name' => 20, 'friends.profile.name' => 5])->take(10)->get() // fulltext phrase search + default columns for the model: User::search('"going to LA"')->get() // exact search: User::search('"going to LA"', $fulltext = false)->get() // phrase with wildcards: User::search(['going to *', '*LA*', '*NY*'], $fulltext = false)->get() |
Give it a try and leave your feedback in the comments.
