I am using this plugin, and I think that it is missing a little of relevance in this way :

I have different applications in my database, let's say "Test App" and "Great Photo App".
When I am looking for the query "Photo App", the two applications will have the same relevance because "%App%" was found for the first one, and "%Photo%" was found for the second one.

I would find logical that "Great Photo App" was more relevant because :

• It contains the two keywords
• It contains exactly the query in the right order

I don't know what would be the easiest to do, and with the lest cost between adding a query like the one in the beggining :

(case when first_name LIKE '%Sed neque labore%' then 300 else 0 end) + 

or separating the searches between all the keywords, and adding relevance for each of them.

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

I'm using this trait to feed a typeahead search of cities.

I found some situations that it wrongs the suggestion, like this one:

If my database have this items:

Lucas do Rio Verde
Bahia do Santos do Brasil
Do Do Do

And I search for Lucas do, the expected is that Lucas do Rio Verde be the most relevant one, right?
But the result is:

Do Do Do
Bahia do Santos do Brasil
Lucas do Rio Verde

I've found the problem and i'm submiting an pull request right now for this issue, and I will love if you merge it!

Thanks in advance,

How should I use the package for multiple search terms?

For instance, if I'm searching for textures and write "metal golden" where "metal" something belonging to the Category table, while "golden" belongs to the Tag table, I'm only getting results which conform to metal, but the search ends there.

This is quite urgent, so any help is appreciated.

The "mergeQueries"-function calls $this->getTable() in a DB::raw() and thereby ignoring any check whether a table prefix is in use. This causes error messages on queries on "products"-table with prefix "qs_" like:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'qs_products.account_id' in 'where clause' (SQL: select count() as aggregate from (select qs_products., (case when LOWER(brand) LIKE xyz then 150 else 0 end) + (case when LOWER(brand) LIKE xyz% then 50 else 0 end) + (case when LOWER(brand) LIKE %xyz% then 10 else 0 end) + (case when LOWER(description) LIKE xyz then 300 else 0 end) + (case when LOWER(description) LIKE xyz% then 100 else 0 end) + (case when LOWER(description) LIKE %xyz% then 20 else 0 end) as relevance from qs_products where qs_products.account_id = 30 and shop_id in (33) group by qs_products.id having relevance > 7.50 order by relevance desc) as products where qs_products.account_id = 30)

Where:
"relevancedesc) as products whereqs_products.account_id` = 30 "

Should be:
"relevancedesc) as qs_products whereqs_products.account_id` = 30 "

Introducing $tableNameRaw and changing the "mergeQueries"-function to the code below solves my issues (in Laravel 4.2):
protected function mergeQueries(Builder $clone, Builder $original) {

$tableNameRaw = DB::getQueryGrammar()->wrapTable($this->getTable());
    $original->from(DB::raw("({$clone->toSql()}) as {$tableNameRaw}"));
    $original->mergeBindings($clone->getQuery());
}

Used the code suggestion from Christian Mayer:
http://blog.fox21.at/2015/05/06/laravel-5-usage-of-table-names-in-raw-mysql-queries.html

I am using Laravel 4.2.17. Then installing Searchable using composer, I did something like this but getting the error Trait 'Nicolaslopezj\Searchable\SearchableTrait' not found.

<?php namespace Bundles\Custom\Category\Models;

use Carbon;
use Illuminate\Database\Eloquent\SoftDeletingTrait;
use Illuminate\Database\Eloquent\Model;
use Bundles\Xdit\Core\Models\ModelTrait;
use Nicolaslopezj\Searchable\SearchableTrait as SearchableTrait;

/**
 * Class Event
 * @package Bundles\Xfront\Category\Models
 */
class Category extends Model
{

    use ModelTrait;
    use SoftDeletingTrait;
    use SearchableTrait;

    protected $searchable = [
        'columns' => [
            'name' => 10,
        ],
    ]

Hello I saw on your documentation that you split a string, for example if we have this name Rolland Levi you search the Rolland and then the Levi. Is there anyway to avoid this and first check the whole word Rolland Levi and if the results are null then split it them?

Thank you

Hi there,

Great Library! I'm facing an issue issue though, I keep receiving the error "A facade root has not been set" as follow:

object(RuntimeException)#130 (7) {
  ["message":protected]=>
  string(31) "A facade root has not been set."
  ["string":"Exception":private]=>
  string(0) ""
  ["code":protected]=>
  int(0)
  ["file":protected]=>
  string(63) "/var/www/xxx/vendor/illuminate/support/Facades/Facade.php"
  ["line":protected]=>
  int(206)
  ["trace":"Exception":private]=>

Thanks in advance

Hi,

I am using searchable with own publishedScope (Global Scope) on laravel 5.2.5

PublishedScope

$builder->where('created_at', '<', Carbon::now());

When used like:

Watch::search($searchQuery)->get();

I get error.

When used like:

Watch::withoutGlobalScopes([PublishedScope::class])->search($searchQuery)->get();

No error it is works. But i must use that scope.

Model:

protected $searchable = [
        'columns' => [
            'watch_translations.title' => 10,
            'watch_translations.excerpt' => 5,
            'watch_translations.content' => 3,
        ],
        'joins' => [
            'watch_translations' => ['watches.id','watch_translations.watch_id'],
        ],
    ];

Error:

SQLSTATE[HY093]: Invalid parameter number (SQL: select * from (select `watches`.*, avg((case when LOWER(`watch_translations`.`title`) LIKE searchkeyword then 150 else 0 end) + (case when LOWER(`watch_translations`.`title`) LIKE searchkeyword% then 50 else 0 end) + (case when LOWER(`watch_translations`.`title`) LIKE %searchkeyword% then 10 else 0 end) + (case when LOWER(`watch_translations`.`excerpt`) LIKE searchkeyword then 75 else 0 end) + (case when LOWER(`watch_translations`.`excerpt`) LIKE searchkeyword% then 25 else 0 end) + (case when LOWER(`watch_translations`.`excerpt`) LIKE %searchkeyword% then 5 else 0 end) + (case when LOWER(`watch_translations`.`content`) LIKE searchkeyword then 45 else 0 end) + (case when LOWER(`watch_translations`.`content`) LIKE searchkeyword% then 15 else 0 end) + (case when LOWER(`watch_translations`.`content`) LIKE %searchkeyword% then 3 else 0 end)) as relevance from `watches` left join `watch_translations` on `watches`.`id` = `watch_translations`.`watch_id` where `watches`.`deleted_at` is null and `created_at` < 2015-12-31 11:54:59 group by `watches`.`id`, `watch_translations`.`title`, `watch_translations`.`excerpt`, `watch_translations`.`content` having relevance > 4.50 order by `relevance` desc, `position` asc) as `watches` where `watches`.`deleted_at` is null and `created_at` < ? order by `position` asc)```

Searching on relationships is great. Any support for pivot tables?

Just installed the trait, did a basic search:
php $files = File::search($input['query'])->paginate(10);

First a PDO Exception:
PDOException in Connection.php line 288: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'relevance' in 'having clause'

QueryException in Connection.php line 620: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'relevance' in 'having clause' (SQL: select count(*) as aggregate from filesgroup byfiles.id having relevance > 5.75)

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?

I'm using Laravel 4.2.17. After installing Searchable using composer, I did something like this but getting the error Trait 'Nicolaslopezj\Searchable\SearchableTrait' not found.

<?php
 use Nicolaslopezj\Searchable\SearchableTrait;

 class Story extends Eloquent {
   use SearchableTrait;

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

type: Symfony\Component\Debug\Exception\FatalErrorException.

line: 5

if i use table attribute name 'desc', it gives error below, if I change it to other name it works.

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'desc) LIKE ? then 150 else 0 end) + (case when LOWER(desc) LIKE ? then 50 else 0' at line 1 (SQL: select * from

Hi,

It's possible to update your package to avoid duplicate results after search ?

For example:

protected $searchable = [
    'columns' => [
        'company_name' => 10,
        'code' => 5,
        'contacts.firstname' => 2,
        'contacts.lastname' => 2,
    ],
    'joins' => [
        'contacts' => ['clients.id', 'contacts.client_id']
    ]
];

If company_name is Test and contacts.firstname is Test, I have a duplicate results, it search Test in company_name, it displayed, it search Test in contacts.firstname and it displayed again.

Can we group the results for this case ?

Thx

当系统中存在表前缀时一下两种方式都会出错
=====调用=============================================
'columns' => [
'nameCn' => 2,
'nameEn' => 2,
'tab_classconfs.name'=>2,
// 'introduction' => 2,
],
'joins' => [
'classconfs' => ['filmtalents.tempid','classconfs.temp_id'],
],
======error===
Unknown column 'tab_tab_classconfs.name'
======调用===========================================
'columns' => [
'nameCn' => 2,
'nameEn' => 2,
'classconfs.name'=>2,
// 'introduction' => 2,
],
'joins' => [
'classconfs' => ['filmtalents.tempid','classconfs.temp_id'],
],
======error===

Unknown column 'classconfs.name'

更正方法
C:\wamp\www\film-on-web\vendor\nicolaslopezj\searchable\src\SearchableTrait.php
protected function makeGroupBy(Builder $query)
{
$driver = $this->getDatabaseDriver();
if ($driver == 'sqlsrv') {
$columns = $this->getTableColumns();
} else {

//下面这一句是我加的
$id = $this->getTable() . '.' .$this->primaryKey;

        $joins = array_keys(($this->getJoins()));

        foreach ($this->getColumns() as $column => $relevance) {
            $column = str_replace(DB::getTablePrefix(), "", $column);
            array_map(function($join) use ($column, $query){

                if(Str::contains($column, $join)){
                    $query->groupBy("$column");
                }

            }, $joins);

        }
    }
    $query->groupBy($id);
}

Hey! Suppose, there is a record - "The White Wizard", and I search for "White" or "wizard" it gives back null result. However, it works if I search "the", "The White", "White wizard".
Thank for your help!
@nicolaslopezj I've noticed that searching any word alone (in a record) except the first, gives null result.

Hi,
I have some problems using Searchable with L5 pagination.

Using "get" all works fine:

User::search('...')->get();

But with "paginate" I have an error:

User::search('...')->paginate(10);

The problem seems to be "HAVING" on pagination count ... take a look here: https://github.com/laravel/framework/blob/master/src/Illuminate/Database/Eloquent/Builder.php#L239

Thoughts?

Currently pagination is a little bloated ... what about add a specific method to the Trait?

An example of use:

$search = Input::get('search');
$page   = Input::get('page', 1);
$count  = Input::get('count', 10);
$from   = 1 + $count * ($page - 1);

$data = YourModel::search($search)
            ->take($count)
            ->skip($from - 1)
            ->get();

// Instance of paginator
$results = State::getSearchPaginator($data, $count);

Add this method to the Trait:

public static function getSearchPaginator($data, $perPage)
{
    $db_query_log = DB::getQueryLog();
    $db_query_sql = end($db_query_log);

    if ($data instanceof Collection)
    {
        $data = $data->toArray();
    }

    $total_items = DBHelper::getQueryCount($db_query_sql);

    return Paginator::make($data, $total_items, $perPage);
}

Thoughts?
If you agree, I can send a PR

I am having an issue with the trait's path. While locally it works fine, when used on the server, it can't be found. If I do composer dumpautoload -o, it does works again, but if I do simple composer dumpautoload it does not.

I have found that other psr-4 compliant packages for laravel don't have an inner folder structure in their src folder. I mean that everything inside the src folder is though to belong to the package's root namespace. So I tried to move the SearchableTrait.php file in the src folder and it worked!

Example output from 1.2.0:

select `posts`.*, 
if(slug = 'macbook', 75, 0) + if(slug LIKE 'macbook%', 25, 0) + if(slug LIKE '%macbook%', 5, 0) + 
if(content = 'macbook', 30, 0) + if(content LIKE 'macbook%', 10, 0) + if(content LIKE '%macbook%', 2, 0) + 
if(title = 'macbook', 150, 0) + if(title LIKE 'macbook%', 50, 0) + if(title LIKE '%macbook%', 10, 0) 
as relevance 
from `posts` group by `id` having relevance > 4.25 order by `relevance` desc

VS

Output from 1.2.1

select `posts`.*, 
if(content = 'macbook', 150, 0) + if(content LIKE 'macbook%', 50, 0) + if(content LIKE '%macbook%', 10, 0) 
as relevance 
from `posts` group by `id` having relevance > 7.5 order by `relevance` desc

I have something funky going on with the "relevance" alias in the query, which is causing a search to not return any results, despite there being valid results to match.

The code I have that is failing is:

SELECT DISTINCT `questions`.*,
     (CASE WHEN LOWER(`title`) LIKE 'commodi' THEN 60 ELSE 0 END) + 
    (CASE WHEN LOWER(`title`) LIKE 'commodi%' THEN 20 ELSE 0 END) + 
    (CASE WHEN LOWER(`title`) LIKE '%commodi%' THEN 4 ELSE 0 END) AS relevance
 FROM `questions`
 LEFT JOIN `commentable_entities` AS `question_commentable_entities` ON `commentable_entity_id_question` = `question_commentable_entities`.`id`
 LEFT JOIN `commentable_entities` AS `answer_commentable_entities` ON `commentable_entity_id_answer` = `answer_commentable_entities`.`id`
 LEFT JOIN `comments` AS `question_comments` ON `question_comments`.`commentable_entity_id` = `question_commentable_entities`.`id`
 LEFT JOIN `comments` AS `answer_comments` ON `answer_comments`.`commentable_entity_id` = `answer_commentable_entities`.`id`
 WHERE `questions`.`deleted_at` IS NULL
 GROUP BY `questions`.`id`
 HAVING relevance > 0
 ORDER BY `relevance` DESC

I have one row that has a relevance of 4, and many rows with a relevance of zero, but no results are returned from the query.

If I remove the JOINs from the query, it works fine and returns the rows as expected.

A colleague of mine also figured out a fix for the issue, where instead of HAVING relevance > 0 we instead just do the select statement again - having (CASE WHEN ...) > 0).

Digging into the code, I found the function filterQueryWithRelevance already sets up the HAVING clause with this method that works for all database drivers EXCEPT mysql:

https://github.com/RJacksonm1/searchable/blob/master/src/SearchableTrait.php#L187

I can't find any sort of documentation or rationale as to why this mysql-specific exclusion was included. This check was initially set up to check if the database driver was PostgreSQL with commit a1956ee, titled sql injection security, though that title seems irrelevant to this actual change.

Do you know why this check exists and if its important? I've removed it in a branch on my fork of this repository to fix the issue for my project, so if there is a problem introduced by my removal I'd like to know. :x

Thanks

protected function mergeQueries(Builder $clone, Builder $original) {
        $original->from(DB::raw("({$clone->toSql()}) as `{$this->getTable()}`"));
        $original->mergeBindings($clone->getQuery());
    }

if my config/database.php set the table prefix, it would be throw exception.
I find a reason is DB::raw cant get the table's prefix.

I know you can search basic relationships (one to one, one to many, etc) via joins:

'joins' => [
    'posts' => ['users.id', 'posts.user_id'],
    'post_tag' => ['posts.id', 'post_tag.post_id'],
    'tags' => ['post_tag.tag_id', 'tags.id']
]

However, there is no way to do an advanced join if you want to join polymorphic relationships. I.E. if you had a taggable relationship and you could have tags for both posts and comments.

Line 40 breaks any special character:

$search = strtolower(trim($search));

It seems like strtolower() doesn't support multi-byte characters or something?

See result:

php > echo strtolower('š');
�
php > echo mb_strtolower('š');
š

Error:

Column not found: 1054 Unknown column 'universities.name' in 'having clause'

Model:

   use SearchableTrait;

    protected $searchable = [
        'columns' => [
            'colleges.name' => 5,
            'colleges.address' => 4,
            'universities.name' => 3,
            'districts.name' => 2,
            'statuses.name' => 1
        ],
        'joins' => [
            'districts' => ['districts.id', 'colleges.district_id'],
            'universities' => ['universities.id', 'colleges.university_id'],
            'statuses' => ['statuses.id', 'colleges.status_id']
        ]
    ];

Query:

return $this->collegeModal->search($search)->with(['university', 'district', 'status', 'user', 'college_courses.course'])->get();

This does not happen when I fall back to version 1.5.12

It works really fine on mySQL but when I run it on Heroku that is using pgSQL it returns a QueryException

SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "."
LINE 1: ...om (select "users".*, avg((case when LOWER(`users`.`firstnam...
^ (SQL: select * from (select "users".*, avg((case when LOWER(`users`.`firstname`) ILIKE jean then 150 else 0 end) + (case when LOWER(`users`.`firstname`) ILIKE jean% then 50 else 0 end) + (case when LOWER(`users`.`firstname`) ILIKE %jean% then 10 else 0 end) + (case when LOWER(`users`.`middlename`) ILIKE jean then 150 else 0 end) + (case when LOWER(`users`.`middlename`) ILIKE jean% then 50 else 0 end) + (case when LOWER(`users`.`middlename`) ILIKE %jean% then 10 else 0 end) + (case when LOWER(`users`.`lastname`) ILIKE jean then 150 else 0 end) + (case when LOWER(`users`.`lastname`) ILIKE jean% then 50 else 0 end) + (case when LOWER(`users`.`lastname`)....

Hi,
after updating and got the latest commit, I found 2 things broken. First is about the Class 'Nicolaslopezj\Searchable\Str' not found issue. The second one is the results are duplicated. Any idea?

Thanks.

The documentation states that passing a threshold parameter to search() will overrule the default threshold value, the example being ->search($query, 0) to return all records. However, by passing 0, the default threshold will still be applied because 0 is falsey, just like null. The line of code to be changed would probably be:

$threshold ?: ($relevance_count / 4)

Can i use this trait if i want to search through table's relations( one to many, etc) ?

SearchableTrait, line 133

if(Str::contains($column, $join)){

Where can I get Str class?

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

Hi,

I have a problem when I want to paginate search. Laravel returns

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'relevance' in 'having clause' (SQL: select count(*) as aggregate from `posts` group by `posts`.`id` having relevance > 5.25)

that add possibility to check the type of the model.

The search is worked perfectly but the page number doesn't shown in the view file. While using the paginate.

Hi,
Having the Product model with the following relationship through products_categories table:

    public function sectors()
    {
        return $this->belongsToMany('Category', 'products_categories', 'product_id', 'category_id');
    }

how can I use $searchable in order to join products with categories? In the readme file you show an example using 1:N relationships (user has many posts), but no idea how to reflect N:M relationship with this

Thanks in advance

If you have a field named "relevance" in your table, the system did not work... make this "virtual field" configurable or name it like "relevance" or "searchable_relevance"... so the chance to have a conflict is minimized.

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.

Im doing some joins. And when i search a common word, it tends to duplicate my display.

For example.

I have tags and genre. I have "Rock" tag in tags table and "Rock" genre in genre table. When i search "Rock", it displays the users, it duplicates my result.

When I select set of columns then use the search scope, the result set is showing all columns in table instead of just the selected ones.

Example of columns selections failures:
Model::select($columns)->search($query);
or
$users = User::search($query)->paginate(20, $columns);

After a quick look, this line
$query->select($this->getTable() . '.*');
is selecting all columns and ignoring any previous selections. Commenting it solves the issue for me.

How do I construct my search controller that combines all the models that I'm searching?

How do I send those results to my view?

Is there perhaps a working example somewhere?

Many thanks.

Hi,

I am looking for mysql full text based search engine is this plugin support this functionality? Or let me know how we can make it one

When I do where('status',true) chain method along with search. It gives me integer error . Doesn't work well with boolean.

SQLSTATE[HY093]: Invalid parameter number (SQL: select count() as aggregate from (select clips., (case when LOWER(title) LIKE 0 then 300 else 0 end) + (case when LOWER(title) LIKE 0 then 100 else 0 end) + (case when LOWER(

Getting the following error when trying to use the count() method on the query builder

Column not found: 1054 Unknown column 'relevance' in 'order clause' (SQL: select count(*) as aggregate from `categories` group by `id` having relevance > 3.75 order by `relevance` desc)

Is there any reason for not groupping the results?

Hello,

thanx for this very good job.

in Laravel 5, you need to change user Str by use Illuminate\Support\Str;

This is a great addition to models and your relevance math really hits the mark. However, this doesn't seem to work with sqlsrv driver.

The problems:

1. The $query string does not come out quoted in the case statements. (e.g there are no quotes surrounding the query string).
2. The having clause (or where clause) cannot refer to column aliases, and so the entire CASE statements used in the select clause must be repeated.

Any tips on how I might start addressing these? I'm using v1.3 pulled via composer.

Im loving this but some of my tests are coming back negative but i can't work
out why.

it seems to be struggling to find anything in'text' columns, but has no issues with varchar columns.

$table->text('content_1')->nullable();

is this a bug or do i need to adjust my expectation? Do i need to assign a fulltext in phpmyadmin?

Hello everyone !! I have two tables user and post .... a user create posts so has a relationship set up in models. Now in user controller I want to search posts that is created by a particular user.

I know I can use Post::search($string)->where(some_condition)->get().

But I want something like this $user->post()->search($string)->get()

Release 1.5.0 introduces a bug, at least in L4. I didn't test L5.

The mergeQueries() method doesn't include the table prefix in the raw query. I'm getting errors with tables that have deleted_at columns like:

Unknown column 'prefix_tablename.deleted_at' in 'where clause' (SQL: select * from ( ... subquery ...) as tablenamewhereprefix_tablename.deleted_at is null