tomlingham / laravel-searchy Goto Github PK

Issue

On my hosting I got this error:

SQLSTATE[42000]: Syntax error or access violation: 1305 FUNCTION regfmri2.ROUND does not exist (SQL: select *, IF(CONCAT(id, ' ', first_name, ' ', last_name) = 'foo', 100, 0) + IF(CONCAT(id, ' ', first_name, ' ', last_name) LIKE 'foo%', 50, 0) + IF(CONCAT(id, ' ', first_name, ' ', last_name) LIKE 'F% O% O%', 42, 0) + IF(REPLACE(CONCAT(id, ' ', first_name, ' ', last_name), '\.', '') LIKE '%f%o%o%', ROUND(40 * (CHAR_LENGTH( 'foo' ) / CHAR_LENGTH( REPLACE(CONCAT(id, ' ', first_name, ' ', last_name), ' ', '') ))), 0) + IF(CONCAT(id, ' ', first_name, ' ', last_name) LIKE 'foo%', 35, 0) + IF( CHAR_LENGTH( TRIM(CONCAT(id, ' ', first_name, ' ', last_name))) = CHAR_LENGTH( REPLACE( TRIM(CONCAT(id, ' ', first_name, ' ', last_name)), ' ', '')) AND CONCAT(id, ' ', first_name, ' ', last_name) LIKE BINARY 'F%O%O%', 32, 0) + IF(CONCAT(id, ' ', first_name, ' ', last_name) LIKE '%foo%', 30, 0) + 8 * ROUND ((
CHAR_LENGTH(CONCAT(id, ' ', first_name, ' ', last_name)) - CHAR_LENGTH( REPLACE ( LOWER(CONCAT(id, ' ', first_name, ' ', last_name)), lower('foo'), ''))
) / LENGTH('foo')) AS relevance from `subjects` having `relevance` > 0 order by `relevance` desc)

Which is caused by + 8 * ROUND (( contained in the code above. For some reason, the space between ROUND and openning bracket ( makes the MySQL parser think, that the ROUND is a table name. See http://stackoverflow.com/a/12566366/3285282.

Solution

Please, generate the SQL query in a way, that it contains

+ 8 * ROUND((

instead of

+ 8 * ROUND ((

Database version

MySQL [regfmri2]> SHOW VARIABLES LIKE "%version%";
+-------------------------+----------------+
| Variable_name           | Value          |
+-------------------------+----------------+
| protocol_version        | 10             |
| version                 | 5.0.67         |
| version_comment         | SUSE MySQL RPM |
| version_compile_machine | x86_64         |
| version_compile_os      | suse-linux-gnu |
+-------------------------+----------------+

Please note that this issue doesn't occur on my dev machine with MySQL 5.6.27.

Query:
Searchy::search('categories')->fields('title')->query($keyword)->getQuery()->limit(3)->get();

Result

SQLSTATE[42000]: Syntax error or access violation: 1463 Non-grouping field 'relevance' is used in HAVING clause (SQL: select *, IF(COALESCE(title, '') = 'keyword', 100, 0) + IF(COALESCE(title, '') LIKE 'keyword%', 50, 0) + IF(COALESCE(title, '') LIKE 'K% E% Y% W% O% R% D%', 42, 0) + IF( REPLACE(COALESCE(title, ''), '.', '') LIKE '%k%e%y%w%o%r%d%', ROUND(40 * ( CHAR_LENGTH( 'keyword' ) / CHAR_LENGTH( REPLACE(COALESCE(title, ''), ' ', '') ))), 0) + IF(COALESCE(title, '') LIKE 'keyword%', 35, 0) + IF( CHAR_LENGTH( TRIM( COALESCE(title, '') )) = CHAR_LENGTH( REPLACE( TRIM( COALESCE(title, '') ), ' ', '')) AND COALESCE(title, '') LIKE BINARY 'K%E%Y%W%O%R%D%', 32, 0) + IF(COALESCE(title, '') LIKE '%keyword%', 30, 0) + 8 * ROUND((
CHAR_LENGTH(COALESCE(title, '')) - CHAR_LENGTH( REPLACE( LOWER(COALESCE(title, '')), lower('keyword'), ''))
) / LENGTH('keyword')) AS relevance from categories having relevance > 0 order by relevance desc limit 3)

Im getting a error when im using paginate when i do this:
Is there a solution ?

Error im getting:

$data = shoe::hydrate(Searchy::search('shoe')
->fields('brand','type','information','style_code','keywords')->query($keyword)
->getQuery()->having('relevance', '>', 20)
->join('uploaded_by', function ($join) {
$join->on('shoe.user_id', '=', 'uploaded_by.user_id');
})
->join('brand', function ($join){
$join->on('shoe.brand_id', '=', 'brand.brand_id');
})
->join('type', function ($join){
$join->on('shoe.type_id', '=', 'type.type_id');
})
->where('shoe.visible','=', 1)
->where('shoe.power','=', 1)
->paginate($amount_items));

From the doc. It said the result returns an object array. However I only getting a normal array. What is the way to return an object array.

I would like to pull in related models.

$users = Searchy::users('id', 'name', 'email')->with('profile')->query($q)->get();
// or
$users = Searchy::users('id', 'name', 'email')->query($q)->get();
$users->load('profile');

Maybe I'm missing something but is it possible to have multiple keyword search using Searchy?

I installed this repository but instead of specifying "2.", I had to specify "2.@dev" to meet composer's minimum stability requirements for my default installation of Laravel 5.1. I was getting an error that the package could not be found (can also be caused by minimum stability incompatibility). Is this expected?

Below is my Laravel composer.json file

{
    "name": "laravel/laravel",
    "description": "The Laravel Framework.",
    "keywords": ["framework", "laravel"],
    "license": "MIT",
    "type": "project",
    "require": {
        "php": ">=5.5.9",
        "laravel/framework": "5.1.*",
        "tom-lingham/searchy" : "2.*@dev"
    },
    "require-dev": {
        "fzaninotto/faker": "~1.4",
        "mockery/mockery": "0.9.*",
        "phpunit/phpunit": "~4.0",
        "phpspec/phpspec": "~2.1"
    },
    "autoload": {
        "classmap": [
            "database"
        ],
        "psr-4": {
            "App\\": "app/"
        }
    },
    "autoload-dev": {
        "classmap": [
            "tests/TestCase.php"
        ]
    },
    "scripts": {
        "post-install-cmd": [
            "php artisan clear-compiled",
            "php artisan optimize"
        ],
        "pre-update-cmd": [
            "php artisan clear-compiled"
        ],
        "post-update-cmd": [
            "php artisan optimize"
        ],
        "post-root-package-install": [
            "php -r \"copy('.env.example', '.env');\""
        ],
        "post-create-project-cmd": [
            "php artisan key:generate"
        ]
    },
    "config": {
        "preferred-install": "dist"
    }
}

Searchy::users('name', 'email')->query('John Smith')->get();

I expect users in this example to be a modelname but instead it is the table name. Why not use the modelname and take advantage of the model's configuration?

My Laravel is 5.3,I use ufuzzy,

return [

    'default' => 'ufuzzy',

        ...
]

error:

FatalThrowableError in SearchBuilder.php line 104:
Class name must be a valid object or a string

And,I use fuzzy,
error:

SQLSTATE[42000]: Syntax error or access violation: 1463 Non-grouping field 'relevance' is used in HAVING clause

my version is 5.1 Can I use it?
SearchBuilder.php 104 line has an error
Class name must be a valid object or a string

SearchyServiceProvider.php
use Laravel\Lumen\Application as LumenApplication;
laravel not lumen

Just like Searchy：........->orderBy("","desc")?

$results = Searchy::search('table_name')->fields(['series_name', 'feature_title'])->query('Batman')->get();

this returning empty results and

$results = Searchy::search('table_name')->fields('series_name')->query('Batman')->get();

This is working fine and returning results.

Hello,

I have a big table containing geocoded items that I filter for a radius of distance from a latitude and longitude coordinates (ex. to match items near 10 km from coordinates). After that I need to order the results by relevance to a given search keyword and is for that I use Searchy with a lot of satisfaction, but I have a limitation that represents for me a big performance weak.

$items = Searchy('items')->fields('name')
                         ->query('foobar')
                         ->getQuery()
                         ->distance($latitude, $longitude, $radius)
                         ->take(10)
                         ->get();

distance is a local scope of my Items-model and is used to call a stored MySQL procedure that calculates the distance between $latitude, $longitude and items' coordinates included in a $radius of distance.

That example works but is very un-performant because the search based on name is performed before the geographic filter so instead of search over 1000 items it searches over 1 million.

I have not found a way to use Searchy AFTER a query filtering, I can not filter the query BEFORE.

Why not extend Eloquent e allow a use of Searchy like this?

$items = Item::distance($latitude, $longitude, $radius)->search('foobar', ['name'])
                                                       ->take(10)
                                                       ->get();

It would be a lot more simple and intuitive than now and this use will not force you to do escamotages to work with the native Eloquent system.

What do you think?

Error when use using quotes.

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 '% R% E% D%', 42, 0) ...

$query = "dishon'red";

dd(Searchy::products('name')->query($query)->get());

check ConsecutiveCharactersUnicodeMatcher plz

Hi there

Your description says:

Searchy is an; easy-to-use, light-weight, MySQL only

I was wondering would this work for SQlite?

This happens in Laravel 5.3, and has happened both for the default search driver and levenshtein.

It would appear to be down to L 5.3 having "strict" mode set to true in the config/database.php file, under the MYSQL connection setting.

Unsure about a way round this, as my MYSQL isn't amazingly strong, but I've bypassed it for the moment by making that value false.

Hope this can help someone else.

first I do:
$query = Searchy::search('advertisements');
$query= $query->fields('title')->query(Input::get('title'))
->getQuery()->having('relevance', '>', 50);

again after this I need to do another query with searchy on $query which I have made before,
How can I do this,plz help :(

Hi,
Is there any way in which we can get selected columns rather than the whole row, I tried using get(['id', 'name']) but it's not working.

Hello . I want to know does this package support inner Joins ? if yes then how to use it ? As many time we face the scenario where search result coming from the different tables .

Hi!
how i can use many query?
for example i wonna find all skills where have word indesign and photoshop :)
Searchy::driver('ufuzzy')->skills('name')->query('indesign', 'photoshop')->get();

Hi,

What is the scope of relevance value? I want to make a slider in my search form between 'retrieval' and 'precision', but I do not know scope of the relevance value :/

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 'having relevance > ? order by relevance desc limit 100' at line 3 (SQL: select *, IF(name = 'admin', 100, 0) + IF(name LIKE 'admin%', 50, 0) + IF(name LIKE 'A% D% M% I% N%', 42, 0) + IF(REPLACE(name, '.', '') LIKE '%a%d%m%i%n%', ROUND(40 * (CHAR_LENGTH( 'admin' ) / CHAR_LENGTH( REPLACE(name, ' ', '') ))), 0) + IF(name LIKE 'admin%', 35, 0) + IF( CHAR_LENGTH( TRIM(name)) = CHAR_LENGTH( REPLACE( TRIM(name), ' ', '')) AND name LIKE BINARY 'A%D%M%I%N%', 32, 0) + IF(name LIKE '%admin%', 30, 0) + 8 * ROUND ((
CHAR_LENGTH(name) - CHAR_LENGTH( REPLACE ( LOWER(name), lower('admin'), ''))
) / LENGTH('admin')) AS relevance having relevance > 0 order by relevance desc limit 100)

Hi there,

First and foremost thanks for all the good word, simply splendid stuff.

I was looking at the documentation and saw that the recommendation for the use of hydrate. however I got strange errors because of the way searchy returns the data. What I believe is that you return a kind of object instead of regular json, thus requirering a toArray() afther the get().

Is this indeed true and if so would you kindly update to docs ?

example:
\App\Product::hydrate(Searchy::products('naam') ->query($searchvalue)->getQuery()->where('bakkerij_id','=',$bakkerij->id)->having('relevance','>',20)->get()->toArray());

unable to require 1.0 branch (not found)

I know it is posible to rename the field, but i would like to have an option on the configuration to remove it after the query, because I'm querying the database with different texts and I realize I can not use
->query($text1)->query($text2)
because is like querying the query and what I want is a join.
So I'm trying to use array_unique(array_merge($workers, $moreWorkers), SORT_REGULAR);

$members = Searchy::members('name')->query('hussain');
No problem, page loads.
var_dump($members);
page is blank! even the existing markup does not show.

Anything I'm doing wrong?

When you do a search today an array is returned when running get(). Is it possible to make it return an Eloquent collection?

Awesome package Tom!
Thank you very much!

Say you got the following table schema:

CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `col1` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
  `col2` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
  `col3` varchar(30) CHARACTER SET utf8 DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

As you can see col3 is allowed NULL values.

Searchy::test('col1', 'col2', 'col3')->query('test')->get()

will give an empty array even if there are values matching query in col1 and col2.

Searchy::test('col1', 'col2')->query('test')->get()

gives the appropriate results.

I did some further testing and found out that when using nullable fields for searching into, if at least one of them is NULL then the whole row is ignored, even if there are matches in other fields.

Hey!

Just installed Searchy and the searching itself works great. However, I'm wondering if it's possible to paginate the results?

I'm comparing it to using the Query Builder and paginating the results as such:

    $jobMatches = Job::where('title', 'like', $searchQuery)
        ->orWhere('description', 'like', $searchQuery)
        ->paginate($this->numPerPage)
        ->sortByDesc('published_at')
        ->all();

I would love to do the same thing, something like this:

    $jobMatches = Searchy::search('jobs')
        ->fields('title', 'description')
        ->query($searchQuery)
        ->getQuery()
        ->having('relevance', '>', 10)
        ->paginate($this->numPerPage)
        ->get();

Am I missing something obvious to make this work, or is this just something I'll have to dream of? 😄

I got this error when use Searchy with L5.2

Call to undefined method Illuminate\Foundation\Application::bindShared()

It's because bindShared() have been replaced with singleton(). Please update #it. Thank you

I am a huge fan of the package, and was wondering if it would be possible to override the default database connection that searchy uses. If there is not currently this functionality, I would be interested in contributing that type of functionality, but do not know exactly where in your package that type of configuration would go.

Thanks for what you are doing for the Laravel Community!

Hi, is there a way to use queryScope or Eager load using this package?

On my User Model I have this scope:

/**
 * Active Users.
 */
public function scopeActive($query)
{
    return $query->where('active', 1);
}

Now I want to get/filter just the active user. I tried this code but it didn't work:

$users = Searchy::users('name')->query('John Smith')->active()->get();

Call to undefined method TomLingham\Searchy\SearchDrivers\FuzzySearchDriver::active()

I also tried to eager load the roles of my user using 'with' method but no luck.

$users = Searchy::users('name')->query('John Smith')->with('roles')->get();

Call to undefined method TomLingham\Searchy\SearchDrivers\FuzzySearchDriver::with()

Any possible solution? Thanks.

too many result, need TOP() method

Dear,

Searchy is amazing tool. Thanks for sharing.

I have question about using Larave-Searchy. How can I use relations or Eloquent with method during searcing?

How can I search through multiple tables connected with foreign keys?

Hi,

I have a table like this:

CREATE TABLE customers
(
    id INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
    first_name VARCHAR(255),
    last_name VARCHAR(255),
    email VARCHAR(255),
    created_at TIMESTAMP DEFAULT '0000-00-00 00:00:00' NOT NULL,
    updated_at TIMESTAMP DEFAULT '0000-00-00 00:00:00' NOT NULL,
);

And a single user with first_name Robert and last_name King.

When I run the statement:

$results = Searchy::customers('first_name', 'last_name')->query('robert king')->get();

I expect it to return the user called Robert King, but it doesn't.

Thanks

Can you please post instructions on how to use it with DB:table?

Joins used on the search queries don't work very well. Take this for example:

$posts = Searchy::posts('name', 'body', 'users.name')->query('Bob')
    ->leftJoin('users', 'user_id', '=', 'users.id')
    ->get(['posts.name', 'body', 'users.id as user_id', 'users.name as user_name']);

Rather than receiving an array of objects with the requested columns and their aliases, you get an array of objects with the combined columns of both tables, without their aliases, with the joined table's columns overwriting any with the same name.

Is there anyway that your package can return trashed results?
By adding a flag, or indicator can the query results will return trashed items. I'm thinking something like
app()->searchy->assets('model', 'serial', 'service_tag', 'asset_tag')->query($search)->withTrashed()->get();

The DB::table should return all results, but at some point the search results filter out the soft deleted items.

I get error when i use

public function getSearch($value='')
{
//$users = Searchy::users('first_name', 'email', 'last_name')->query($value)->get();
//$users = Searchy::search('users')->query($value)->get();
$users = Searchy::search('users')->query('John Smith');
// return Response::json($users->toArray());
}

Non-static method TomLingham\Searchy\SearchBuilder::search() should not be called statically, assuming $this from incompatible context

How can i use orderby?
Its not working in this code

 $all_products = shoe::hydrate(Searchy::search('shoe')
                ->fields('keywords','type.type')
                ->query($keywords)
                ->getQuery()
                ->join('uploaded_by', function ($join) {
                    $join->on('shoe.user_id', '=', 'uploaded_by.user_id');
                })
                ->join('brand', function ($join){
                    $join->on('shoe.brand_id', '=', 'brand.brand_id');
                })
                ->join('type', function ($join){
                    $join->on('shoe.type_id', '=', 'type.type_id');
                })
                ->where('shoe.visible','=', 1)
                ->where('shoe.power','=', 1)
                ->having('relevance', '>', 10)
                ->orderby('shoe.release_date','desc')
                ->orderby('shoe.is_ad','desc')
                ->get());

For example, I'm making search like this

$users = DB::table('users') ->where('name', 'like', '%' . $query . '%') ->paginate(6);

Now I want to use it like:

$users = Searchy::search('users')->fields('name', 'email')->query($query)->get();

How to use pagination? Thanks.

Big sidenote; I'm one of the founders of StyleCI.

You should look at StyleCI for automatic code standards fixing. We can automate the entire process of fixing, pull request and merging for you too.

I followed the instruction to install this package but I can't make it works. I'm using Laravel 5.4

I'd like to use it in my RegisterController.php

namespace App\Http\Controllers\Auth;

use Searchy;

class RegisterController extends Controller { 
    public function example () {
        Searchy::search('companies')->fields('name')->query('test')->getQuery()->limit(1)->get();
    }   
}

I got the error Class 'Searchy' not found

Hi,

When calling ->paginate() on the query builder returned by the Searchy package, it is throwing an SQL error.

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'relevance' in 'having clause' (SQL: select count(*) as aggregate from `burial_records` having `relevance` > 0) 

Thanks

I don't know if this is a bug with your library or simply me not configuring it correctly but I have got searchy working and searching an index table containing content scraped from my website.

To better explain what I am doing, imagine an index table that has the following row (among many others):

{
    id: 1,
    body: "Product Name® This product is really good at solving problems with cold and flu"
}

I have been using ->having('relevance', '>', 20) and if I search that table with the query "product name" it will return the one matching row, however if I search it with the query "product name cold and flu" it will return nothing. The expected result is that it should return the same thing, probably with a higher relevancy score.

Hi.
Not working with russian text, english working normal.
If i trying find something on Russian, he return all position

I am using Laravel 5.3.
I have disabled strict mode.

        'mysql' => [
            //...
            'strict' => false,
           //...
        ]

Query:

$articles = \Searchy::articles('title', 'content')->query($keywords)->get();

Result:

QueryException in Connection.php line 761:
SQLSTATE[42000]: Syntax error or access violation: 1463 Non-grouping field 'relevance' is used in HAVING clause (SQL: select *, IF(COALESCE(`title`, '') = 'book', 100, 0) + IF(COALESCE(`title`, '') LIKE 'book%', 50, 0) + IF(COALESCE(`title`, '') LIKE 'B% O% O% K%', 42, 0) + IF( REPLACE(COALESCE(`title`, ''), '\.', '') LIKE '%b%o%o%k%', ROUND(40 * ( CHAR_LENGTH( 'book' ) / CHAR_LENGTH( REPLACE(COALESCE(`title`, ''), ' ', '') ))), 0) + IF(COALESCE(`title`, '') LIKE 'book%', 35, 0) + IF( CHAR_LENGTH( TRIM( COALESCE(`title`, '') )) = CHAR_LENGTH( REPLACE( TRIM( COALESCE(`title`, '') ), ' ', '')) AND COALESCE(`title`, '') LIKE BINARY 'B%O%O%K%', 32, 0) + IF(COALESCE(`title`, '') LIKE '%book%', 30, 0) + 8 * ROUND((
CHAR_LENGTH(COALESCE(`title`, '')) - CHAR_LENGTH( REPLACE( LOWER(COALESCE(`title`, '')), lower('book'), ''))
) / LENGTH('book')) + IF(COALESCE(`content`, '') = 'book', 100, 0) + IF(COALESCE(`content`, '') LIKE 'book%', 50, 0) + IF(COALESCE(`content`, '') LIKE 'B% O% O% K%', 42, 0) + IF( REPLACE(COALESCE(`content`, ''), '\.', '') LIKE '%b%o%o%k%', ROUND(40 * ( CHAR_LENGTH( 'book' ) / CHAR_LENGTH( REPLACE(COALESCE(`content`, ''), ' ', '') ))), 0) + IF(COALESCE(`content`, '') LIKE 'book%', 35, 0) + IF( CHAR_LENGTH( TRIM( COALESCE(`content`, '') )) = CHAR_LENGTH( REPLACE( TRIM( COALESCE(`content`, '') ), ' ', '')) AND COALESCE(`content`, '') LIKE BINARY 'B%O%O%K%', 32, 0) + IF(COALESCE(`content`, '') LIKE '%book%', 30, 0) + 8 * ROUND((
CHAR_LENGTH(COALESCE(`content`, '')) - CHAR_LENGTH( REPLACE( LOWER(COALESCE(`content`, '')), lower('book'), ''))
) / LENGTH('book')) AS relevance from `articles` where `deleted_at` is null having `relevance` > 0 order by `relevance` desc)

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 'key = 'global', 100, 0) + IF(key LIKE 'global%', 50, 0) + IF(key LIKE 'G% L% O% ' at line 1 (SQL: select *, IF(key = 'global', 100, 0) + IF(key LIKE 'global%', 50, 0) + IF(key LIKE 'G% L% O% B% A% L%', 42, 0) + IF(REPLACE(key, '.', '') LIKE '%g%l%o%b%a%l%', ROUND(40 * (CHAR_LENGTH( 'global' ) / CHAR_LENGTH( REPLACE(key, ' ', '') ))), 0) + IF(key LIKE 'global%', 35, 0) + IF( CHAR_LENGTH( TRIM(key)) = CHAR_LENGTH( REPLACE( TRIM(key), ' ', '')) AND key LIKE BINARY 'G%L%O%B%A%L%', 32, 0) + IF(key LIKE '%global%', 30, 0) + 8 * ROUND ((
CHAR_LENGTH(key) - CHAR_LENGTH( REPLACE ( LOWER(key), lower('global'), ''))
) / LENGTH('global')) AS relevance from table having relevance > 0 order by relevance desc limit 100)