A php trait to search laravel models

Overview

Searchable, a search trait for Laravel

Searchable is a trait for Laravel 4.2+ and Laravel 5.0 that adds a simple search function to Eloquent Models.

Searchable allows you to perform searches in a table giving priorities to each field for the table and it's relations.

This is not optimized for big searches, but sometimes you just need to make it simple (Although it is not slow).

Installation

Simply add the package to your composer.json file and run composer update.

"nicolaslopezj/searchable": "1.*"

Usage

Add the trait to your model and your search rules.

use Nicolaslopezj\Searchable\SearchableTrait;

class User extends \Eloquent
{
    use SearchableTrait;

    /**
     * Searchable rules.
     *
     * @var array
     */
    protected $searchable = [
        /**
         * Columns and their priority in search results.
         * Columns with higher values are more important.
         * Columns with equal values have equal importance.
         *
         * @var array
         */
        'columns' => [
            'users.first_name' => 10,
            'users.last_name' => 10,
            'users.bio' => 2,
            'users.email' => 5,
            'posts.title' => 2,
            'posts.body' => 1,
        ],
        'joins' => [
            'posts' => ['users.id','posts.user_id'],
        ],
    ];

    public function posts()
    {
        return $this->hasMany('Post');
    }

}

Now you can search your model.

// Simple search
$users = User::search($query)->get();

// Search and get relations
// It will not get the relations if you don't do this
$users = User::search($query)
            ->with('posts')
            ->get();

Search Paginated

As easy as laravel default queries

// Search with relations and paginate
$users = User::search($query)
            ->with('posts')
            ->paginate(20);

Mix queries

Search method is compatible with any eloquent method. You can do things like this:

// Search only active users
$users = User::where('status', 'active')
            ->search($query)
            ->paginate(20);

Custom Threshold

The default threshold for accepted relevance is the sum of all attribute relevance divided by 4. To change this value you can pass in a second parameter to search() like so:

// Search with lower relevance threshold
$users = User::where('status', 'active')
            ->search($query, 0)
            ->paginate(20);

The above, will return all users in order of relevance.

Entire Text search

By default, multi-word search terms are split and Searchable searches for each word individually. Relevance plays a role in prioritizing matches that matched on multiple words. If you want to prioritize matches that include the multi-word search (thus, without splitting into words) you can enable full text search by setting the third value to true. Example:

// Prioritize matches containing "John Doe" above matches containing only "John" or "Doe".
$users = User::search("John Doe", null, true)->get();

If you explicitly want to search for full text matches only, you can disable multi-word splitting by setting the fourth parameter to true.

// Do not include matches that only matched "John" OR "Doe".
$users = User::search("John Doe", null, true, true)->get();

How does it work?

Searchable builds a query that search through your model using Laravel's Eloquent. Here is an example query

Eloquent Model:

use Nicolaslopezj\Searchable\SearchableTrait;

class User extends \Eloquent
{
    use SearchableTrait;

    /**
     * Searchable rules.
     *
     * @var array
     */
    protected $searchable = [
        'columns' => [
            'first_name' => 10,
            'last_name' => 10,
            'bio' => 2,
            'email' => 5,
        ],
    ];

}

Search:

$search = User::search('Sed neque labore', null, true)->get();

Result:

select `users`.*, 

-- If third parameter is set as true, it will check if the column starts with the search
-- if then it adds relevance * 30
-- this ensures that relevant results will be at top
(case when first_name LIKE 'Sed neque labore%' then 300 else 0 end) + 

-- For each column you specify makes 3 "ifs" containing 
-- each word of the search input and adds relevace to 
-- the row

-- The first checks if the column is equal to the word,
-- if then it adds relevance * 15
(case when first_name LIKE 'Sed' || first_name LIKE 'neque' || first_name LIKE 'labore' then 150 else 0 end) + 

-- The second checks if the column starts with the word,
-- if then it adds relevance * 5
(case when first_name LIKE 'Sed%' || first_name LIKE 'neque%' || first_name LIKE 'labore%' then 50 else 0 end) + 

-- The third checks if the column contains the word, 
-- if then it adds relevance * 1
(case when first_name LIKE '%Sed%' || first_name LIKE '%neque%' || first_name LIKE '%labore%' then 10 else 0 end) + 

-- Repeats with each column
(case when last_name LIKE 'Sed' || last_name LIKE 'neque' || last_name LIKE 'labore' then 150 else 0 end) + 
(case when last_name LIKE 'Sed%' || last_name LIKE 'neque%' || last_name LIKE 'labore%' then 50 else 0 end) +
(case when last_name LIKE '%Sed%' || last_name LIKE '%neque%' || last_name LIKE '%labore%' then 10 else 0 end) + 

(case when bio LIKE 'Sed' || bio LIKE 'neque' || bio LIKE 'labore' then 30 else 0 end) + 
(case when bio LIKE 'Sed%' || bio LIKE 'neque%' || bio LIKE 'labore%' then 10 else 0 end) + 
(case when bio LIKE '%Sed%' || bio LIKE '%neque%' || bio LIKE '%labore%' then 2 else 0 end) + 

(case when email LIKE 'Sed' || email LIKE 'neque' || email LIKE 'labore' then 75 else 0 end) + 
(case when email LIKE 'Sed%' || email LIKE 'neque%' || email LIKE 'labore%' then 25 else 0 end) + 
(case when email LIKE '%Sed%' || email LIKE '%neque%' || email LIKE '%labore%' then 5 else 0 end) 

as relevance 
from `users` 
group by `id` 

-- Selects only the rows that have more than
-- the sum of all attributes relevances and divided by 4
-- Ej: (20 + 5 + 2) / 4 = 6.75
having relevance > 6.75 

-- Orders the results by relevance
order by `relevance` desc

Contributing

Anyone is welcome to contribute. Fork, make your changes, and then submit a pull request.

Support via Gittip

Issues
  • SQL error when paginate after search on Laravel v5.1.9+

    SQL error when paginate after search on Laravel v5.1.9+

    Hi,

    I got this error when I perform paginate on Laravel version 5.1.9 and above:

    QueryException in Connection.php line 636:
    SQLSTATE[HY000]: General error: 2031 (SQL: select count(*) as aggregate from (select ....
    

    My code:

    user::search('John', null, true)->paginate(10);
    

    This error does not occur on normal get()

    user::search('John', null, true)->get() // no error;
    

    It also does not occur on Laravel v5.1.8 and below

    help wanted 
    opened by laitedliang 62
  • Any news on Laravel 7.0?

    Any news on Laravel 7.0?

    Release will be on 3rd march

    opened by caiokawasaki 11
  • Ambiguous columns

    Ambiguous columns

    Hello!

    I have a database with next structure:

    table orders:
        id;
        order_number;
        domain_id;
        user_id;
        other_fields;
    
    table users;
        id;
        login;
        domain_id;
        other_fields;
    

    In my Order Model I put next code:

    protected $searchable = [
            'columns' => [
                'orders.order_number' => 100,
                'orders.lastname' => 100,
                'orders.email' => 100,
                'users.login' => 70,
                'orders.postcode' => 50,
                'orders.phone' => 50,
                'orders.city' => 50,
                'orders.street' => 50,
            ],
            'joins' => [
                'users' => ['orders.user_id','users.id'],
            ]
        ];
    

    As the result, I caught next error:

    SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'domain_id' in where clause is ambiguous

    The reason of this error is duplicate column 'domain_id' in the both tables.

    Is there any way to choose columns of 'users' table which will be joining at the searching?

    opened by ashandi 9
  • Incompatible with Postgres?

    Incompatible with Postgres?

    SQLSTATE[42883]: Undefined function: 7 ERROR: function if(boolean, integer, integer) does not exist
    LINE 1: select "articles".*, if(title = 'test', 150, 0) + if(title L...
    ^
    HINT: No function matches the given name and argument types. You might need to add explicit type casts.
    

    Gets thrown whenever I try to search. I'm using Postgres which doesn't ship with an IF() function. You could possibly use CASE() instead (which works with both MySQL and Postgres) but I haven't looked into it.

    opened by a7y 9
  • Update breaks query

    Update breaks query

    Updating from 1.9.6 to 1.10.1 breaks my set up, I receive the following:

    SQLSTATE[08P01]: <>: 7 ERROR: bind message supplies 9 parameters, but prepared statement "pdo_stmt_00000003" requires 18 (SQL: select * from

    I can downgrade to 1.9.6 and everything works fine.

    opened by dasbrow 8
  • Strange behavior when searching with joins

    Strange behavior when searching with joins

    Hi,

    I've the following search configured at my HotelRoom model.

        /**
         * Searchable rules.
         *
         * @var array
         */
        protected $searchable = [
            'columns' => [
                'name' => 10,
                'hotels.invented_name' => 10,
                'hotels.company_name' => 10,
                'location_cities.full_name' => 10,
            ],
            'joins' => [
                'hotels' => ['hotel_rooms.hotel_id', 'hotels.id'],
                'location_cities' => ['hotels.location_city_id', 'location_cities.ufi'],
            ]
        ];
    

    And I have an HotelRoom with the name Cristal test

    If I do a search for cristal, it results correctly. But if I do a search for test, it don't results anything.

    Then I tried to remove my joins:

        /**
         * Searchable rules.
         *
         * @var array
         */
        protected $searchable = [
            'columns' => [
                'name' => 10,
            ],
            'joins' => [
            ]
        ];
    

    And noticed that the search works perfectly, resulting both for cristal or test

    What may be happening here?

    opened by iget-master 8
  • Improved EntireWord Search

    Improved EntireWord Search

    This pull request implements a better EntireWord Search that gives more relevance to results that starts with the entire search...

    Some cases like this search: Lucas do rio

    May not show the result Lucas do Rio Verde as first result in this table:

    Lucas do Rio Verde
    Bahia dos santos do Brasil (Rio grande do sul)
    Do do do do (Rio grande do sul)
    

    In my PR:

    • I give a relevance multiplier of 30 to a search LIKE 'lucas do%'. This fix the problem above.
    • I added a trim() to search, to avoid blank spaces to causing performance issues.
    • Moved $like_comparator variable from filterQueryWithRelevance() to getSearchQuery() method
    opened by iget-master 7
  • Search by tags

    Search by tags

    Hi.

    I look for this great package. Is there any community forum for this project?

    I would like to ask if there is to possibility to search with tags. Structure:

    posts:

    • id

    post_tag:

    • post_id
    • tag_id

    tags:

    • id
    • title

    Thanks

    opened by homoky 5
  • laravel 6

    laravel 6

    hi, just curious to know if you will have an update for laravel 6? thanks

    opened by scripta55 5
  • Laravel 6 support

    Laravel 6 support

    Laravel 6 release is only one week away! :rocket: I can not update to current Laravel 6 dev release, because it requires illuminate/database in version 6.

    Would be great to have this package available in Laravel 6 :)

    opened by v1r0x 5
  • 'syntax error, unexpected '?'' with Laravel/PHP 5

    'syntax error, unexpected '?'' with Laravel/PHP 5

    I know version 5 should not be supported, but I think this is a small change that can help legacy apps so as to not break when updated

    I get the following error when installing on a fresh Laravel 5 which uses PHP 5 (any subversion)

    'syntax error, unexpected '?'' vendor/nicolaslopezj/searchable/src/SearchableTrait.php:380

    This is because line 380 of said file is

    if ($this->relevanceField ?? false) {

    Which uses the Null Coalescing Operator ?? which was introduced in PHP 7. This change was introduced in 1.11

    The "correct" way to fix this would be to use a regular ternary operator, or just specify in all composer.json files from version 1.11 to require PHP 7 and above, instead of the current 5.4 which is unsoported. This way a PHP 5 installation will refuse to update from 1.10 and avoid breaking.

    opened by theinfra 0
  • Support for Laravel version 8

    Support for Laravel version 8

    opened by ishan-biztech 2
  • Field not found

    Field not found

    In my table not exist id, use other name, it's possible specific diferent id table with other name field?

    opened by jesusferm 0
  • Search polymorphic relation (tags)

    Search polymorphic relation (tags)

    Hello ! Is there any way to search polymorphic relationship ?

    For instance, I am using spatie/laravel-tags package and this adds a polymorphic relationship between my models and the tags table.

    So I have those tables and my models : dispositifs, infrastructures, etc. which are tagged and which can be searched.

    Taggables

    • tag_id
    • taggable_type
    • taggable_id

    Tags

    • id
    • name (json datatype for translations)
    • slug
    • type [...]

    How could I search for tags please?

    So far, I have this, but it does not seem to work. When I search for a "tag", the results returned are all the models and no point ordering is done ...

    protected $searchable = [
            'columns' => [
                'dispositifs.di_sigle' => 10,
                'dispositifs.di_libelle' => 10,
                'dispositifs.di_objectif' => 6,
                'dispositifs.di_description' => 7,
                'dispositifs.di_condition' => 4,
                'dispositifs.di_procedure' => 4,
                'dispositifs.di_info' => 5,
    	    'acteurs.acteur_libelle' => 8,
    	    'tags.name' => 8,
            ],
            'joins' => [
    			'operateur_dispo' => ['dispositifs.di_id', 'operateur_dispo.operateur_dispositif_id'],
    			'acteurs' => ['operateur_dispo.operateur_acteur_id', 'acteurs.acteur_id'],
    			'taggables' => ['taggables.tag_id','taggables.taggable_id'],
    			'tags' => [ 'tags.id','taggables.tag_id'],
            ],
    ];
    

    Thank you in advance and thanks to @nicolaslopezj for this awesome package ! 😁 👍

    opened by Shi974 2
  • A non-numeric value encountered

    A non-numeric value encountered

    Using Model::search($query, null, true, true)->get();

    I get this response:

    { "errors": { "message": "A non-numeric value encountered:/var/www/html/vendor/nicolaslopezj/searchable/src/SearchableTrait.php:63", "error_code": "UNKNOWN" } }

    Is this because the Primary key is not an integer, this is an existing application which uses a Uuid

    opened by lperry65 0
  • no results when calling search() after a whereHas

    no results when calling search() after a whereHas

    I have a scope which is created using whereHas :

    public function scopeVendorOf($query, Enterprise $customer)
        {
            return $query->whereHas('customers', function ($query) use ($customer) {
                $query->where('id', $customer->id);
            });
        }
    

    now if I call this scope before the search scope it works, but calling the search scope before the vendorOf() one will fail.

    Enterprise::search('lucas',null,true,true)->vendorOf($customer)->count() => 0 Enterprise::vendorOf($customer)->search('lucas',null,true,true)->count() => 1

    Any idea why and how to solve this issue ?

    opened by cyberhicham 0
  • Change Columns and Joins Dynamically [SOLVED]

    Change Columns and Joins Dynamically [SOLVED]

    If you want to change the columns to search on the same model, you can do it dynamically inserting this code to your model or to the source file:

    vendor/nicolaslopezj/searchable/src/SearchableTrait.php

        public function scopeSetSearchScope($query,$search = []){
            $this->searchable = $search;
            return $query;
        }
    

    Then you can:

            $toSearch = [
                'columns' => [
                    'contacts.name' => 10,
                    'contacts.last_name' => 10,
                    'contacts.alias' => 10,
                    'fields.value' => 5,
                ],
                'joins' => [
                    'fields' => ['contacts.id','fields.contact_id'],
                ],
            ];
    
            $contacts =  Contact::with(['fields'])
                        ->companyScope()
                        ->setSearchScope($toSearch)
                        ->search(request('search'),null,true)
                        ->groupBy('contacts.id')
                        ->orderBy('relevance','desc')
                        ->paginate(request('show',100));
    
    opened by trijulio 0
  • determine relevance based on relationship: e.g. most recent date or count

    determine relevance based on relationship: e.g. most recent date or count

    Is there a way to the following influence the relevance? E.g. the “Peter” that I visited most often (count of related Visits), or most recently, should get a higher relevance than the “Peter” that I hardly ever visit. So take into account count of related Models or aspect of them (date, or other methods to determine relevancy, e.g. income amount).

    opened by wivaku 0
  • SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias:

    SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias:

    I got this error.

    SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 'contents' (SQL: select count(*) as aggregate from (selectposts.*, max((case when LOWER(contents.title) LIKE sed then 150 else 0 end) + (case when LOWER(contents.title) LIKE sed% then 50 else 0 end) + (case when LOWER(contents.title) LIKE %sed% then 10 else 0 end) + (case when LOWER(contents.excerpt) LIKE sed then 105 else 0 end) + (case when LOWER(contents.excerpt) LIKE sed% then 35 else 0 end) + (case when LOWER(contents.excerpt) LIKE %sed% then 7 else 0 end) + (case when LOWER(contents.body) LIKE sed then 135 else 0 end) + (case when LOWER(contents.body) LIKE sed% then 45 else 0 end) + (case when LOWER(contents.body) LIKE %sed% then 9 else 0 end) + (case when LOWER(contents.seo_title) LIKE sed then 120 else 0 end) + (case when LOWER(contents.seo_title) LIKE sed% then 40 else 0 end) + (case when LOWER(contents.seo_title) LIKE %sed% then 8 else 0 end) + (case when LOWER(contents.meta_description) LIKE sed then 90 else 0 end) + (case when LOWER(contents.meta_description) LIKE sed% then 30 else 0 end) + (case when LOWER(contents.meta_description) LIKE %sed% then 6 else 0 end)) as relevance frompostsinner joincontentsonposts.id=contents.post_idinner joinusersonposts.user_id=users.idleft joincontentsonposts.id=contents.post_idwhereposts.type= post andposts.status!= auto-draft group byposts.id,contents.title,

    my search rule is

    ` protected $searchable = [ 'columns' => [ 'contents.title' => 10, 'contents.excerpt' => 7, 'contents.body' => 9, 'contents.seo_title' => 8, 'contents.meta_description' => 6, ], 'joins' => [ 'contents' => ['posts.id', 'contents.post_id'], ],

    ];`
    
    opened by qmeyti 4
Releases(1.13.0)
Owner
Nicolás López Jullian
A JavaScript developer
Nicolás López Jullian
Maps Laravel Eloquent models to Elasticsearch types

Elasticquent Elasticsearch for Eloquent Laravel Models Elasticquent makes working with Elasticsearch and Eloquent models easier by mapping them to Ela

Elasticquent 1.2k Jun 14, 2021
A php trait to search laravel models

Searchable, a search trait for Laravel Searchable is a trait for Laravel 4.2+ and Laravel 5.0 that adds a simple search function to Eloquent Models. S

Nicolás López Jullian 1.9k Jun 15, 2021
Unmaintained: Laravel Searchy makes user driven searching easy with fuzzy search, basic string matching and more to come!

!! UNMAINTAINED !! This package is no longer maintained Please see Issue #117 Here are some links to alternatives that you may be able to use (I do no

Tom Lingham 527 May 25, 2021
[Deprecated] We now recommend using Laravel Scout, see =>

[DEPRECATED] Algolia Search API Client for Laravel Algolia Search is a hosted full-text, numerical, and faceted search engine capable of delivering re

Algolia 243 May 7, 2021
Plastic is an Elasticsearch ODM and mapper for Laravel. It renders the developer experience more enjoyable while using Elasticsearch, by providing a fluent syntax for mapping, querying, and storing eloquent models.

Plastic is an Elasticsearch ODM and mapper for Laravel. It renders the developer experience more enjoyable while using Elasticsearch, by providing a f

Sleiman Sleiman 494 May 4, 2021
A fully featured full text search engine written in PHP

TNTSearch TNTSearch is a full-text search (FTS) engine written entirely in PHP. A simple configuration allows you to add an amazing search experience

TNT Studio 2.4k Jun 19, 2021
Sphinx Search library provides SphinxQL indexing and searching features

Sphinx Search Sphinx Search library provides SphinxQL indexing and searching features. Introduction Installation Configuration (simple) Usage Search I

Ripa Club 62 May 4, 2021
Driver for Laravel Scout search package based on https://github.com/teamtnt/tntsearch

TNTSearch Driver for Laravel Scout - Laravel 5.3 - 8.0 This package makes it easy to add full text search support to your models with Laravel 5.3 to 8

TNT Studio 941 Jun 8, 2021
MeiliSearch PHP is the MeiliSearch API client for PHP developers.

MeiliSearch PHP is the MeiliSearch API client for PHP developers. ⚡ The MeiliSearch API client written for PHP ??

MeiliSearch 148 Jun 13, 2021
Kirby docs search workflow for Alfred

Kirby Docs search workflow for Alfred 4 An ultra-fast Kirby Docs search workflow for Alfred 4 Installation Download the latest version Install the wor

Adam Kiss 19 Jun 14, 2021
Store and retrieve objects from Algolia or Elasticsearch

Store and retrieve objects from a search index This is an opinionated Laravel 5.1 package to store and retrieve objects from a search index. Currently

Spatie 436 Jun 1, 2021
Laravel Scout provides a driver based solution to searching your Eloquent models.

Introduction Laravel Scout provides a simple, driver-based solution for adding full-text search to your Eloquent models. Once Scout is installed and c

The Laravel Framework 1.1k Jun 13, 2021
Official PHP low-level client for Elasticsearch.

elasticsearch-php Official low-level client for Elasticsearch. Its goal is to provide common ground for all Elasticsearch-related code in PHP; because

elastic 4.6k Jun 16, 2021
SphinxQL Query Builder generates SphinxQL, a SQL dialect, which is used to query the Sphinx search engine. (Composer Package)

Query Builder for SphinxQL About This is a SphinxQL Query Builder used to work with SphinxQL, a SQL dialect used with the Sphinx search engine and it'

FoolCode 305 May 13, 2021