How to search for multiple words?

Have you tried to use something like Laravel Scout? https://laravel.com/docs/5.4/scout

The order is important in this scenario so a fulltext index might suit you better.

What results are you getting? Are you getting items.description or vendoritems.partnumber?

I'd try to remove this:

->where('items.deleted', '=', 0)
->where('vendoritems.deleted', '=', 0)

If you soft deleted some items, I'd consider storing in an archive table, since the search doesn't apply to them.

Does a regular sql query work like in phpmyadmin, or something like sqlyog?

@wlepinski Thanks, I'll check it out. Do you have any idea if this works without using models? For example can I just replace ->table(items) with ->search('items')

@jlrdw I dont have control over how tables are structured and I cant change anything.

The problem is that I cant search for multiple words in a single sentence. For example if the text I'm searching is Item D1234 HEX SCREW GS0090 OI09 I want to be able to search for D1234 GS0090 and have this row returned right now I can only search D1234 HEX SCREW GS0090

@derrickrozyay I tried query like below and it returned the result as you are needed

select * from table where $term like concat('%',column, '%')

So, what I wanted to say is reverse all your where clause like below

where(DB::raw(concat('%', $term, '%') , 'LIKE', 'items.description')

NOTE : I am using sql server for my database please find similar function for concat in your corresponding DB.

Use some string functions to get first and last part of string, ignoring the middle part if necessary.

a crude way is to replace all spaces in the search term with percent symbols so you end up with a search like '%D1234%GS0090%'

The disadvantage with this is that if the terms are reversed then there would be no match

next step in complexity is to break the term into discrete strings then search for them individually and then display items with most matches first, then fewer or single matches

A point here is you probably need to play around with some string functions and see what works. As yes even in programming there is cases for trial and error.

You really should be using an actual search index for this as matching with LIKE is really not suited for this.

However, assuming you only want an item to match if it contains all of the keywords provided this should work regardless of the order the terms are provided.

$terms = explode(' ', $term);

$products = DB::connection('items')
    ->table('items')
        ->join('itemunits','itemunits.itemcode', '=', 'items.itemcode')
        ->join('vendoritems', 'vendoritems.itemcode', '=', 'items.itemcode')
        ->where('items.deleted', '=', 0)
        ->where('vendoritems.deleted', '=', 0)
    ->where(function($query) use ($terms){
        foreach($terms as $term){
            $query->where('items.description', 'LIKE', '%'.$term.'%');
        }
    })
    ->orWhere(function($query) use ($terms){
        foreach($terms as $term){
            $query->where('items.vendorcode', 'LIKE', '%'.$term.'%');
        }
    })
    ->orWhere(function($query) use ($terms){
        foreach($terms as $term){
            $query->where('items.vendordefaultcode', 'LIKE', '%'.$term.'%');
        }
    })
    ->orWhere(function($query) use ($terms){
        foreach($terms as $term){
            $query->where('vendoritems.partnumber', 'LIKE', '%'.$term.'%');
        }
    })
    ->orWhere(function($query) use ($terms){
        foreach($terms as $term){
            $query->where('vendoritems.partvendorcode', 'LIKE', '%'.$term.'%');
        }
    })
        ->get());

@derrickrozay No, it's not that simple as changing the method from ->find() to ->search(). Laravel Scout needs to create the indexes required to do a full-text search on your database.

I would recommend you to follow the https://laravel.com/docs/5.4/scout#installation. By default, Scout will try to use Algolia Driver to generate those indexes for you, as I didn't want to use this driver and leverage the capabilities of my MySQL database I ended using the https://packagist.org/packages/damiantw/laravel-scout-mysql-driver.

The process to generate the indexes is handled by the third-party library using the php artisan scout:mysql-index {Model} artisan command. You can also refer the official documentation of both libraries described here.

Performance-wise speaking you should avoid the foreach to assign multiple LIKE queries. You'll have some serious bootneck on your database when the items table starts to become a little monster.

@spekkionu What do you mean by "use an actual search index"?

@wlepinski Thanks. I'll look into that. I have to use mysql I dont have the ability to switch to another driver. How hard was it to implement? One thing I wanted to avoid was having to create a model for each of the pre-made tables I'll need to search (over 5) but there doesnt seem to be any other options

By "actual search index" I was referring to performing your searches on a dedicated search index rather than directly on the mysql tables.

There are dozens of search indexing solutions available and most likely have a scout driver available.

• https://github.com/ErickTamayo/laravel-scout-elastic
• https://github.com/damiantw/laravel-scout-mysql-driver
• https://github.com/teamtnt/laravel-scout-tntsearch-driver
• https://github.com/hocnt84/laravel-scout-sphinx

Another solution is just do a simple search, in other words by vendor, if that don't have what's needed, try description, etc. Just simplify things.

Of course there's Elasticsearch also, see if it will work for you.