Laravel 5.5/Scout: how to extend Scout to use Eloquent joins?

No ideas, guys?

I believe that more people have been faced (or facing) it as having a searchable and sortable grid is a common need. Thanks?

Visit::where('column', 'LIKE', '%$query%')->join('origins', 'origins.id', 'visits.origin_id')

Thanks, Pierre! Unfortunately, it will not work.

Visit::where('column', 'LIKE', '%$query%')
->join('origins', 'origins.id', 'visits.origin_id')
->search('canada')
->get();

will produce:

Call to undefined method Illuminate\Database\Query\Builder::search()

and

$visits = Visit::search('canada')
->where('column', 'LIKE', '%$query%')
->join('origins', 'origins.id', 'visits.origin_id')
->get();

will give us:

Method join does not exist. SQLSTATE[42S22]: Column not found: 1054 Unknown column 'origin_name' in 'order clause' (SQL: select * from visits order by origin_name asc limit 10 offset 0)

I am trying to figure out how to extend ../vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php to "understand" the search() method.

Any suggestions are welcome!

Hi!

If you go to http://<your-elastic-host>/<your-elastic-index>/_mappings, you can see how your "visit" model are mapped.

Maybe you have this structure: visit > origin > { id, name, ... }, and you can do:

$visits = Visit::search('canada')->with(['origin', 'profile', 'user'])->orderBy('origin.name.keyword')->paginate(10);

I hope it helps you :)

Hi, @CcrisS. Thanks for the post, but it won't work. The mapping shows

   "origin_name": {
                        "type": "text",
                        "fields": {
                            "keyword": {
                                "type": "keyword",
                                "ignore_above": 256
                            }
                        },
                        "fielddata": true

but

$sort_column = 'origin_name';
$visits = Visit::search("canada")
            ->orderby($sort_column, $sort_az_za)
            ->with(['origin', 'profile', 'user'])
            ->paginate(10); 

will give us:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'origin_name' in 'order clause' (SQL: select * from visits order by origin_name asc limit 10 offset 0)

The whole problem is that the Visit model does not know the origin table even with the chained "with('origin') method.

Does anyone know how we can make Scout understand the following syntax?

$visits = DB::table('visits') 
->join('origins', 'origins.id', 'visits.origin_id')
->orderby($sort_column, $sort_az_za)
->paginate(10);

Update: I give Algolia a try and:

1. it searches very well related tables. It also has fuzzy search out of the box and a basic web interface for index management. To make it work, it's important to set up the main model. In my case, the model Visit has:

  public function toSearchableArray()
    {
        $array = $this->toArray();
        $array['products'] = $this->products->toArray();
        $array['origin'] = $this->origin->toArray();
        $array['profile'] = $this->profile->toArray();
        $array['user'] = $this->user->toArray();
        return $array;
   }

Searching is a breeze:

  $visits = Visit::search("$query")
            ->paginate(10);

Works very fast and nice but... -- and this is very annoying but -- I can't sort the resulting grid by any field I want. I dug a little and found out the following:

1. To sort, it's necessary to create an index replica. That's easy and makes sense;
2. only numeric fields can be sorted -- and this is very weird...
3. for instance, to sort the date, I created a new field called dt_unix where I store strtotime($date). Then I created two indices replicas: dt_asc and dt_desc.
4. When I want to sort I just have to indicate which index should be used. Here is how to set the index on the fly:

  $index_name = 'dt_desc';
  $visits = Visit::search("$query")
            ->within($index_name)
            ->paginate(10);

1. However, only the fields on the main table/model can be sorted. Thus, if I want to sort Origin (by any numeric criteria I could create), there is no way to set the index to sort by a field from a related table. Maybe using an "inverted model", i.e, Origin and the "related" Visits, it can be done -- it does look very messy, kind of the patching the workaround!

If I am missing something, please let me know. The only thing I need is to have a server paginated grid that is searchable and sortable -- sure, it's a very common thing.

As always, any ideas are welcome!

EDIT: I also tried the algolia instantsearch for building the search interface. It's nice, but not what I want righ now.

The "toSearchableArray" function is a scout functionallity, it works for Elasticsearch too. Now that your "visit" index knows about "origins", you can try this again (with scout.driver = 'elasticsearch'):

$visits = Visit::search('canada')->with(['origin', 'profile', 'user'])->orderBy('origin.name.keyword')->paginate(10);

It's important to use the "keyword" field (you can see it in your mappings).

Hello, Ccris! Thanks for the hint. I've changed back to Elastic Search two days ago - now using Erick's driver, that sorts related tables out of the box: https://github.com/ErickTamayo/laravel-scout-elastic. This driver relays on you to set up whatever you want via ES API and does a great job so far. I will soon post the complete code. Thanks!