Searching

Anyone willing to advise/help ?

You are using LIKE, that's why it returns similar results instead of matching exactly what you've typed. Use a normal where('fieldName', '=', $searchTerm).

Ah thanks @Ruffles I'll try this.

If you want the result to start with what you type you can simply do this

->where('main_title', 'LIKE', $t . '%')

@bobbybouwmann that may be a better option as @Ruffles suggestion doesn't do what I want.

Example:

If i on a user type scottish border country then I expect that one item is returned as this is the only one, whereas if I was to type scottish then about 5/6 items would appear .

Still not getting the correct results for searching here.

Example:

If i on a user type scottish border country then I expect that one item is returned as this is the only one, whereas if I was to type scottish then about 5/6 items would appear .

How can I achieve this?

Anyone?

Give more details, like some of the content of your database

I need to be able to type something like "scottish border country" which would return the one book as it matches, but also type "scottish" which would return many other books as the "scottish" would be in the following rows within the db.

@pmall that help, with what I'm trying to achieve?

Like @pmall said, some content to recreate the issue would help. My guess would be that achieving your desired results with just one query (without using full-text-indexing) is impossible. I have 2.1 thoughts:

1. Assuming MySQL, take a look at using full text searching. https://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

2. Consider using ElasticSearch or Algolia as both of these will give you much more search power and much faster too.

2.1 Run two queries. First search for exact match and if no results found then run the LIKE match query. Clearly this isn't a great solution but it might be acceptable for your project.

Algolia is not what i need plus the client won't want to pay for this and Elastic Search I can't find a suitable package for this L4.2 version I'm working on.

Currently running the following:

$query = Books::query();
        foreach ($searchTerms as $t) {
            $query->where('main_title', '=', $t);
            $query->orWhere('main_title', 'LIKE', $t . '%');
            $query->orWhere('sur_1', 'LIKE', $t . '%');
            $query->orWhere('fore_1', 'LIKE', $t . '%');
            $query->orWhere('sur_2', 'LIKE', $t . '%');
            $query->orWhere('fore_2', 'LIKE', $t . '%');
            $query->orWhere('sur_3', 'LIKE', $t . '%');
            $query->orWhere('fore_3', 'LIKE', $t . '%');
        }

Then if anyone wants to help and have some data here's a portion of the database - http://laravel.io/bin/nQ4Gv, help would be greatly appreciated.

I can't see the data at the moment - and it's not abundantly clear what you aiming to do, or what the issue is with the results you're getting without seeing data.

That said, my first guess is that you need to group the results by an appropriate column, the book ID most likely, that way you'll get one row returned for each book, even if the terms match in multiple columns. (e.g. below).

$results = DB::table('books')
            ->where(function ($query) use ($searchTerms) {
                foreach ($searchTerms as $t) {
                    $query->where('main_title', 'LIKE', '%' . $t . '%')
                        ->orWhere('fore_1', 'LIKE', '%' . $t . '%')
                        ->orWhere('sur_1', 'LIKE', '%' . $t . '%')
                        ->orWhere('fore_2', 'LIKE', '%' . $t . '%')
                        ->orWhere('sur_2', 'LIKE', '%' . $t . '%');
                }
            })
        ->groupBy('id')
            ->paginate(10);

I don't think it does need grouping, all I want it to do is match if a book exists, otherwise find books like or has similar words in them.

@mstnorris you any idea's on this pal!!?

Can you give a couple of example of queries and their expected results from the sample data please?

I've used this ES client before and it says it works on L4. TBH it's a very light wrapper around the official ES PHP lib so you could just use that directly. https://github.com/shift31/laravel-elasticsearch

Thats the one I tried but the docs state a service provider, but when i tested within the browser, it shows as service provider not found, even though i followed the docs. @robgeorgeuk

@lstables I'm in and out of meetings all day for the next couple of days but if you haven't had any luck when I check back I'll be sure to have a more in depth look.

Good luck.

Ok, well post a couple of sample queries and lets see what we can do with MySQL.

If the search is important then I still think Elasticsearch could be a better solution to this issue and others. There are lots of useful things you can do like spelling correction and adding weight to certain fields, e.g. If the main_title contains the search term then rank this above a record that has the term in the sub_title etc etc.

I have this now, with the advice from a other dev friend. But still not showing exact results or similar and not sure why.

public function search()
    {
        $this->page_path = 'search';
        $searchTerm = explode(' ', Input::get('term'));

        if (empty($searchTerm)) {
            return Redirect::back();
        }
        $exactMatches = $this->searchForItem($searchTerm);

        if($exactMatches)
            return $exactMatches;

        $similarMatches = $this->searchSimilar($searchTerm);

        return $similarMatches;
    }
    public function searchForItem($searchTerm)
    {
        $query = Books::query(); // Not sure what this is suppose to be I've tried Books::all(), new Books; and still no joy or just returns everything, which is obviously not what I want.
        foreach($searchTerm as $t) {
            $query->where('main_title', '=', $t);
            $query->orWhere('sur_1', '=', $t);
            $query->orWhere('fore_1', '=', $t);
            $query->orWhere('sur_2', '=', $t);
            $query->orWhere('fore_2', '=', $t);
            $query->orWhere('sur_3', '=', $t);
            $query->orWhere('fore_3', '=', $t);
        }
        $books = $query->paginate(10);
        $books->appends(['term' => $searchTerm]);

        $terms           = $searchTerm;
     
        return View::make('site.results', compact('books', 'terms');
    }

    public function searchSimilar($searchTerm)
    {
        $searchableTerms = explode(' ',$searchTerm);
        $query = new Books;

        foreach($searchableTerms as $term)
        {
            $query->whereRaw('main_title LIKE %?%',[$term]);
            $query->orWhereRaw('sur_1 LIKE %?%',[$term]);
            $query->orWhereRaw('fore_1 LIKE %?%',[$term]);
            $query->orWhereRaw('sur_2 LIKE %?%',[$term]);
            $query->orWhereRaw('fore_2 LIKE %?%',[$term]);
            $query->orWhereRaw('sur_3 LIKE %?%',[$term]);
            $query->orWhereRaw('fore_3 LIKE %?%',[$term]);
        }
//        return $query->get();
        $books = $query->paginate(10);
        $books->appends(['term' =>$searchTerm]);

        $terms           = $searchTerm;
        $recently_viewed = Viewing::getViewingHistory();

        return View::make('site.results', compact('books', 'terms', 'recently_viewed'));
    }

@mstnorris still no further with this tried all sorts now and my last post shows a newer way then I originally posted, any idea's?

If you're not going to go with something like ES, how about something like searchable? https://github.com/nicolaslopezj/searchable

Supports weighting/relevance and pagination etc, so should fit what you need.

The link you posted for the data set on the laravel.io bin doesn't seem to work for me, otherwise I'd take a look.

I've looked at that package and it's really what I need I need it to be simple but effective.

Try

// Split into words and don't include spaces in the resulting array
$searchableTerms = preg_split('/\s+/', $searchTerm);

DB::table('books')
    ->where(function ($query) use ($searchableTerms) {
        foreach($searchableTerms AS $t) {
            $query->where('main_title', 'REGEXP', '[[:<:]]'.$t.'[[:>:]]');
        }
    }

Use "where" instead of "orWhere" so the query matches records that include ALL the words and use REGEXP to match complete words.

I get that, but in my results view page I have a foreach() loop so need it to work with that too.

Hi @lstables,

Could you create a migration on your fields you are searching by to create a full text index and then do something similar to this post

http://creative-punch.net/2013/12/implementing-laravel-4-full-text-search/

in your query? The example is Laravel 4, so it might fit into your project nicely.

Apologies if this is an option you've tried.

@desloc Thanks for that, but following that tut through I get SQLSTATE[HY000]: General error: 1191 Can't find FULLTEXT index

@lstables First of all never use explode for like %$term%. Use preg_split - here's why http://stackoverflow.com/questions/28270244/laravel-search-multiple-words-separated-by-space

Next, you could use this https://github.com/jarektkaczyk/eloquence/wiki/Builder-searchable-and-more#searchable probably, but 1 it's L5 only and 2 you didn't really explain the algo you need.

Btw If you ever feel the need for https://github.com/nicolaslopezj/searchable mentioned by @industrious.mouse (and you're on L5), then get my package straight away - it does it all too but faster, better and more flexible.

@jarektkaczyk Hi, there. is your package still maintained? Will like to use it in a project but I see a couple of issues. Any hope you fixing things soon?