QueryException when when more than 1 model indexed

[Cookizza]

Hi guys,

This is a bit strange, as I swear it was working at one point. Now however it seems broken.

I have 2 models, App\Page & App\Advice - their toSearchableArray both look similar - like below:

public function toSearchableArray() {
    $search = [
        'title'    => $this->title,
        'category' => $this->category,
        'subject'  => $this->subject,
        'intro'    => strip_tags($this->intro),
        'content'  => strip_tags($this->content),
        'tags'     => is_array($this->tags) ? implode(' ',$this->tags) : ''
    ];

    $search[$this->getKeyName()] = $this->getKey();

    return $search;
}

Now I run the command php artisan tntsearch:import App\\Page

Search the works as I'd expect, returning results no problem for pages (or, if I instead only indexed with App\Advice Advice gets searched fine too)

However when both are index using the above command, I suddenly start hitting error 500 like the below:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'advices.id' in 'where clause' (SQL: select * from `pages` where `pages`.`id` in (20, 18, 1, 19, 13, 24, 27, 12, 10, 26, 17, 32, 15, 14, 35, 3, 33, 34, 44, 7, 4, 38, 5, 2) and `advices`.`id` in (33, 37, 19, 41, 18, 40, 5, 43, 29, 7, 28, 48, 72, 15, 75, 31, 59, 51, 81, 58, 30, 52, 77, 11, 62, 46, 23, 45, 60, 47, 35, 56, 54, 79, 68, 61, 83, 20, 34, 42, 50, 38, 63, 55, 70, 36, 3, 73, 49, 10, 25, 69, 22, 71, 76, 64, 78, 66, 65, 84, 67, 8, 16, 27, 74, 4, 9, 53, 39, 57, 17, 6, 13, 80, 32, 21, 14))

It dies on line 205 on /src/Engines/TNTSearchEngine.php - if that's helpful.

Is there something I'm missing?

[Cookizza]

This is so strange, it's like the query is trying to access other tables that are index without me even asking it to.

Article::search('Dog')->get();
Illuminate/Database/QueryException with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'articles.id' in 'where clause' (SQL: select * from `pages` where `pages`.`id` in (18, 1, 12, 32, 19) and `pages`.`id` in (18, 1, 12, 32, 19) and `pages`.`id` in (18, 1, 12, 32, 19) and `articles`.`id` in (2) and `articles`.`id` in (2) and `animals`.`id` in (133, 47, 46, 63, 64, 79, 122, 132, 19, 43, 44, 68, 70, 78, 119, 123, 134, 135, 137, 152, 158, 160, 25, 27, 36, 66, 67, 69, 83, 99, 100, 121, 131, 149, 172) and `articles`.`id` in (2))'

Why is it trying to search animals and pages?? I am only asking it to search articles..

[michaelklopf]

Which version of the driver are you using? So we know in which version line 205 is failing. Have you debugged a little deeper? Is the problem on the driver side, or tnt search side?

[samoldenburg]

Am having the same issue with a fresh install today using latest versions.

[michaelklopf]

@samoldenburg Which Laravel version?

Is 'php artisan scout:import App\Page' working?

We use that one because the tntsearch one does not work with relationships.

[Cookizza]

Laravel 5.7 & TNT driver 3.2.1

Pretty sure 5.7 caused this - perhaps some query optimisation?

If i search any model alone on a single request, it works fine. As soon as more than one model is searched on a single request - we get these strange SQL queries.

I have a full toSearrchableArray setup on all my models - both scout:import and tnt:import work fine

[Cookizza]

just FYI, the error it references in TNTSearchEngine.php is line 201 the map() function

 $models = $builder->whereIn(
        $model->getQualifiedKeyName(), $keys
    )->get()->keyBy($model->getKeyName());

This is the part that's failing. This is about keys, by the looks. All of my keys are simply ID columns. As an example, my most complex toSearchableArray is

public function toSearchableArray() {

    $search = [
        'title'       => $this->title,
        'description' => $this->description
    ];

    $search['id'] = $this->id;

    return $search;

}

[samoldenburg]

@michaelklopf Same versions as @Cookizza, and I've come to the same conclusion. The problem arises in the map() function where you're trying to load up models based on the results.

Specifically for me, I'm writing a "global" search which needs to search against several models in the same API request (not the same search of course) which is when this particular failure happens. This has been causing some really wacky queries to be generated for me, where it references columns that don't exist on completely unrelated tables.

My controller action looks something like the following:

foreach ($models as $model) {
    $results[$model] = ($model)::search($request->get('query'))->get();
}

return compact('results');

Unfortunately I've already spent 2 days on this and at this point am throwing in the towel on this package for now. I'm rolling with something more proven like the Algolia driver, even though I'm not happy about the additional cost and third party service.

[Cookizza]

If you need to have the search now, you can always go back to laravel 5.6. I'm guessing something has changed in eloquent optimisation that is merging these queries

[daniel3303]

I am having the exact same problem (but with Laravel 5.6). Is there any soluction for this?

[nticaric]

Have you tried with latest version v3.2.2?

[michaelklopf]

I tried to recreate the issue with searching in multiple models in one request and the fix in v3.2.2 does not throw an error anymore, but I don't know if your search results will still look the same. Can you confirm that it works @Cookizza @samoldenburg ?

[Cookizza]

@michaelklopf Things looks to be running normally.

Results seem fine - using tnt:import

Thanks!!