Find a sentence that contains a word in text from database

Question

I want to explain what I am trying to accomplish with an example. Let's assume I have two rows in my posts table.

It snows a lot in winter in Norway. While it barely snows where I live.

I run four miles every morning. I am trying to get fit.

When I search in winter I want to get the sentence It snows a lot in winter in Norway.

I know I can get the row with:

$posts = \App\Models\Post::where('body', 'like', "%{in winter}%")->get();

But I am not sure how to get the exact sentence.

Answer 1

While it might be technically possible using SQL to get the exact sentence, you are better off using PHP for getting the exact sentence from the collection.

I have created an example using collections (since you're using Laravel), starting with your provided Post query (although I did remove the curly braces from the like string).

1. Get the collection of posts with body like search query

$posts = Post::where('body', 'like', "%in winter%")->get();

2. Map collection of posts to individual sentences. Flatten to remove empty sentences.

$postSentences = $posts->map( function($post) {
   // preg split makes sure the text is splitted on . or ! or ?
   return preg_split('/\.|\?|!/', $post->body); 
})->flatten();

3. Get the corresponding sentence(s) using a filter and Str::contains()

$matchingSentences = $postSentences->filter( function($sentence) {
    return Str::contains($sentence, 'in winter');
});

$matchingSentences should return:

Illuminate\Support\Collection 
    all: [
       "It snows a lot in winter in Norway",
    ],
}

The example can probably be altered / shortened to your fitting. But this should solve the aforementioned problem.