Builder searchable and more

Eloquence\Builder

• Introduction
• Searchable
• Joining relations

Introduction

Eloquence Builder ships with a few addons to the base eloquent query builder. The most significant is Searchable feature.

Searchable

tldr; You can easily search through any columns on the model's table or any of its relations (no-matter-how-deeply-nested) and return the result sorted by search relevance (with possible weight for each column). You need to register ServiceProvider in order to use search feature.

• automaticaly handles relations, including pivots and 'through' tables
• supports pagination
• optimized for speed
• allows combination of words & phrases to search for with "
• supports (almost) all query builder methods, including orderBy, select, groupBy etc

Limitations and requirements

• searchable uses non-strict groupBy clause which requires strict mode to be disabled in MySQL5.7+ ('strict' => false in your config/database.php will do).
• withCount() will not work properly in combination with searchable and paginate(). If you are experiencing this problem and know a solution, please submit a pull request.

Let's see it in action:

<?php namespace App;

use Sofa\Eloquence\Eloquence;
use Illuminate\Database\Eloquent\Model;

class User extends Model
{
   use Eloquence;

   // no need for this, but you can define default searchable columns:
   protected $searchableColumns = ['name', 'email'];

   // let's add some relations
   public function profile()
   {
      return $this->belongsTo(Profile::class); // also Profile belongsTo Address
   }

   public function posts()
   {
      return $this->hasMany(Post::class); // also Post belongsToMany Categories
   }
}

Then we can do this:

// basic usage, fulltext search for '*jarek*' or '*sofa*' thru defined columns
>>> User::search('jarek sofa')->get();

// exact search for 'jarek tkaczyk' thru defined columns
>>> User::search('"jarek tkaczyk"', false)->first();

// wildcard search for 'jarek*' or '*czyk' thru provided columns with weighted score
>>> User::search(['jarek*', '*czyk'], ['name' => 10, 'email' => 5], false)->get();

// fulltext search on multiple tables (joined automatically using dot nested relations)
>>> User::search('foo bar', ['name', 'posts.title', 'posts.categories.name', 'profile.address.city'])->get();

Searchable feature is based on great https://github.com/nicolaslopezj/searchable trait, only it's more than 4 times faster and gives you huge flexibility - pretty much everything is done for you under the hood.

Joining relations

Sometimes you may need to join a relation instead of simply eager loading it. For example there is no other way to order by a field in related table but to join that table.

Eloquence provides easy way to do this, which is aligned to other eloquent calls thanks to intuitive dot-notation:

// instead of hard coding table and keys:
$activeCategories = Category::leftJoin('category_post', category_post.category_id', '=', 'categories.id')
    ->leftJoin('posts', 'category_post.post_id', '=', 'posts.id')
    ->latest('posts.updated_at')
    ->get(['categories.*']);


// simply do this:
$activeCategories = Category::leftJoinRelations('posts')
    ->latest('posts.updated_at')
    ->get(['categories.*']);


// obviously it's getting even more handy if you need nested relations
Category::joinRelations('posts.comments');

// instead of:
Category::join('category_post', category_post.category_id', '=', 'categories.id')
    ->join('posts', 'category_post.post_id', '=', 'posts.id')
    ->join('comments', 'comments.post_id', '=', 'posts.id');

It's a variadic function, so you can pass an array of relations to join:

Category::joinRelations(['posts', 'users'], 'left'); // 2nd param is type of join
Category::leftJoinRelations(['posts', 'users']);
Category::rightJoinRelations(['posts', 'users']);

Or multiple params

Category::joinRelations('posts', 'users'); // assumes inner type
Category::leftJoinRelations('posts', 'users');
Category::rightJoinRelations('posts', 'users');

There is also prefixColumnsForJoin method to help you. By default selects all fields from the main table:

Category::joinRelations('posts.comments')
    ->prefixColumnsForJoin();

// produces
select categories.* from categories inner join ...

If you specified columns, then it prefixes these on main table:

// categories: id, name, timestamps
// posts: id, title, timestamps
// comments: id, body, timestamps
Category::joinRelations('posts.comments')
    ->select('id', 'name', 'title', 'body')
    ->prefixColumnsForJoin();

// produces
select categories.id, categories.name, title, body ...