Find a sentence that contains a word in text from database

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 = AppModelsPost::where('body', 'like', "%{in winter}%")->get();

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

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 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:

IlluminateSupportCollection 
    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.


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x