Search does't work with WHERE

[andrii-trush]

Hello,

I have installed latest version of scout and this package.

my config

    'mysql' => [
        'mode' => 'NATURAL_LANGUAGE',
        'model_directories' => [app_path(), TDEV_AGENCY_CMS_ROOT . 'public'],
        'min_search_length' => 0,
        'min_fulltext_search_length' => 4,
        'min_fulltext_search_fallback' => 'NATURAL_LANGUAGE',
        'query_expansion' => false
    ]

DB structure

CREATE TABLE `post_localizations` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `post_id` bigint(20) unsigned NOT NULL,
  `lang` char(3) COLLATE utf8mb4_unicode_ci NOT NULL,
  `title` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` longtext COLLATE utf8mb4_unicode_ci,
  `meta_title` text COLLATE utf8mb4_unicode_ci,
  `meta_description` text COLLATE utf8mb4_unicode_ci,
  `short_description` text COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `post_localizations_lang_index` (`lang`),
  KEY `post_localizations_post_id_foreign` (`post_id`),
  FULLTEXT KEY `post_localizations` (`lang`,`title`,`description`,`short_description`),
  CONSTRAINT `post_localizations_post_id_foreign` FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`)
) 

My method is

PostLocalization::search($request->query->get('q'))->where('lang', 'ru')->get()

It works perfectly if where is dropped but with where it doesn't work.

In the method search of this package class MySQLEngine

i have added

$query->toSql()

And run sql command directly in mysql and it returns correct result

$result['count'] of method search returns 1, $result['results'] return empty collection

Somebody can help me?

[flipersclub]

I've seen since 3.0 the ->where() stopped working it started selecting the relevance in the SELECT clause - however all the parameter were added to the binding so we had 4 bindings and 3 ? in the query.

Downgrade Scout back down to 7.0 and the MySQL Driver down to 2.4 and it starts working again - though no support for Laravel 8.0

[nev7n]

I've hit the same issue, downgrade didn't work for me. I'm on the following versions:

laravel/framework                     v6.18.11 
laravel/scout                         v8.0.1
yab/laravel-scout-mysql-driver        v3.0.0

I tried scout ^7.0 and mysql driver ^2.4 but still wasn't working.

[quangtam]

I've hit the same issue, downgrade didn't work for me. I'm on the following versions:

laravel/framework                     v6.18.11 
laravel/scout                         v8.0.1
yab/laravel-scout-mysql-driver        v3.0.0

I tried scout ^7.0 and mysql driver ^2.4 but still wasn't working.

Same with me!

[xleedos]

For those struggling with this and looking for a quick fix: change /src/Engines/Modes/Mode.php, Line 43
from:
$queryString .= "$field $operator ? AND ";

To:
$queryString .= "$field $operator $value AND ";

:-)

[Adam-Mould]

Search works with WHERE when using LIKE mode. NATURAL_LANGUAGE seems to have the issue.

A quick fix for Natural Language: /src/Engines/MySQLEngine.php, Line 69

if ($mode->isFullText()) {
  $query = $query->selectRaw(DB::raw($mode->buildSelectColumns($builder)), $params);
}

Change to...

if ($mode->isFullText()) {
  $query = $query->selectRaw(DB::raw($mode->buildSelectColumns($builder)), [$params[0]]);
}

It seems the NaturalLanguage.php, buildParams() method appends the search query to the params array, however, when the MySQLEngine.php search() method detects an isFullText() mode it uses selectRaw and passes the params array as bindings in the wrong order. The above fix simply uses the zero-based item from the params array (which is the search query).

[mikemike]

Search works with WHERE when using LIKE mode. NATURAL_LANGUAGE seems to have the issue.

A quick fix for Natural Language: /src/Engines/MySQLEngine.php, Line 69

if ($mode->isFullText()) {
  $query = $query->selectRaw(DB::raw($mode->buildSelectColumns($builder)), $params);
}

Change to...

if ($mode->isFullText()) {
  $query = $query->selectRaw(DB::raw($mode->buildSelectColumns($builder)), [$params[0]]);
}

It seems the NaturalLanguage.php, buildParams() method appends the search query to the params array, however, when the MySQLEngine.php search() method detects an isFullText() mode it uses selectRaw and passes the params array as bindings in the wrong order. The above fix simply uses the zero-based item from the params array (which is the search query).

This fully fixes the issue. Any plans to merge this fix?

[metrixinfo]

I can confirm the "FIX" works for Laravel 8.
Can we get this merged please?

[msonowal]

@metrixinfo
Check the latest version 4

[kespinosa]

i am using laravel 9 with laravel scout mysql 5.1 and this one does not work where. checking it i found that the ksort changes the order in which the query is assembled, passing the first text by placing it in the first where.

query builder

$Post = DscmsPost::search($request->q)->where('id_type_post',$this->tipo_post)->where('buscar',1)->paginate($this->page);

excerpt from the query being prepared:

"id_type_post = ? AND buscar = ? AND MATCH(description,img_preview,img_principal,keywords,permalink,post,post_preview,purl,subtitle,title) AGAINST(? IN NATURAL LANGUAGE MODE)

string to search: conveniencia

query that was executed>

select
  count(*) as aggregate
from
  `dscms_post`
where
  id_type_post = 'convivencia'
  AND buscar = 1
  AND MATCH(
    description,
    img_preview,
    img_principal,
    keywords,
    permalink,
    post,
    post_preview,
    purl,
    subtitle,
    title
  ) AGAINST(1 IN NATURAL LANGUAGE MODE)

commenting line 68 of /src/Engines/MySQLEngine.php, where the param is ordered with the ksort, solves the problem.