'Search text parts in db tables

I have db tables 'books','book_authors' . And when user search in website for 'John Doe How to be happy' for example. I wanna search from db for author with name 'John' or 'Doe' or book with title'How to be happy'.

 $books = Book::where('book_title','like', '%'.$search.'%')
        ->orWhereHas('authors', function (Builder $q) use ($search) {
            $q->where('name', 'like','%'.$search.'%')
        })->paginate(12);

Here my query . But It looks for author with name 'John Doe How to be happy' or for books with title 'John Doe How to be happy' . And results nothing . Is it possible ?

laravel


Solution 1:[1]

Use orWhere

$books = Book::where('book_title','like', '%'.$search_word.'%')
         ->orWhereHas('authors', function ($q) use($search) {
            $q->orWhere('name', 'like','%'.$search.'%');
         })->paginate(12);

Solution 2:[2]

You can create permutation of search words then use the permutation with whereIn to get the expected result.

$str = "John Doe How to be happy";
$arr = explode(" ", $str);
$book_title = array();
/*
$book_title would look as below after for loop completes
Array
(
    [0] => happy
    [1] => be happy
    [2] => to be happy
    [3] => How to be happy
    [4] => Doe How to be happy
    [5] => John Doe How to be happy
)
*/
$author = array();
/*
$author would look as below after for loop completes
Array
(
    [0] => John
    [1] => Doe
    [2] => How
    [3] => to
    [4] => be
    [5] => happy
)
*/
for ($i = 0, $l = count($arr) - 1; $i <= $l; $i++) {
    array_push($author, $arr[0]);
    array_unshift($book_title, join(" ", $arr));
    array_shift($arr);
}


$books = Book::whereIn('book_title', book_title)
        ->whereHas('authors', function (Builder $q) use ($author) {
            $q->whereIn('name', $author);
        })
        ->paginate(12);

I have used whereHas with where because we want both the condition to be true

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 VIKAS KATARIYA
Solution 2

Related Questions